Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/usr/bin/env python
- # Whis 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
- # By Braddock Gaskill 8/1/2012
- 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 test_sqlalchemy_insert(n=10000):
- t0 = time.time()
- for i in range(n):
- ins = Customer.__table__.insert().values(name = 'NAME ' + str(i))
- DBSession.execute(ins)
- DBSession.commit()
- print "SqlAlchemy Insert: 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), guid VARCHAR(32), 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)
- test_sqlalchemy_insert(100000)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement