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)