Here is the SQLAlchemy version of your MySQL script that performs in four seconds, compared to three for MySQLdb:
from sqlalchemy import Integer, Column, create_engine, MetaData, Table import datetime metadata = MetaData() foo = Table( 'foo', metadata, Column('id', Integer, primary_key=True), Column('a', Integer(), nullable=False), Column('b', Integer(), nullable=False), Column('c', Integer(), nullable=False), ) class Foo(object): def __init__(self, a, b, c): self.a = a self.b = b self.c = c engine = create_engine('mysql+mysqldb://scott:tiger@localhost/test', echo=True) start = datetime.datetime.now() with engine.connect() as conn: foos = [ Foo(row['a'], row['b'], row['c']) for row in conn.execute(foo.select().limit(1000000)).fetchall() ] print "total time: ", datetime.datetime.now() - start
total time: 0:00:04.706010
Here is a script that uses the ORM to load object rows fully; by avoiding the creation of a fixed list with all 1M objects at once using yield per, this runs in 13 seconds with SQLAlchemy master (18 seconds with rel 0.9):
import time from sqlalchemy import Integer, Column, create_engine, Table from sqlalchemy.orm import Session from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Foo(Base): __table__ = Table( 'foo', Base.metadata, Column('id', Integer, primary_key=True), Column('a', Integer(), nullable=False), Column('b', Integer(), nullable=False), Column('c', Integer(), nullable=False), ) engine = create_engine('mysql+mysqldb://scott:tiger@localhost/test', echo=True) sess = Session(engine) now = time.time() # avoid using all() so that we don't have the overhead of building # a large list of full objects in memory for obj in sess.query(Foo).yield_per(100).limit(1000000): pass print("Total time: %d" % (time.time() - now))
We can then split the difference between these two approaches, and load just individual columns with the ORM:
for obj in sess.query(Foo.id, Foo.a, Foo.b, Foo.c).yield_per(100).limit(1000000): pass
The above again runs in 4 seconds.
The comparison of SQLAlchemy Core is the more apt comparison to a raw MySQLdb cursor. If you use the ORM but query for individual columns, it’s about four seconds in most recent versions.
At the ORM level, the speed issues are because creating objects in Python is slow, and the SQLAlchemy ORM applies a large amount of bookkeeping to these objects as it fetches them, which is necessary in order for it to fulfill its usage contract, including unit of work, identity map, eager loading, collections, etc.
To speed up the query dramatically, fetch individual columns instead of full objects. See the techniques at
http://docs.sqlalchemy.org/en/latest/faq/performance.html#result-fetching-slowness-orm which describe this.
For your comparison to PeeWee, PW is a much simpler system with a lot less features, including that it doesn’t do anything with identity maps. Even with PeeWee, about as simple of an ORM as is feasible, it still takes 15 seconds, which is evidence that cPython is really really slow compared to the raw MySQLdb fetch which is in straight C.
For comparison to Java, the Java VM is way way way faster than cPython. Hibernate is ridiculously complicated, yet the Java VM is extremely fast due to the JIT and even all that complexity ends up running faster. If you want to compare Python to Java, use Pypy.