dataExportScript.py 2.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122
  1. """
  2. 本脚本文件主要用于MySQL数据导出
  3. 使用方法:
  4. 使用本脚需要安装pandas和pymysql库
  5. 安装方法:pip install xxxx
  6. 本脚本接受一个参数,指定取数据库前多少行的数据。
  7. 例如:取出前1000行的内容
  8. 方法:dataExportScript.py 1000
  9. """
  10. import pandas as pd
  11. import pymysql
  12. import time
  13. import sys
  14. import os
  15. def readSQLData(ip, username, passwd, DBname, SQL):
  16. """
  17. 读取一条sql数据
  18. 参数:
  19. ip:str 主机地址
  20. port:int 默认3306
  21. username:str 用户名
  22. passwd:str 密码
  23. DBname:str 数据库名
  24. SQL:str SQL语句
  25. return: DataFrame
  26. """
  27. con = pymysql.connect (host=ip, user=username, password=passwd, db=DBname)
  28. return pd.read_sql (SQL, con)
  29. def newFile(dir):
  30. """
  31. 从指定目录中找到最新修改的文件
  32. 参数:
  33. dir:str 目录
  34. return:文件相对路径
  35. """
  36. # 列出目录下所有的文件
  37. filelist = os.listdir (dir)
  38. # print(filelist)
  39. # 对文件修改时间进行升序排列
  40. filelist.sort (key=lambda fn: os.path.getmtime (dir + '/' + fn))
  41. # 获取文件所在目录
  42. try:
  43. filepath = os.path.join (dir, filelist[-1])
  44. newFileName = filelist[-1]
  45. # print(newFileName[:-4].split('_')[1])
  46. number = newFileName[:-4].split ('_')[1]
  47. except:
  48. number = 0
  49. return number
  50. def main():
  51. # MySQL数据库连接参数
  52. IP = "112.74.211.208"
  53. USER = "ch"
  54. PASSWD = "ch_d88fc6@ttq.so"
  55. DB = "ttq"
  56. # 接受参数
  57. argv1 = sys.argv[1]
  58. # argv1 = 500
  59. # 备份目录
  60. BACKUPS_DIR = "/home/python_script/python_script/dataBackupsDir"
  61. # 数据库查询
  62. SQL = "SELECT * FROM log_action where `id`>%d LIMIT 0,%d"%(int(newFile(BACKUPS_DIR)),int(argv1))
  63. logdata = readSQLData (IP, USER, PASSWD, DB, SQL)
  64. datestr = time.strftime('%Y-%m-%d', time.localtime(time.time()))
  65. # 数据导出
  66. logdata.to_csv ('%s/backupFile%s_%s.csv'%(BACKUPS_DIR,datestr,logdata['id'].values[-1]), index=None)
  67. main()
  68. if __name__ == '__main__':
  69. # 本地测试
  70. pass
  71. # argv1 = sys.argv[1]
  72. # print (newFile ('./Data/'))
  73. # SQL = "SELECT * FROM log_action LIMIT 0, %d"%int(argv1)
  74. # logdata = readSQLData ("112.74.211.208", "ch", "ch_d88fc6@ttq.so", "ttq", SQL)
  75. # print(int (logdata["id"][-1:].values))
  76. # datastr = time.strftime('%Y-%m-%d', time.localtime(time.time()))
  77. # logdata.to_csv ('./Data/backupFile%s_%s.csv'%(datastr,argv1), index=None)