4

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!

user1342336
  • 967
  • 2
  • 16
  • 28

3 Answers3

9

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()
Angelos
  • 1,632
  • 16
  • 25
van
  • 74,297
  • 13
  • 168
  • 171
0

SQLite has no logins.

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

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thank you. This is true that I need to add this code in some place. But the problem is how to add it. I will test. – user1342336 Dec 05 '12 at 14:35
0

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.
ntc2
  • 11,203
  • 7
  • 53
  • 70
  • This doesn't work as sqlite doesn't store this information in the file. Its based on connection. Reference: https://sqlite.org/forum/info/c5dc50f61b88c587 – Compro Prasad Jun 12 '22 at 11:56
  • @ComproPrasad **it works during the alembic migration script**. Yes, the setting does not persist after the migration script finishes. – ntc2 Jun 13 '22 at 12:14