sqlalchemy批量插入数据
方式1:
first_time = datetime.utcnow()
for i in range(10000):
user = User(username=username + str(i), password=password)
db.session.add(user)
db.session.commit()
second_time = datetime.utcnow()
print((second_time - first_time).total_seconds())
# 38.14347s
方式2:
second_time = datetime.utcnow()
db.session.bulk_save_objects(
[
User(username=username + str(i), password=password)
for i in range(10000)
]
)
db.session.commit()
third_time = datetime.utcnow()
print((third_time - second_time).total_seconds())
# 2.121589s
方式3:
third_time = datetime.utcnow()
db.session.bulk_insert_mappings(
User,
[
dict(username="NAME INSERT " + str(i), password=password)
for i in range(10000)
]
)
db.session.commit()
fourth_time = datetime.utcnow()
print((fourth_time - third_time).total_seconds())
# 1.13548s
方式4:
fourth_time = datetime.utcnow()
db.session.execute(
User.__table__.insert(),
[{"username": 'Execute NAME ' + str(i), "password": password} for i in range(10000)]
)
db.session.commit()
five_time = datetime.utcnow()
print((five_time - fourth_time).total_seconds())
# 0.888822s