title: Why is SQLAlchemy insert with sqlite 25 times slower than using sqlite3 directly?
body: <p>Why is this simple test case inserting 100,000 rows 25 times slower with SQLAlchemy than it is using the sqlite3 driver directly?  I have seen similar slowdowns in real-world applications.  Am I doing something wrong?</p>
  
  <pre><code>#!/usr/bin/env python
  # Why is SQLAlchemy with SQLite so slow?
  # Output from this program:
  # SqlAlchemy: Total time for 100000 records 10.74 secs
  # sqlite3:    Total time for 100000 records  0.40 secs
  
  
  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: 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 range(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(100000)
      test_sqlite3(100000)
  </code></pre>
  
  <p>I have tried numerous variations (see <a href="http://pastebin.com/zCmzDraU">http://pastebin.com/zCmzDraU</a> )</p>