5、Python-循环执行多次sql语句

最新推荐文章于 2024-05-13 20:01:42 发布

蜻蜓轻停青亭倾听听雨声

最新推荐文章于 2024-05-13 20:01:42 发布

阅读量1.2k

点赞数

分类专栏： python mysql 文章标签：数据库 mysql python

本文链接：https://blog.csdn.net/qq_41917061/article/details/129836822

版权

python 同时被 2 个专栏收录

65 篇文章 7 订阅

订阅专栏

mysql

5 篇文章 0 订阅

订阅专栏

循环执行sql语句

游标对象中得executeMany()函数可以反复执行一条SQL语句

sql = "insert into my_class(id,name,class) VALUES(%s,%s,%s)" 
data = [
    [1, "张三" , "A" ] , 
    [2, "李四" , "B" ] 
] 
cursor.executemany(sql, data)

例：

import mysql.connector.pooling
config = {
    "host" : "localhost" ,
    "port" : 3306,
    "user" : "root" ,
    "password" : "123456" ,
    "database" : "qa"
}
try:
    # 创建连接池
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    # 取出空闲连接，后面无需设置关闭
    con = pool.get_connection()
    # 开启事务
    con.start_transaction()
    # 创建游标
    cursor = con.cursor()
    sql = "insert into my_class(id,name,class) value (%s,%s,%s)"
    data = [
        [1, "王五", "A"],
        [2, "李四", "B"]
    ]
    # 循环多次执行SQL
    cursor.executemany(sql,data)
    # 提交
    con.commit()

except Exception as e:
    # 若是连接创建成功，报错时回滚
    if "con" in dir():
        con.rollback()
    print(e)