CODEWITHSUNDEEP
sqlitesqlalchemy
user2097818
user2097818

Reputation: 1881

SQLite PRAGMA foreign_keys with SQLAlchemy

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.

Upvotes: 0

Views: 1536

Answers (1)

zzzeek
zzzeek

Reputation: 75127

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.

Upvotes: 1

Related Questions