On startup of my program, my in-memory sqlite DB needs to turn off foreign
key enforcement temporarily (in order to insert data from a different
sqlite DB). However, it seems my command to set foreign_keys back on has no
effect. See the attached MRE.
I expect this output:
after turning back on [(1,)]
But I get this:
after turning back on [(0,)]
Interestingly, if I comment out the insert statement (or put it before the
toggle) the code works fine.
Any ideas? I tried replicating this in the sqlite CLI but it works as I
expect:
SQLite version 3.35.4 2021-04-02 15:20:15
Enter ".help" for usage hints.
sqlite> pragma foreign_keys;
0
sqlite> pragma foreign_keys=on;
sqlite> pragma foreign_keys;
1
sqlite> create table groups (id primary key);
sqlite> pragma foreign_keys=off;
sqlite> pragma foreign_keys;
0
sqlite> insert into groups default values;
sqlite> pragma foreign_keys=on;
sqlite> pragma foreign_keys;
1
I'm using SQLAlchemy==1.3.22.
Thanks!
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/bebe39d3-6a91-4fdd-ab82-ea435315835dn%40googlegroups.com.
from sqlalchemy import Column, Integer, create_engine, event
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, Session as DBSession, configure_mappers
import sqlalchemy.pool
import sqlite3
def get_mem_conn():
return sqlite3.connect(':memory:', check_same_thread=False)
def get_engine():
engine = create_engine(
'sqlite://',
creator=lambda: conn,
# with NullPool i get 'cannot
poolclass=sqlalchemy.pool.StaticPool,
)
# https://stackoverflow.com/questions/2614984/sqlite-sqlalchemy-how-to-enforce-foreign-keys
from sqlalchemy import event
event.listen(engine, 'connect', _enable_fks)
return engine
def display_fk_pragma(conn, msg):
print(msg, conn.execute('pragma foreign_keys').fetchall())
def load_in_memory_db():
display_fk_pragma(conn, 'before temporarily disabling')
conn.execute('pragma foreign_keys=off')
conn.execute('INSERT INTO groups DEFAULT VALUES')
conn.execute('pragma foreign_keys=on')
display_fk_pragma(conn, 'after turning back on')
conn.commit()
def _enable_fks(c, _):
c.execute('pragma foreign_keys=on')
display_fk_pragma(c, 'after setting on in connect.listen')
Base = declarative_base()
class Group(Base):
__tablename__ = 'groups'
id = Column(Integer, primary_key=True)
def init_orm():
configure_mappers()
Base.metadata.create_all(engine)
load_in_memory_db()
conn = get_mem_conn()
engine = get_engine()
db: DBSession = sessionmaker(bind=engine)()
init_orm()
