how to get bound parameters of sqlalchemy query as dictionary?

Question

I know there's a way to print an actual sql query from any sqlalchemy query object. However, I want to use a query as a parameterized form without parameters's value being rendered.

For instance, printed query looks like below.

SELECT "user".id AS user_id, "user".hashed_pw AS user_hashed_pw, 
"user".name AS user_name, "user".tel AS user_tel, "user".email AS user_email, 
"user".created_time AS user_created_time FROM "user" 
WHERE "user".name = :name_1 AND "user".age < :age_1

I passed name_1 as 'john' and age_1 as 30, but I don't know how to get the values of name_1 and age_1. I want to get a dictionary like {'name_1':'john', 'age_1':30}.

How can I do this?

Answer 1

When an ORM query is compiled, it's turned into sqlalchemy.engine.Compiled. It has statement attribute which is a sqlalchemy.sql.expression.ClauseElement which has params attribute.

In [3]: query = session.query(User).filter(User.age > 18, User.name == 'joe')
   ...: query.statement.compile().params
Out[3]: {'age_1': 18, 'name_1': 'joe'}

And typical SQLAlchemy ORM boilerplate for completeness.

In [1]: import sqlalchemy as sa
   ...: from sqlalchemy.ext.declarative import declarative_base
   ...: from sqlalchemy.sql import func
   ...:
   ...: Base = declarative_base()
   ...:
   ...: class User(Base):
   ...:
   ...:     __tablename__ = 'user'
   ...:
   ...:     id = sa.Column(sa.Integer, primary_key=True)
   ...:     age = sa.Column(sa.Integer)
   ...:     hashed_pw = sa.Column(sa.String)
   ...:     name = sa.Column(sa.String)
   ...:     tel = sa.Column(sa.String)
   ...:     email = sa.Column(sa.String)
   ...:     created_time = sa.Column(sa.DateTime, server_default=func.now())

In [2]: engine = sa.create_engine('sqlite://')
   ...: Base.metadata.create_all(engine)
   ...: Session = sa.orm.sessionmaker(bind=engine)
   ...: session = Session()
   ...:
   ...: user1 = User(age=20, name='joe')
   ...: user2 = User(age=22, name='jon')
   ...: session.add_all([user1, user2])
   ...: session.commit()