mysql_helper.py 1.9 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859
  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.184', user='hcst', passwd='hcst@2021', 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.184', user='hcst', passwd='hcst@2021', 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()