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 MySQLdb
  12. import logging
  13. from utils.send_feishu_msg import SendFeiShuMsg
  14. logger = logging.getLogger(__name__)
  15. def insert(item, table_name):
  16. con = MySQLdb.connect(host='192.168.0.184', user='hcst', passwd='hcst@2021', charset='utf8', port=3390)
  17. async_item = tuple(item.values())
  18. # 对数据库进行插入操作,并不需要commit,twisted会自动commit
  19. placeholders = ', '.join(['%s'] * len(item))
  20. columns = ', '.join(item.keys())
  21. insert_sql = "REPLACE INTO kwai_promoter.%s ( %s ) VALUES ( %s )" % (
  22. table_name, columns, placeholders)
  23. cur = con.cursor()
  24. try:
  25. cur.execute(insert_sql, async_item)
  26. con.commit()
  27. except Exception as e:
  28. SendFeiShuMsg.send_robot_msg('{table_name}数据插入错误{e}'.format(table_name=table_name, e=e))
  29. con.rollback()
  30. con.close()
  31. def batch_insert(item_list, table_name):
  32. con = MySQLdb.connect(host='192.168.0.184', user='hcst', passwd='hcst@2021', charset='utf8', port=3390)
  33. # 对数据库进行插入操作,并不需要commit,twisted会自动commit
  34. placeholders = ', '.join(['%s'] * len(tuple(item_list[0])))
  35. columns = ', '.join(item_list[0].keys())
  36. insert_sql = "REPLACE INTO kwai_promoter.%s ( %s ) VALUES ( %s )" % (
  37. table_name, columns, placeholders)
  38. cur = con.cursor()
  39. data = []
  40. try:
  41. for item in item_list:
  42. value = tuple(item.values())
  43. data.append(value)
  44. cur.executemany(insert_sql, data)
  45. con.commit()
  46. except Exception as e:
  47. SendFeiShuMsg.send_robot_msg('{table_name}数据批量插入错误{e}'.format(table_name=table_name, e=e))
  48. con.rollback()
  49. con.close()
  50. class MysqlDBHelper:
  51. """这个类也是读取settings中的配置,自行修改代码进行操作"""