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