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' : '[email protected]'},
... {'user_id': 2, 'email_address' : '[email protected]'},
... ])
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,
'[email protected]' ),
(2, '[email protected]'))
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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
