python pymysql库的常用操作
批量插入
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
更新
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)
以上就是python pymysql库的常用操作的详细内容，更多关于python pymysql库的资料请关注我们其它相关文章！
赞 (0)