mysql批量数据

import mysql.connector

# 连接到 MySQL 数据库
db = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

cursor = db.cursor()

# 准备要插入的数据
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
    ('John', 'Highway 21'),
    ('Peter', 'Lowstreet 4'),
    ('Amy', 'Apple st 652'),
    ('Hannah', 'Mountain 21'),
    ('Michael', 'Valley 345'),
    ('Sandy', 'Ocean blvd 2'),
    ('Betty', 'Green Grass 1'),
    ('Richard', 'Sky st 331'),
    ('Susan', 'One way 98'),
    ('Vicky', 'Yellow Garden 2'),
    ('Ben', 'Park Lane 38'),
    ('William', 'Central st 954'),
    ('Chuck', 'Main Road 989'),
    ('Viola', 'Sideway 1633')
]

# 执行批量插入
cursor.executemany(sql, val)

# 提交事务
db.commit()

print(cursor.rowcount, "记录插入成功。")

# 关闭连接
cursor.close()
db.close()

ALTER TABLE customers MODIFY COLUMN address VARCHAR(255);

SET GLOBAL innodb_lock_wait_timeout = 120; -- 设置为120秒

import time

for i in range(3):  # 最多重试3次
    try:
        cursor.execute("DELETE FROM customers WHERE status = 'inactive'")
        db.commit()
        break
    except mysql.connector.errors.DatabaseError as err:
        if err.errno == 1213:  # 死锁错误
            time.sleep(1)  # 等待1秒后重试
            db.rollback()
        else:
            raise