SQLAlchemy - in_() and notin_() with an empty list

18 Mar 2017

Updated 2026-07-16: clarified the current behavior and cited the SQLAlchemy docs.

Before SQLAlchemy 1.2.0, an empty list passed to in_() emitted SQL that queried your entire table. Since 1.2.0 this is handled: an empty in_() renders a backend-specific empty-set expression that matches no rows, and an empty notin_() one that matches all rows, so neither scans the table. If you are stuck on an older version, upgrading is the fix.

The gist below shows the old behavior and the query it produced:

# demonstrates the issue fixed in: https://bitbucket.org/zzzeek/sqlalchemy/issues/3907 from sqlalchemy import create_engine, Column, Integer from sqlalchemy.orm import scoped_session, sessionmaker from sqlalchemy.ext.declarative import declarative_base engine = create_engine('mysql://root@localhost/test?charset=utf8mb4', convert_unicode=True, echo=True) session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine)) Base = declarative_base() Base.query = session.query_property() class Post(Base): __tablename__ = 'posts' id = Column(Integer, primary_key=True) #Base.metadata.drop_all(engine) Base.metadata.create_all(engine) # create new rows if database is empty for x in range(10): session.add(Post()) session.commit() # empty lists trigger insane queries before 1.2.0 Post.query.filter(Post.id.in_([])).all() """ SELECT posts.id AS posts_id FROM posts WHERE posts.id != posts.id """

The Robustness Principle

14 Mar 2017

I learned about this at a talk called “Implementing Evolvable APIs” at SXSW: Wikipedia: Robustness principle

For example, making an API that throws errors when an unexpected parameter is provided is a bad idea. What if you need to make changes to the client to add the new parameter? You will need to make sure you deploy the code on the server side first, otherwise it will cause errors.

SQLAlchemy - DISTINCT, LIMIT, or OFFSET Causing Subqueries

15 Feb 2017

Updated 2026-07-16: fixed the docs link, confirmed it still applies in 2.0, and added the 2.0 syntax alongside the original.

This part of The Zen of Joined Eager Loading is really important, and still applies in SQLAlchemy 2.0:

When using joined eager loading, if the query contains a modifier that impacts the rows returned externally to the joins, such as when using DISTINCT, LIMIT, OFFSET or equivalent, the completed statement is first wrapped inside a subquery, and the joins used specifically for joined eager loading are applied to the subquery. SQLAlchemy’s joined eager loading goes the extra mile, and then ten miles further, to absolutely ensure that it does not affect the end result of the query, only the way collections and related objects are loaded, no matter what the format of the query is.

I made an example to illustrate this:

from sqlalchemy import create_engine, Column, ForeignKey, Integer from sqlalchemy.orm import relationship, scoped_session, sessionmaker from sqlalchemy.ext.declarative import declarative_base engine = create_engine('mysql://root@localhost/test?charset=utf8mb4', convert_unicode=True) session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine)) Base = declarative_base() Base.query = session.query_property() class Post(Base): __tablename__ = 'posts' id = Column(Integer, primary_key=True) products = relationship( 'Product', lazy='joined', ) class Product(Base): __tablename__ = 'products' id = Column(Integer, primary_key=True) post_id = Column(Integer, ForeignKey('posts.id'), index=True) #Base.metadata.drop_all(engine) Base.metadata.create_all(engine) first_result = Post.query.first() if not first_result: for x in range(50): products = [Product() for y in range(12)] session.add(Post(products=products)) session.commit() print(Post.query.limit(20))

On MySQL this can be responsible for some really poor query performance, because it can cause it to use temporary tables and filesort.

The best way I’ve found to prevent the subqueries is by first querying for the ids only, then running another query that includes all relations:

ids = session.query(Product.id).limit(20)
Product.query.filter(Product.id.in_(ids))

In SQLAlchemy 2.0, the same approach with select():

ids = select(Product.id).limit(20)
products = session.scalars(
    select(Product).where(Product.id.in_(ids))
).all()

