CODEWITHSUNDEEP
pythonsqlitesqlalchemyalembic
user1342336
user1342336

Reputation: 977

How to turn on 'PRAGMA foreign_keys = ON' in sqlalchemy migration script or configuration file for sqlite?

In a suitable sqlite version, we can enforce foreign key constraint by 'PRAGMA foreign_keys = ON'. However user can not log in a database every time when making a connection. So I wonder how can we make it working in a migration script in sqlalchemy/alembic? Thanks very much!

Upvotes: 4

Views: 5085

Answers (3)

ntc2
ntc2

Reputation: 11912

In an Alembic migration script you can use raw SQL to turn on foreign key support like this:

def upgrade():
    from sqlalchemy.orm.session import Session
    session = Session(bind=op.get_bind())
    session.execute('PRAGMA foreign_keys = ON;')
    session.commit()
    # Actual migration logic would follow.

Upvotes: 0

van
van

Reputation: 76962

See Foreign Key Support from SA SQLite documentation:

import sqlite3

from sqlalchemy.engine import Engine
from sqlalchemy import event

@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    if type(dbapi_connection) is sqlite3.Connection:  # play well with other DB backends
       cursor = dbapi_connection.cursor()
       cursor.execute("PRAGMA foreign_keys=ON")
       cursor.close()

Upvotes: 9

CL.
CL.

Reputation: 180020

SQLite has no logins.

To enable foreign keys in a script, just add the PRAGMA foreign_keys = ON command to that script.

Upvotes: 0

Related Questions