1. #!/usr/bin/env python
  2. # Whis is SQLAlchemy with SQLite so slow?
  3. # Output from this program:
  4. # SqlAlchemy: Total time for 100000 records 10.74 secs
  5. # sqlite3:    Total time for 100000 records  0.40 secs
  6. # By Braddock Gaskill 8/1/2012
  7.  
  8. import time
  9. import sqlite3
  10.  
  11. from sqlalchemy.ext.declarative import declarative_base
  12. from sqlalchemy import Column, Integer, String,  create_engine
  13. from sqlalchemy.orm import scoped_session, sessionmaker
  14.  
  15. Base = declarative_base()
  16. DBSession = scoped_session(sessionmaker())
  17.  
  18. class Customer(Base):
  19.     __tablename__ = "customer"
  20.     id = Column(Integer, primary_key=True)
  21.     name = Column(String(255))
  22.  
  23. def init_sqlalchemy(dbname = 'sqlite:///sqlalchemy.db'):
  24.     engine  = create_engine(dbname, echo=False)
  25.     DBSession.configure(bind=engine, autoflush=False, expire_on_commit=False)
  26.     Base.metadata.drop_all(engine)
  27.     Base.metadata.create_all(engine)
  28.  
  29. def test_sqlalchemy(n=100000):
  30.     init_sqlalchemy()
  31.     t0 = time.time()
  32.     for i in range(n):
  33.         customer = Customer()
  34.         customer.name = 'NAME ' + str(i)
  35.         DBSession.add(customer)
  36.     DBSession.commit()
  37.     print "SqlAlchemy: Total time for " + str(n) + " records " + str(time.time() - t0) + " secs"
  38.  
  39. def test_sqlalchemy_insert(n=10000):
  40.     t0 = time.time()
  41.     for i in range(n):
  42.         ins = Customer.__table__.insert().values(name = 'NAME ' + str(i))
  43.         DBSession.execute(ins)
  44.     DBSession.commit()
  45.     print "SqlAlchemy Insert: Total time for " + str(n) + " records " + str(time.time() - t0) + " secs"
  46.  
  47. def init_sqlite3(dbname):
  48.     conn = sqlite3.connect(dbname)
  49.     c = conn.cursor()
  50.     c.execute("DROP TABLE IF EXISTS customer")
  51.     c.execute("CREATE TABLE customer (id INTEGER NOT NULL, name VARCHAR(255), guid VARCHAR(32), PRIMARY KEY(id))")
  52.     conn.commit()
  53.     return conn
  54.  
  55. def test_sqlite3(n=100000, dbname = 'sqlite3.db'):
  56.     conn = init_sqlite3(dbname)
  57.     c = conn.cursor()
  58.     t0 = time.time()
  59.     for i in range(n):
  60.         row = ('NAME ' + str(i),)
  61.         c.execute("INSERT INTO customer (name) VALUES (?)", row)
  62.     conn.commit()
  63.     print "sqlite3: Total time for " + str(n) + " records " + str(time.time() - t0) + " sec"
  64.  
  65. if __name__ == '__main__':
  66.     test_sqlalchemy(100000)
  67.     test_sqlite3(100000)
  68.     test_sqlalchemy_insert(100000)