SQLAlchemy - "Base.query = db_session.query_property()"

13 Feb 2017

I just saw this line in the SQLAlchemy flask patterns example:

Base.query = db_session.query_property()

I went searching for what it does and found this: https://eoyilmaz.blogspot.com/2014/01/i-feel-so-much-hungry-about-posting.html

Now I know how Flask-SQLAlchemy made queries work with Model.query instead of session.query(Model).

SQLAlchemy - Lost connection to MySQL server during query

11 Feb 2017

Here’s was my situation:

The database was set up behind behind an AWS ELB and HAProxy.
The idle connection timeout on the ELB was set to 60 mins.
All the relevant timeouts on HAProxy seemed to be set to 60 mins too.
The pool_recycle in SQLAlchemy was set to 30 mins.
I was still seeing the occasional “Lost connection to MySQL server during query” when small queries were running after the connection had some time to sit around.

The solution ended up being setting my pool_recycle down to 5 mins, but I’m still not sure what was causing connections to time out after 5 mins.

There are definitely other things that can cause this problem too. For example, it can happen if your data exceeds max_allowed_packet. See this page for more details: https://dev.mysql.com/doc/refman/5.7/en/error-lost-connection.html

Most of this also applies for “MySQL server has gone away”.

You should also make sure your pool_recycle is set lower than your ‘interactive_timeout’ and ‘wait_timeout’ properties in the mysql config file to the values you need.

SQLAlchemy - Unexpected Lazy Loading

27 Jan 2017

If you’re seeing unexpected lazy loading on a lazy=”joined” relationship in SQLAlchemy, it might be because you’re accessing those relationships after you’ve already run session.commit(). By default, session.commit() will expire the data on your relationships, meaning it will try to fetch it again next time you try to access those attributes.

The relevant section of the docs for session.commit():

By default, the Session also expires all database loaded state on all ORM-managed attributes after transaction commit. This so that subsequent operations load the most recent data from the database. This behavior can be disabled using the expire_on_commit=False option to sessionmaker or the Session constructor.

Python - David Beazley's "Understanding The Python GIL"

20 Jan 2017

This talk explains a lot about Python’s multithreading: http://www.dabeaz.com/python/UnderstandingGIL.pdf

In Python 2.7, it knows to switch to different thread whenever it starts waiting on IO. And, it will check if it needs to switch threads every 100 bytecode instructions.

SQLAlchemy - Is it necessary to commit after session.execute()?

17 Jan 2017

Updated 2026-07-16: updated the answer and links for SQLAlchemy 2.0.

Session.execute runs its statement within the current transaction, and SQLAlchemy’s autobegin starts that transaction on first use.

So if the statement writes (INSERT, UPDATE, DELETE), yes, you need to commit(), or the transaction rolls back when the session closes. A read-only SELECT changes nothing, so there is nothing to commit.

Here’s a code example along with the output from echo=True, showing the session begins a transaction but doesn’t end it until you run session.commit():

from sqlalchemy import create_engine from sqlalchemy.orm import scoped_session, sessionmaker engine = create_engine('mysql://root@localhost/test') db_session = scoped_session(sessionmaker(bind=engine)) db_session.execute(''' INSERT INTO user (first_name, last_name, username, email) VALUES ("blah", "blah", "blah", "blah@blah.com"); ''')

Global .gitignore

27 Nov 2016

Create a global .gitignore (GitHub Docs)

SQLAlchemy 1.1.4's New "server_side_cursors" Option

18 Nov 2016

Before SQLAlchemy 1.1.4, if you wanted to stream your MySQL query using server side cursors, you would need to run your query using execution_options(stream_results=True) and pass SSCursor into create_engine’s connect_args. Now, all you need to do is pass server_side_cursors=True into create_engine and it will automatically stream the results for your select queries. SQLAlchemy 1.4 deprecated server_side_cursors in favor of stream_results=True.

http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#server-side-cursors

Older Newer