ORM? SQLAlchemy? Since it has already been summarized in the following Qiita article, omit it and only the main subject https://qiita.com/tomo0/items/a762b1bc0f192a55eae8 https://qiita.com/ariku/items/75799665acd09520bed2
url = 'mysql+pymysql://%s:%s@%s:%s/%s?charset=%s' % (
self.user,
self.password,
self.host,
int(self.port),
self.db,
self.charset
)
self.engine = create_engine(url)
self.session = sessionmaker(bind=self.engine, autocommit=False, autoflush=True)()`
#Include subquery in IN clause
rows = db.session.query(User).filter(
User.id.in_(
db.session.query(UserItem.user_id).filter(
UserItem.item_id == 1, UserItem.numbers > 10
)
)
).all()
Like below
SELECT * FROM users WHERE id IN (
SELECT user_id FROM user_items WHERE item_id = 1 AND numbers > 10
)
SQLAlchemy can also express complex SQL such as SQL that uses subqueries in the IN clause
As a caveat, in ORM, what kind of SQL is issued behind the scenes is a black box, so if it is not a subtle SQL, add the following option at create_engine when debugging and output SQL I recommend you to check it as you do
echo_option = 'DEBUG' in os.environ and os.environ['DEBUG'] == 'on'
self.engine = create_engine(url, echo=echo_option)
self.session = sessionmaker(bind=self.engine, autocommit=False, autoflush=True)()`
Recommended Posts