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.
Filter ('c3 =: c3')
is easier than filter_by (c3 = sa.bindparam ('c3')
.
Recommended Posts