I just did a testing, basically copied and ran the code of below link: http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html in the middle of process, I copied and ran below code:
from sqlalchemy.engine import Enginefrom 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() then insert a couple rows to both user and addresses tables. I didn't insert user id '3', however I could insert foreign key user id '2' into addresses table. So foreign key constraint was not enforced. I am using >>> sqlalchemy.__version__ '0.7.8' See part of my codes below: >>> 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() ... >>> ins = users.insert().values(name='jack') >>> conn = engine.connect() >>> result = conn.execute(ins) 2012-12-05 11:36:52,181 INFO sqlalchemy.engine.base.Engine INSERT INTO users (na me) VALUES (?) 2012-12-05 11:36:52,181 INFO sqlalchemy.engine.base.Engine ('jack',) 2012-12-05 11:36:52,181 INFO sqlalchemy.engine.base.Engine COMMIT >>> result.inserted_primary_key [1] >>> conn.execute(addresses.insert(), [ ... {'user_id': 2, 'email_address' : 'j...@yahoo.com'}, ... {'user_id': 2, 'email_address' : 'we...@aol.com'}, ... ]) 2012-12-05 11:39:30,765 INFO sqlalchemy.engine.base.Engine INSERT INTO addresses (user_id, email_address) VALUES (?, ?) 2012-12-05 11:39:30,766 INFO sqlalchemy.engine.base.Engine ((2, 'j...@yahoo.com' ), (2, 'we...@aol.com')) 2012-12-05 11:39:30,766 INFO sqlalchemy.engine.base.Engine COMMIT <sqlalchemy.engine.base.ResultProxy object at 0x8fe3e8c> Best regards, On Wednesday, December 5, 2012 10:35:26 AM UTC-5, Audrius Kažukauskas wrote: > > On Wed, 2012-12-05 at 07:04:57 -0800, junepeach wrote: > > Thank you Audrius.Maybe when I ran 'alembic upgrade head', 'PRAGMA > > foreign_keys' value of the current sqlite DB connection was already > > changed to 1 which I need to test in a python code. However when I > > manually logged in by typing 'sqlite3 mydb', this is another > > connection, so the 'PRAGMA foreign_keys' value became '0' again. Not > > sure if I am correct about that? > > That's right, you need to execute 'PRAGMA foreign_keys=ON' every time > you connect to your DB, doesn't matter what client you use. There's no > permanent way to turn foreign keys constraint on. > > -- > Audrius Kažukauskas > http://neutrino.lt/ > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/LwHeVf8hGvUJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.