mysql_helper.py 4.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113
  1. # Author renyupeng
  2. """
  3. coding=utf-8
  4. @Time : 2023/2/7
  5. @Site :
  6. @File : mysql_helper.py
  7. @Software: PyCharm
  8. @contact: renyupeng@c-top.com.cn
  9. @Tel 1501435553
  10. """
  11. import pymysql
  12. import logging
  13. from constant.ConfConstant import ConfConstant
  14. from utils.send_feishu_msg import SendFeiShuMsg
  15. logger = logging.getLogger(__name__)
  16. def insert(item, table_name):
  17. con = pymysql.connect(host='192.168.0.249', user='hcst', passwd='hcst@2024', charset='utf8', port=3390)
  18. async_item = tuple(item.values())
  19. # 对数据库进行插入操作,并不需要commit,twisted会自动commit
  20. placeholders = ', '.join(['%s'] * len(item))
  21. columns = ', '.join(item.keys())
  22. insert_sql = "REPLACE INTO kwai_promoter.%s ( %s ) VALUES ( %s )" % (
  23. table_name, columns, placeholders)
  24. cur = con.cursor()
  25. try:
  26. cur.execute(insert_sql, async_item)
  27. con.commit()
  28. except Exception as e:
  29. SendFeiShuMsg.send_robot_msg('{table_name}数据插入错误{e}'.format(table_name=table_name, e=e))
  30. con.rollback()
  31. con.close()
  32. def batch_insert(item_list, table_name):
  33. con = pymysql.connect(host='192.168.0.249', user='hcst', passwd='hcst@2024', charset='utf8', port=3390)
  34. # 对数据库进行插入操作,并不需要commit,twisted会自动commit
  35. placeholders = ', '.join(['%s'] * len(tuple(item_list[0])))
  36. columns = ', '.join(item_list[0].keys())
  37. insert_sql = "REPLACE INTO kwai_promoter.%s ( %s ) VALUES ( %s )" % (
  38. table_name, columns, placeholders)
  39. cur = con.cursor()
  40. data = []
  41. try:
  42. for item in item_list:
  43. value = tuple(item.values())
  44. data.append(value)
  45. cur.executemany(insert_sql, data)
  46. con.commit()
  47. except Exception as e:
  48. SendFeiShuMsg.send_robot_msg('{table_name}数据批量插入错误{e}'.format(table_name=table_name, e=e))
  49. con.rollback()
  50. con.close()
  51. def insertPro(item, table_name):
  52. con = pymysql.connect(host='139.186.27.96', user='data', passwd='hcst@2021', charset='utf8', port=3390)
  53. async_item = tuple(item.values())
  54. print(async_item,'-----')
  55. # 对数据库进行插入操作,并不需要commit,twisted会自动commit
  56. placeholders = ', '.join(['%s'] * len(item))
  57. columns = ', '.join(item.keys())
  58. insert_sql = "REPLACE INTO ruixuan.%s ( %s ) VALUES ( %s )" % (
  59. table_name, columns, placeholders)
  60. print(insert_sql)
  61. cur = con.cursor()
  62. try:
  63. cur.execute(insert_sql, async_item)
  64. con.commit()
  65. except Exception as e:
  66. print(e)
  67. SendFeiShuMsg.send_robot_msg('{table_name}数据插入错误{e}'.format(table_name=table_name, e=e))
  68. con.rollback()
  69. con.close()
  70. def batch_insertPro(item_list, table_name):
  71. con = pymysql.connect(host='139.186.27.96', user='data', passwd='hcst@2021', charset='utf8', port=3390)
  72. # 对数据库进行插入操作,并不需要commit,twisted会自动commit
  73. placeholders = ', '.join(['%s'] * len(tuple(item_list[0])))
  74. columns = ', '.join(item_list[0].keys())
  75. insert_sql = "REPLACE INTO ruixuan.%s ( %s ) VALUES ( %s )" % (
  76. table_name, columns, placeholders)
  77. cur = con.cursor()
  78. data = []
  79. try:
  80. for item in item_list:
  81. value = tuple(item.values())
  82. data.append(value)
  83. cur.executemany(insert_sql, data)
  84. con.commit()
  85. except Exception as e:
  86. SendFeiShuMsg.send_robot_msg('{table_name}数据批量插入错误{e}'.format(table_name=table_name, e=e))
  87. con.rollback()
  88. con.close()
  89. def batch_insertPro_jst(item_list, table_name):
  90. con = pymysql.connect(host='139.186.27.96', user='data', passwd='hcst@2021', charset='utf8', port=3390)
  91. # 对数据库进行插入操作,并不需要commit,twisted会自动commit
  92. insert_sql = "REPLACE INTO ruixuan.{table_name}(poi_id, po_id,sku_id,qty, price,po_date,start_qty,end_qty,sku_num) VALUES ( %s,%s, %s, %s, %s, %s, %s, %s, %s )".format(table_name=table_name)
  93. cur = con.cursor()
  94. try:
  95. cur.executemany(insert_sql, item_list)
  96. con.commit()
  97. except Exception as e:
  98. SendFeiShuMsg.send_robot_msg('{table_name}数据批量插入错误{e}'.format(table_name=table_name, e=e))
  99. con.rollback()
  100. con.close()