# Author renyupeng """ coding=utf-8 @Time : 2023/2/7 @Site : @File : mysql_helper.py @Software: PyCharm @contact: renyupeng@c-top.com.cn @Tel 1501435553 """ import pymysql import logging from constant.ConfConstant import ConfConstant from utils.send_feishu_msg import SendFeiShuMsg logger = logging.getLogger(__name__) def insert(item, table_name): con = pymysql.connect(host='192.168.0.101', user='hcst', passwd='hcst@2024', charset='utf8', port=3390) async_item = tuple(item.values()) # 对数据库进行插入操作,并不需要commit,twisted会自动commit placeholders = ', '.join(['%s'] * len(item)) columns = ', '.join(item.keys()) insert_sql = "REPLACE INTO kwai_promoter.%s ( %s ) VALUES ( %s )" % ( table_name, columns, placeholders) cur = con.cursor() try: cur.execute(insert_sql, async_item) con.commit() except Exception as e: SendFeiShuMsg.send_robot_msg('{table_name}数据插入错误{e}'.format(table_name=table_name, e=e)) con.rollback() con.close() def batch_insert(item_list, table_name): con = pymysql.connect(host='192.168.0.101', user='hcst', passwd='hcst@2024', charset='utf8', port=3390) # 对数据库进行插入操作,并不需要commit,twisted会自动commit placeholders = ', '.join(['%s'] * len(tuple(item_list[0]))) columns = ', '.join(item_list[0].keys()) insert_sql = "REPLACE INTO kwai_promoter.%s ( %s ) VALUES ( %s )" % ( table_name, columns, placeholders) cur = con.cursor() data = [] try: for item in item_list: value = tuple(item.values()) data.append(value) cur.executemany(insert_sql, data) con.commit() except Exception as e: SendFeiShuMsg.send_robot_msg('{table_name}数据批量插入错误{e}'.format(table_name=table_name, e=e)) con.rollback() con.close() def insertPro(item, table_name): con = pymysql.connect(host='139.186.27.96', user='data', passwd='hcst@2021', charset='utf8', port=3390) async_item = tuple(item.values()) print(async_item,'-----') # 对数据库进行插入操作,并不需要commit,twisted会自动commit placeholders = ', '.join(['%s'] * len(item)) columns = ', '.join(item.keys()) insert_sql = "REPLACE INTO ruixuan.%s ( %s ) VALUES ( %s )" % ( table_name, columns, placeholders) print(insert_sql) cur = con.cursor() try: cur.execute(insert_sql, async_item) con.commit() except Exception as e: print(e) SendFeiShuMsg.send_robot_msg('{table_name}数据插入错误{e}'.format(table_name=table_name, e=e)) con.rollback() con.close() def batch_insertPro(item_list, table_name): con = pymysql.connect(host='139.186.27.96', user='data', passwd='hcst@2021', charset='utf8', port=3390) # 对数据库进行插入操作,并不需要commit,twisted会自动commit placeholders = ', '.join(['%s'] * len(tuple(item_list[0]))) columns = ', '.join(item_list[0].keys()) insert_sql = "REPLACE INTO ruixuan.%s ( %s ) VALUES ( %s )" % ( table_name, columns, placeholders) cur = con.cursor() data = [] try: for item in item_list: value = tuple(item.values()) data.append(value) cur.executemany(insert_sql, data) con.commit() except Exception as e: SendFeiShuMsg.send_robot_msg('{table_name}数据批量插入错误{e}'.format(table_name=table_name, e=e)) con.rollback() con.close()