[PYTHON] Accelerate query generation with SQLAlchemy ORM

Suppose you have a table like this:

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

base = declarative_base()

class Xyz(base):
    __tablename__ = 'xyz'
    id = sa.Column(sa.INTEGER, primary_key=True)
    c1 = sa.Column(sa.INTEGER)
    c2 = sa.Column(sa.INTEGER)
    c3 = sa.Column(sa.INTEGER)
    c4 = sa.Column(sa.INTEGER)
    c5 = sa.Column(sa.INTEGER)
    c6 = sa.Column(sa.INTEGER)
    c7 = sa.Column(sa.INTEGER)
    c8 = sa.Column(sa.INTEGER)
    c9 = sa.Column(sa.INTEGER)

	@classmethod
	def from_c3(cls, n):
		return session.query(cls).filter_by(c3=n).one()

If this from_c3 () is taking a long time, the time it takes the ORM to generate the SQL can be checked as follows:

from sqlalchemy.orm import Query
from timeit import timeit

f1 = lambda: str(Query(Xyz).filter_by(c3=123))
print f1()
print timeit(f1, number=10000)

'''result
SELECT xyz.id AS xyz_id, xyz.c1 AS xyz_c1, xyz.c2 AS xyz_c2, xyz.c3 AS xyz_c3, xyz.c4 AS xyz_c4, xyz.c5 AS xyz_c5, xyz.c6 AS xyz_c6, xyz.c7 AS xyz_c7, xyz.c8 AS xyz_c8, xyz.c9 AS xyz_c9 
FROM xyz 
WHERE xyz.c3 = :c3_1
4.91327404976
'''

If this is taking a long time, you can speed up the query by caching the generated SQL.

compiled = str(Query(Xyz).filter_by(c3=sa.bindparam('c3')))
f2 = lambda: str(Query(Xyz).from_statement(compiled).params(c3=123))
print f2()
print timeit(f2, number=10000)

'''result
SELECT xyz.id AS xyz_id, xyz.c1 AS xyz_c1, xyz.c2 AS xyz_c2, xyz.c3 AS xyz_c3, xyz.c4 AS xyz_c4, xyz.c5 AS xyz_c5, xyz.c6 AS xyz_c6, xyz.c7 AS xyz_c7, xyz.c8 AS xyz_c8, xyz.c9 AS xyz_c9 
FROM xyz 
WHERE xyz.c3 = :c3
1.09750509262
'''

This SQL generation time depends on the complexity of the schema, so you can save more time on tables with more columns.

Postscript

Filter ('c3 =: c3') is easier than filter_by (c3 = sa.bindparam ('c3').