SQLite PRAGMA foreign_keys with SQLAlchemy

Question

I need to change the value of PRAGMA foreign_keys to 'off' before I do some operations, but then I would like to turn it right back 'on'.

Ultimately, I have only just gotten familiar with the ORM and havent had much time to get dirty with SQLAlchemy Core. When I import my Declarative modules, there is an import that makes sure my global engine has been started. Beyond that, I dont touch engines/pools/sessions.

I found this reference: How to turn on 'PRAGMA foreign_keys = ON' in sqlalchemy migration script or configuration file for sqlite?

from sqlalchemy.engine import Engine
from sqlalchemy import event

@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()

I could just stick this in my global engine configuration, and use a switch to PRAGMA on or PRAGMA off...

    if self.enforceDBFK:
        cursor.execute("PRAGMA foreign_keys=ON")
    else:
        cursor.execute("PRAGMA foreign_keys=OFF")

How do you 'unlisten' the event (that seems cleaner)?

Any other suggestions on how to do this.

Note from SQLite docs on PRAGMA foreign_keys:

foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT

Related Subtopic: On a second note, once I have the PRAGMA figured out, can I still use SQLAlchemy ORM instances to work on the tables (with foreign_keys=OFF)? SQLAlchemy doesn't enforce key integrity itself does it? How can I make SQLAlchemy also ignore foreign keys temporarily?

I just need to update a bunch of rows, but until the updates are completed as a group there will be broken/overlapping references all over the place.

score 1 · Accepted Answer · answered Apr 22 '13 at 17:17

1

we don't have an explicit API for event "removal" at this time though this is a feature that will eventually be available. So you'd need to create a single event that itself turns itself on and off based on a flag, seems like that's what you've already worked out.

answered Apr 22 '13 at 17:17

zzzeek

72,307
23
193
185

And just to clarify: Since this event happens on a new connection, what is the quickest way to force a new connection? `session.commit()`? I would like to do this from as high up as possible. – user2097818 Apr 28 '13 at 04:37
if you have NullPool in use then sure – zzzeek Apr 28 '13 at 22:59

SQLite PRAGMA foreign_keys with SQLAlchemy

1 Answers1