UpLoadFile.py 2.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2024/6/27 20:28
  4. # @Site :
  5. # @File : UpLoadFile.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. from urllib import parse
  10. import pandas as pd
  11. from sqlalchemy import create_engine
  12. class UpLoadFile:
  13. @staticmethod
  14. def uploadFile(file_name):
  15. OuterStr = "mysql+pymysql://%s:%s@%s:%d/%s" % (
  16. "hcst", parse.quote_plus("hcst@2024"), "192.168.0.101", 3390, "jeecg-boot")
  17. OuterConn = create_engine(OuterStr,
  18. pool_size=10,
  19. max_overflow=5,
  20. pool_timeout=10,
  21. pool_recycle=3600)
  22. chunksize = 100000
  23. for chunk in pd.read_csv(f'/home/excel/{file_name}'.format(file_name=file_name),
  24. chunksize=chunksize, encoding='GBK', header=None, low_memory=False, skiprows=1):
  25. # 将每个块存入MySQL,'表名'需要替换为目标表名
  26. chunk.columns = ['delivery_month',
  27. 'operation_label',
  28. 'material_id',
  29. 'str_material_id',
  30. 'account_id',
  31. 'stat_date',
  32. 'str_account_id',
  33. 'account_name',
  34. 'ad_ids',
  35. 'customer_name',
  36. 'user_name',
  37. 'rebate_type',
  38. 'settlement_1l',
  39. 'settlement_2l',
  40. 'first_valid',
  41. 'cost_7day',
  42. 'cost']
  43. try:
  44. chunk.to_sql('first_delivery_valid_materials', con=OuterConn, index=False, if_exists='append'
  45. )
  46. return 0
  47. except Exception as e:
  48. print(e)
  49. return -1