python - Sqlite / SQLAlchemy: how to enforce Foreign Keys?

Question

The new version of SQLite has the ability to enforce Foreign Key constraints, but for the sake of backwards-compatibility, you have to turn it on for each database connection separately!

sqlite> PRAGMA foreign_keys = ON;

I am using SQLAlchemy -- how can I make sure this always gets turned on? What I have tried is this:

engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)
engine.execute('pragma foreign_keys=on')

...but it is not working!...What am I missing?

EDIT: I think my real problem is that I have more than one version of SQLite installed, and Python is not using the latest one!

>>> import sqlite3
>>> print sqlite3.sqlite_version
3.3.4

But I just downloaded 3.6.23 and put the exe in my project directory! How can I figure out which .exe it's using, and change it?

question from:https://stackoverflow.com/questions/2614984/sqlite-sqlalchemy-how-to-enforce-foreign-keys

Answer 1

For recent versions (SQLAlchemy ~0.7) the SQLAlchemy homepage says:

PoolListener is deprecated. Please refer to PoolEvents.

Then the example by CarlS becomes:

engine = create_engine(database_url)

def _fk_pragma_on_connect(dbapi_con, con_record):
    dbapi_con.execute('pragma foreign_keys=ON')

from sqlalchemy import event
event.listen(engine, 'connect', _fk_pragma_on_connect)