# 常用datetime格式化 2020-10-15 11:47:09 d3_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
批量插入
import pymysql
def insert_to_mysql(to_db_list):
mysql_db = pymysql.connect(host="HOST_IP", port=3306, user="username", password="password",
database="db", charset="utf8")
cursor = mysql_db.cursor()
sql = "INSERT INTO `your_db`.`your_table`(`colum1`, `colum2`, `colum3`) VALUES (%s,%s,%s)"
try:
# cursor.execute()
cursor.executemany(sql, to_db_list) # 批量插入
effect_rows = cursor.rowcount
mysql_db.commit()
cursor.close()
print('数据库添加成功，插入 {}条数据'.format(effect_rows))
return effect_rows
except Exception as e:
mysql_db.rollback()
print('数据库执行失败')
print(e)
return 0
my_list = []
my_list.append(('v1', 'v2', 'v3'))
cnt = insert_to_mysql(my_list)
查询
def get_id_name():
cursor = mysql_db.cursor()
sql = "select id, name from `your_db`.`table`"
cursor.execute(sql)
res = cursor.fetchall()
# print(res)
return res
my_list = get_id_name()
for index in range(len(my_list)):
print(my_list[index][0]) # id
print(my_list[index][1]) # name
根据ID查询
sql = "SELECT company_id FROM company_tables where company_name = '{0}'".format(name)
cursor.execute(sql)
更新
def update_by_id(update_list):
"""根据ID更新col1, col2, col3
list 依次为 col1, col2, col3, id
:param update_list:
:return:
"""
cursor = mysql_db.cursor()
sql = "UPDATE `your_db`.`table` SET col1=(%s),col2=(%s),col3=(%s) WHERE id=(%s)"
try:
# cursor.execute()
cursor.executemany(sql, update_list) # 批量插入
mysql_db.commit()
cursor.close()
print('数据库更新成功')
except Exception as e:
mysql_db.rollback()
print('数据库更新失败')
print(e)
my_list = []
my_list.append(('v1', 'v2', 'v3', 'id'))
update_by_id(my_list)
参考：