SQLAlchemy: pool_size and SQLite

Question

The code

create_engine(sqlite:////infodb/timestamp.db', pool_size=10)

works fine in SQLAlchemy version 0.6.3. However, it does not work in version 0.7.1.

The error message I am getting is as follows

TypeError: Invalid argument(s) 'pool_size' sent to create_engine(), using configuration SQLiteDialect_pysqlite/NullPool/Engine. Please check that the keyword arguments are appropriate for this combination of components.

In version 0.7.1 the default value of the poolclass variable is an instance of sqlalchemy.pool.NullPool. In turn, the NullPool object does not accept pool_size variable. In version 0.6.3, poolclass was an instance of sqlalchemy.pool.SingletonThreadPool and thus there was no problem dealing with pool_size.

As a result, version SQLAlchemy 0.7.1 is not backward-compatible with 0.6.3. So I wondering, what is the reason for going from SingletonThreadPool to NullPool? It breaks the user's code.

Answer 1

From the SQLAlchemy 0.7 What's New document:

SQLite - the SQLite dialect now uses NullPool for file-based databases

This change is 99.999% backwards compatible, unless you are using temporary tables across connection pool connections.

A file-based SQLite connection is blazingly fast, and using NullPool means that each call to Engine.connect creates a new pysqlite connection.

Previously, the SingletonThreadPool was used, which meant that all connections to a certain engine in a thread would be the same connection. It’s intended that the new approach is more intuitive, particularly when multiple connections are used.

SingletonThreadPool is still the default engine when a :memory: database is used.

Note that this change breaks temporary tables used across Session commits, due to the way SQLite handles temp tables. See the note at http://www.sqlalchemy.org/docs/dialects/sqlite.html#using-temporary-tables-with-sqlite if temporary tables beyond the scope of one pool connection are desired.

#1921

Major SQLAlchemy releases (so between 0.5 and 0.6 or 0.6 to 0.7) so far have always included backwards incompatible changes as major ideas are being worked out. 0.7 is no exception, see the Backwards Incompatible API Changes section.

You can still provide a different pool class if you have to, by passing a poolclass keyword in with the connect call:

from sqlalchemy.pool import SingletonThreadPool

engine = create_engine('sqlite:///mydb.db', poolclass=SingletonThreadPool)

Passing in an explicit pool class also works on 0.6. Personally, I'd use exception handling here:

try:
    engine = create_engine(URL, pool_size=10)
except TypeError:
    # The pool_size argument won't work for the default SQLite setup in SQLAlchemy 0.7, try without
    engine = create_engine(URL)