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?

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) 

Answer 2

Building on the answers from conny and shadowmatter, here's code that will check if you are using SQLite3 before emitting the PRAGMA statement:

from sqlalchemy import event from sqlalchemy.engine import Engine from sqlite3 import Connection as SQLite3Connection  @event.listens_for(Engine, "connect") def _set_sqlite_pragma(dbapi_connection, connection_record):     if isinstance(dbapi_connection, SQLite3Connection):         cursor = dbapi_connection.cursor()         cursor.execute("PRAGMA foreign_keys=ON;")         cursor.close()