[Python ORM] Notation when writing SQL using subquery in IN clause in SQLAlchemy

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

code

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
)

Summary

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

[Python ORM] Notation when writing SQL using subquery in IN clause in SQLAlchemy
Precautions when using pit in Python
When using regular expressions in Python
When writing a program in Python
[Tips] Easy-to-read writing when connecting functions in Python
Split files when writing vim plugin in python
A memorandum when writing experimental code ~ Logging in python
Dry-run sql query using psycopg2 on Redshift in Python
How to exit when using Python in Terminal (Mac)
Class notation in Python
Output Excel data in separate writing using Python3 + xlrd + mecab
A memo when creating a directed graph using Graphviz in Python
Convenient writing method when appending to list continuously in Python
Solve the Japanese problem when using the CSV module in Python.
A memo of writing a basic function in Python using recursion
Things to keep in mind when using Python with AtCoder
Scripts that can be used when using bottle in Python
Things to keep in mind when using cgi with python.
Attention when os.mkdir in Python
Convert CIDR notation in Python
Translate using googletrans in Python
Using Python mode in Processing
Things to watch out for when using default arguments in Python
Settings when writing Google App Engine / Python apps in Intellij Idea
I get a can't set attribute when using @property in python
[SEO] Flow / sample code when using Google Analytics API in Python
Don't forget shebang when writing Check! Ansible's Dynamic Inventory in python!
Included notation in Python function arguments
Behavior when listing in Python heapq
Try using LevelDB in Python (plyvel)
Using global variables in python functions
[Python] Be careful when using print
Let's see using input in python
Infinite product in Python (using functools)
Edit videos in Python using MoviePy
Reading and writing text in Python
Handwriting recognition using KNN in Python
Precautions when using phantomjs from python
Try using Leap Motion in Python
Depth-first search using stack in Python
When using MeCab with virtualenv python
Precautions when using six with Python 2.5
[VS Code] ~ Tips when using python ~
GUI creation in python using tkinter 2
What Emacs users should know when writing python code in Sublime Text
Notify using Notification Center when the execution environment is macOS in Python
A useful note when using Python for the first time in a while