Sqlite / SQLAlchemy: how to enforce Foreign Keys?

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 eventevent.listen(engine, 'connect', _fk_pragma_on_connect)

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 eventfrom sqlalchemy.engine import Enginefrom 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()

I now have this working:

Download the latest sqlite and pysqlite2 builds as described above: make sure correct versions are being used at runtime by python.

import sqlite3   import pysqlite2 print sqlite3.sqlite_version   # should be 3.6.23.1print pysqlite2.__path__       # eg C:\\Python26\\lib\\site-packages\\pysqlite2

Next add a PoolListener:

from sqlalchemy.interfaces import PoolListenerclass ForeignKeysListener(PoolListener):    def connect(self, dbapi_con, con_record):        db_cursor = dbapi_con.execute('pragma foreign_keys=ON')engine = create_engine(database_url, listeners=[ForeignKeysListener()])

Then be careful how you test if foreign keys are working: I had some confusion here. When using sqlalchemy ORM to add() things my import code was implicitly handling the relation hookups so could never fail. Adding nullable=False to some ForeignKey() statements helped me here.

The way I test sqlalchemy sqlite foreign key support is enabled is to do a manual insert from a declarative ORM class:

# exampleins = Coverage.__table__.insert().values(id = 99,                                    description = 'Wrong',                                    area = 42.0,                                    wall_id = 99,  # invalid fkey id                                    type_id = 99)  # invalid fkey_idsession.execute(ins) 

Here wall_id and type_id are both ForeignKey()'s and sqlite throws an exception correctly now if trying to hookup invalid fkeys. So it works! If you remove the listener then sqlalchemy will happily add invalid entries.

I believe the main problem may be multiple sqlite3.dll's (or .so) lying around.