sqlite setting foreign_keys=off temporarily

[RexE]

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!

[Jonathan Vanasco]

The first two things I would look into:

1. Check the sqlite install/version that SqlAlchemy uses.  It is often NOT the same as the basic operating system install invoked in your terminal.  Sometimes that version does not have the functionality you need.

2. Check the transactional isolation level in sqlalchemy and that you are committing if needed.  IIRC, the sqlite client defaults to non-transactional but the python library defaults to transactional.  I could be wrong on this.

Someone else may be able to look through your code and give more direct answers.