mysql数据库批量插入

以下是MySQL数据库批量插入的两种方式：

1.使用批处理模式

import pymysql

# 连接数据库
conn = pymysql.connect(host='localhost', user='root', password='123456', database='test', charset='utf8mb4')
cursor = conn.cursor()

# 开启批处理模式
cursor.execute('SET AUTOCOMMIT=0')
cursor.execute('SET unique_checks=0')
cursor.execute('SET foreign_key_checks=0')

# 批量插入数据
sql = "INSERT INTO student (name, age) VALUES (%s, %s)"
data = [('Tom', 18), ('Jerry', 20), ('Mike', 22)]
cursor.executemany(sql, data)

# 提交事务
conn.commit()

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

2.使用ORM框架的批量插入方法

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Student

# 连接数据库
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test?charset=utf8mb4')
DBSession = sessionmaker(bind=engine)
session = DBSession()

# 批量插入数据
data = [Student(name='Tom', age=18), Student(name='Jerry', age=20), Student(name='Mike', age=22)]
session.add_all(data)
session.commit()

# 关闭连接
session.close()