I want to convert a resultproxy object into a subquery for use with an ORM query. What's the procedure to do this? I can't really find any examples.
Add a comment
|
1 Answer
A result proxy is something that you get when a SELECT statement is passed to the database and executed - at that point, there's no more "query" so in a literal sense, a result proxy isn't a subquery. However, that SELECT statement which you have executed, can instead not be executed and passed into another query as a subquery pretty easily:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
data = Column(String)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
sess = Session(e)
# test data.
sess.add_all([A(data="d%d" % i) for i in range(10)])
sess.commit()
# a query.
subq = sess.query(A.id).filter(A.data.in_(["d3", "d4", "d5"]))
# execute subquery, gives us a list of tuples
list_of_tuples = subq.all()
# execute subquery via Core, gives us a ResultProxy
result_proxy = sess.execute(subq)
# inject subq into another Query as a subquery()
q = sess.query(A).filter(A.data.in_(subq.subquery()))
a_from_subquery = q.all()
