SQLAlchemy批量插入性能比较

[python]  view plain  copy

1. import time  
2. import sqlite3  
3.   
4. from sqlalchemy.ext.declarative import declarative_base  
5. from sqlalchemy import Column, Integer, String,  create_engine  
6. from sqlalchemy.orm import scoped_session, sessionmaker  
7.   
8. Base = declarative_base()  
9. DBSession = scoped_session(sessionmaker())  
10. engine = None  
11.   
12.   
13. class Customer(Base):  
14.     __tablename__ = "customer"  
15.     id = Column(Integer, primary_key=True)  
16.     name = Column(String(255))  
17.   
18.   
19. def init_sqlalchemy(dbname='sqlite:///sqlalchemy.db'):  
20.     global engine  
21.     engine = create_engine(dbname, echo=False)  
22.     DBSession.remove()  
23.     DBSession.configure(bind=engine, autoflush=False, expire_on_commit=False)  
24.     Base.metadata.drop_all(engine)  
25.     Base.metadata.create_all(engine)  
26.   
27.   
28. def test_sqlalchemy_orm(n=100000):  
29.     init_sqlalchemy()  
30.     t0 = time.time()  
31.     for i in xrange(n):  
32.         customer = Customer()  
33.         customer.name = 'NAME ' + str(i)  
34.         DBSession.add(customer)  
35.         if i % 1000 == 0:  
36.             DBSession.flush()  
37.     DBSession.commit()  
38.     print(  
39.         "SQLAlchemy ORM: Total time for " + str(n) +  
40.         " records " + str(time.time() - t0) + " secs")  
41.   
42.   
43. def test_sqlalchemy_orm_pk_given(n=100000):  
44.     init_sqlalchemy()  
45.     t0 = time.time()  
46.     for i in xrange(n):  
47.         customer = Customer(id=i+1, name="NAME " + str(i))  
48.         DBSession.add(customer)  
49.         if i % 1000 == 0:  
50.             DBSession.flush()  
51.     DBSession.commit()  
52.     print(  
53.         "SQLAlchemy ORM pk given: Total time for " + str(n) +  
54.         " records " + str(time.time() - t0) + " secs")  
55.   
56.   
57. def test_sqlalchemy_orm_bulk_save_objects(n=100000):  
58.     init_sqlalchemy()  
59.     t0 = time.time()  
60.     n1 = n  
61.     while n1 > 0:  
62.         n1 = n1 - 10000  
63.         DBSession.bulk_save_objects(  
64.             [  
65.                 Customer(name="NAME " + str(i))  
66.                 for i in xrange(min(10000, n1))  
67.             ]  
68.         )  
69.     DBSession.commit()  
70.     print(  
71.         "SQLAlchemy ORM bulk_save_objects(): Total time for " + str(n) +  
72.         " records " + str(time.time() - t0) + " secs")  
73.   
74. def test_sqlalchemy_orm_bulk_insert(n=100000):  
75.     init_sqlalchemy()  
76.     t0 = time.time()  
77.     n1 = n  
78.     while n1 > 0:  
79.         DBSession.bulk_insert_mappings(  
80.             Customer,  
81.             [  
82.                 dict(name="NAME " + str(i))  
83.                 for i in xrange(min(10000, n1))  
84.             ]  
85.         )  
86.         n1 = n1 - 10000  
87.     DBSession.commit()  
88.     print(  
89.         "SQLAlchemy ORM bulk_insert_mappings(): Total time for " + str(n) +  
90.         " records " + str(time.time() - t0) + " secs")  
91.   
92. def test_sqlalchemy_core(n=100000):  
93.     init_sqlalchemy()  
94.     t0 = time.time()  
95.     engine.execute(  
96.         Customer.__table__.insert(),  
97.         [{"name": 'NAME ' + str(i)} for i in xrange(n)]  
98.     )  ##==> engine.execute('insert into ttable (name) values ("NAME"), ("NAME2")')  
99.     print(  
100.         "SQLAlchemy Core: Total time for " + str(n) +  
101.         " records " + str(time.time() - t0) + " secs")  
102.   
103.   
104. def init_sqlite3(dbname):  
105.     conn = sqlite3.connect(dbname)  
106.     c = conn.cursor()  
107.     c.execute("DROP TABLE IF EXISTS customer")  
108.     c.execute(  
109.         "CREATE TABLE customer (id INTEGER NOT NULL, "  
110.         "name VARCHAR(255), PRIMARY KEY(id))")  
111.     conn.commit()  
112.     return conn  
113.   
114.   
115. def test_sqlite3(n=100000, dbname='sqlite3.db'):  
116.     conn = init_sqlite3(dbname)  
117.     c = conn.cursor()  
118.     t0 = time.time()  
119.     for i in xrange(n):  
120.         row = ('NAME ' + str(i),)  
121.         c.execute("INSERT INTO customer (name) VALUES (?)", row)  
122.     conn.commit()  
123.     print(  
124.         "sqlite3: Total time for " + str(n) +  
125.         " records " + str(time.time() - t0) + " sec")  
126.   
127. if __name__ == '__main__':  
128.     test_sqlalchemy_orm(100000)  
129.     test_sqlalchemy_orm_pk_given(100000)  
130.     test_sqlalchemy_orm_bulk_save_objects(100000)  
131.     test_sqlalchemy_orm_bulk_insert(100000)  
132.     test_sqlalchemy_core(100000)  
133.     test_sqlite3(100000)  

上面代码分别使用了orm， orm带主键，orm的bulk_save_objects, orm的bulk_insert_mappings, 非orm形式，原生的dbapi方式；插入10000条记录的结果如下：

[plain]  view plain  copy

1. SQLAlchemy ORM: Total time for 100000 records 7.2070479393 secs  
2. SQLAlchemy ORM pk given: Total time for 100000 records 4.28471207619 secs  
3. SQLAlchemy ORM bulk_save_objects(): Total time for 100000 records 1.58296084404 secs  
4. SQLAlchemy ORM bulk_insert_mappings(): Total time for 100000 records 0.453973054886 secs  
5. SQLAlchemy Core: Total time for 100000 records 0.210998058319 secs  
6. sqlite3: Total time for 100000 records 0.136252880096 sec  

注意：

这里只有第一、二种方式可以拿到插入记录的id，其它都不能直接拿到，需要自己再查询一次。

原文连接：http://docs.sqlalchemy.org/en/latest/faq/performance.html