pymysql 常用操作

import <a href="https://www.gaodaima.com/tag/pymysql" title="查看更多关于pymysql的文章" target="_blank">pymysql</a>


def insert_to_mysql(to_<a href="https://www.gaodaima.com/tag/db" title="查看更多关于db的文章" target="_blank">db</a>_list):
    mysql_db = pymysql.connect(host="HOST_IP", port=3306, user="username", password="password",
                               database="db", charset="utf8")
    <a href="https://www.gaodaima.com/tag/cursor" title="查看更多关于cursor的文章" target="_blank">cursor</a> = 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)

www#gaodaima.com来源gao($daima.com搞@代@#码网搞代码

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)