5
votes

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.

1
SQLAlchemy major releases are not backwards compatible in many other ways too. At a guess: SQLite doesn't need a threadpool? - Martijn Pieters
Yes, SQLAlchemy does not need threadpool but we don't know "a priori" what type of database I will deal with. In most cases it is Oracle but in some it is SQLite. Putting 'pool_size' for everything was fine but not it breaks the code. - musthero
just an FYI, SQLAlchemy version changes from 0.X to 0.Y always contain many backwards incompatible changes, you can review them in the documentation: whats new in SQLAlchemy 0.7? SQLite now uses nullpool for file-based databases - zzzeek
Also, using the "pool_size" argument in 0.6 across both non-sqlite and sqlite database URLs is a bad idea as well. 0.6 uses an entirely different pool style for SQLite which means pool_size has totally different semantics. Different database backends will always require different configurational tweaks, it's best to have application code be sensitive to this, storing both the URL and a set of tuning **kwargs to use for each type of database. - zzzeek

1 Answers

12
votes

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)