pymysql常用操作

  • Z时代
  • 2024-01-10
  • 分类：综合

python

批量插入

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)

　　

以上是 pymysql常用操作 的全部内容， 来源链接： utcz.com/z/529915.html

回到顶部