为什么使用SQLAlchemy插入数据到sqlite的速度比直接使用sqlite3慢25倍？

7 浏览
0 Comments

为什么使用SQLAlchemy插入数据到sqlite的速度比直接使用sqlite3慢25倍？

为什么使用SQLAlchemy的这个简单测试案例比直接使用sqlite3驱动插入100,000行数据慢25倍？我在真实应用中也遇到了类似的减速问题。我做错了什么吗？

#!/usr/bin/env python
# 为什么SQLAlchemy和SQLite这么慢？
# 该程序的输出：
# SqlAlchemy: 100000条记录的总时间为10.74秒
# sqlite3:    100000条记录的总时间为0.40秒
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())
class Customer(Base):
    __tablename__ = "customer"
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
def init_sqlalchemy(dbname = 'sqlite:///sqlalchemy.db'):
    engine  = create_engine(dbname, echo=False)
    DBSession.configure(bind=engine, autoflush=False, expire_on_commit=False)
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
def test_sqlalchemy(n=100000):
    init_sqlalchemy()
    t0 = time.time()
    for i in range(n):
        customer = Customer()
        customer.name = 'NAME ' + str(i)
        DBSession.add(customer)
    DBSession.commit()
    print "SqlAlchemy: 100000条记录的总时间为 " + str(time.time() - t0) + " 秒"
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 range(n):
        row = ('NAME ' + str(i),)
        c.execute("INSERT INTO customer (name) VALUES (?)", row)
    conn.commit()
    print "sqlite3: 100000条记录的总时间为 " + str(time.time() - t0) + " 秒"
if __name__ == '__main__':
    test_sqlalchemy(100000)
    test_sqlite3(100000)

我已经尝试了多种变化（请参见http://pastebin.com/zCmzDraU）

0