背景:
项目中有一个数据同步的步骤(使用SQLAlchemy通过txt文件把数据放进mysql数据),在当中遇到了一些问题:
1,在同步批量数据的时候(主要是更新,和插入),会导致mysql等待超时。从而到时数据导入失败。
2,在批量导入数据的时候,使用session.add(obj)的方法,导入时间较长。
由于出现上述的问题,所以特别看了关于SQLAlchemy的插入数据库的文档。
SQLAlchemy批量导入有哪些?性能又如何?
SQLAlchemy官网有一篇文章详细介绍到:Performance—SQLAlchemy
当然,也有技术达人翻译了中文的文档:SQLAlchemy批量插入性能比较
在这里我也贴一下官网的代码,及其结果。然后做一些我自己的认识和简单说明
官网代码:
import time
import sqlite3
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
Base = declarative_base()
DBSession = scoped_session(sessionmaker())
engine = None
class Customer(Base):
__tablename__ = "customer"
id = Column(Integer, primary_key=True)
name = Column(String(255))
def init_sqlalchemy(dbname='sqlite:///sqlalchemy.db'):
global engine
engine = create_engine(dbname, echo=False)
DBSession.remove()
DBSession.configure(bind=engine, autoflush=False, expire_on_commit=False)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
def test_sqlalchemy_orm(n=100000):
init_sqlalchemy()
t0 = time.time()
for i in xrange(n):
customer = Customer()
customer.name = 'NAME ' + str(i)
DBSession.add(customer)
if i % 1000 == 0:
DBSession.flush()
DBSession.commit()
print(
"SQLAlchemy ORM: Total time for " + str(n) +
" records " + str(time.time() - t0) + " secs")
def test_sqlalchemy_orm_pk_given(n=100000):
init_sqlalchemy()
t0 = time.time()
for i in xrange(n):
customer = Customer(id=i + 1, name="NAME " + str(i))
DBSession.add(customer)
if i % 1000 == 0:
DBSession.flush()
DBSession.commit()
print(
"SQLAlchemy ORM pk given: Total time for " + str(n) +
" records " + str(time.time() - t0) + " secs")
def test_sqlalchemy_orm_bulk_save_objects(n=100000):
init_sqlalchemy()
t0 = time.time()
for chunk in range(0, n, 10000):
DBSession.bulk_save_objects(
[
Customer(name="NAME " + str(i))
for i in xrange(chunk, min(chunk + 10000, n))
]
)
DBSession.commit()
print(
"SQLAlchemy ORM bulk_save_objects(): Total time for " + str(n) +
" records " + str(time.time() - t0) + " secs")
def test_sqlalchemy_orm_bulk_insert(n=100000):
init_sqlalchemy()
t0 = time.time()
for chunk in range(0, n, 10000):
DBSession.bulk_insert_mappings(
Customer,
[
dict(name="NAME " + str(i))
for i in xrange(chunk, min(chunk + 10000, n))
]
)
DBSession.commit()
print(
"SQLAlchemy ORM bulk_insert_mappings(): Total time for " + str(n) +
" records " + str(time.time() - t0) + " secs")
def test_sqlalchemy_core(n=100000):
init_sqlalchemy()
t0 = time.time()
engine.execute(
Customer.__table__.insert(),
[{"name": 'NAME ' + str(i)} for i in xrange(n)]
)
print(
"SQLAlchemy Core: Total time for " + str(n) +
" records " + str(time.time() - t0) + " secs")
def init_sqlite3(dbname):
conn = sqlite3.connect(dbname)
c = conn.cursor()
c.execute("DROP TABLE IF EXISTS customer")
c.execute(
"CREATE TABLE customer (id INTEGER NOT NULL, "
"name VARCHAR(255), PRIMARY KEY(id))")
conn.commit()
return conn
def test_sqlite3(n=100000, dbname='sqlite3.db'):
conn = init_sqlite3(dbname)
c = conn.cursor()
t0 = time.time()
for i in xrange(n):
row = ('NAME ' + str(i),)
c.execute("INSERT INTO customer (name) VALUES (?)", row)
conn.commit()
print(
"sqlite3: Total time for " + str(n) +
" records " + str(time.time() - t0) + " sec")
if __name__ == '__main__':
test_sqlalchemy_orm(100000)
test_sqlalchemy_orm_pk_given(100000)
test_sqlalchemy_orm_bulk_save_objects(100000)
test_sqlalchemy_orm_bulk_insert(100000)
test_sqlalchemy_core(100000)
test_sqlite3(100000)
官网运行结果:
SQLAlchemy ORM: Total time for 100000 records 6.89754080772 secs
SQLAlchemy ORM pk given: Total time for 100000 records 4.09481811523 secs
SQLAlchemy ORM bulk_save_objects(): Total time for 100000 records 1.65821218491 secs
SQLAlchemy ORM bulk_insert_mappings(): Total time for 100000 records 0.466513156891 secs
SQLAlchemy Core: Total time for 100000 records 0.21024107933 secs
sqlite3: Total time for 100000 records 0.137335062027 sec
简单说明:同样添加10W行数据,插入时间比
1,Session.add(obj) 使用时间:6.89754080772秒
2,Session.add(obj)注意:手动添加了主键id 使用时间:4.09481811523秒
3,bulk_save_objects([obj1, obj2]) 使用时间:1.65821218491秒
4,bulk_inser_mappings(DBmodel, [dict1, dict2]) 使用时间: 0.466513156781秒
5,SQLAlchemy_core(DBmodel.__table__insert(), [dict1, dict2]) 使用时间:0.21024107933秒
6,直接执行execute(str_sql_insert) 直接执行sql插入语句 使用时间:0.137335062027秒
个人见解:
由上面的运行时间,我们知道直接执行sql语句是最快的,执行Session.add(obj)这样传统的方式属于比较慢。
这样是不是说明了,我们在项目中直接执行sql语句就是最优的?答案是:否认的
1,我们知道SQLAlchemy的出现就是想让我们更加方便快捷的操作数据库,而不是我们直接写sql语句(如果整个项目都直接用sql语句操作数据库的话,万一项目有漏洞让居心不良的人运行了delete table,那真的是GG了),。
2,还有一个问题,当表新加了一个字段的时候,你需要把整个项目中的insert语句,添加该添加的字段。(bulk_inser_mappings, SQLAlchemy_core中都可能存在这么一个问题)
3,很多同学都是习惯使用传统的Session.add(obj)的方式,我们需要在这基础上进行优化,避免在项目上有太多的改动。所以使用的是bulk_save_objects([obj1, obj2])这跟Session.add(obj)的区别就是,一次性添加,跟一个个添加的区别。
值得注意:
DBSession.configure(bind=engine)
时:autoflush默认是True,expire_on_commit也是True,这两个参数会影响你使用中是否自动把数据刷进数据或获得数据
应该把链接方式设置为:
DBSession.configure(bind=engine, autoflush=False, expire_on_commit=False)
具体可以看官网文档:Session API
话说,在项目中没有绝对可以用什么,不可以用什么。只有应该用什么,在一个项目中适应的使用不同的数据库语句会大大提高性能。这个需要建立在自己对应用场景,语句用法充分的理解。