Do we have a sqlalchemy code or migration script to enforce foreign key constraint for sqlite3?

[junepeach]

We can turn on 'PRAGMA foreign_key = on' when running sqlite3 database system manually. However it is not a convenient way. So I wonder when I run the migration script using sqlalchemy and alembic tool, targeting sqlite3, is there a way to enforce the foreign key constraint in the migration script, or configuration script.

Thanks and best regards!

[Audrius Kažukauskas]

In SQLAlchemy it can be done using events, see
<http://docs.sqlalchemy.org/en/rel_0_7/dialects/sqlite.html#foreign-key-support>.
Can't say anything about Alembic, as I haven't played with it yet
(though I'm planning to).

--
Audrius Kažukauskas
http://neutrino.lt/

[junepeach]

Thanks Audrius. Do you have a simple working example in sqlalchemy? I am not familiar with sqlalchemy. My migration script will separate from other python development codes. So it is better to put 'PRAGMA foreign_keys=ON' in a configuration file. I am pretty new to all of those stuff.

[Audrius Kažukauskas]

I assume that by "configuration script" you mean env.py (as I said in my
previous mail, I'm not familiar with Alembic). According to the docs,
that's the place where SA engine is created. Have you tried to place
the code snippet from the link that I gave you in env.py? Perhaps
that is all that's needed for SQLite's foreign key constraint to take
effect in Alembic migration scripts.

[junepeach]

I have tried this with no luck.

[Michael Bayer]

the FK constraint event is described here:

http://docs.sqlalchemy.org/en/rel_0_8/dialects/sqlite.html#foreign-key-support

this code can go whereever it is you build your engine, in env.py, or elsewhere.

If the above isn't working, check the prerequisites mentioned, especially version of sqlite in use when you run Python (which can be different than the version of SQLite you get when you type "sqlite3").

On Dec 4, 2012, at 4:19 PM, junepeach wrote:

> I have tried this with no luck.
>

> --
> 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/-/WIhv75pJw3gJ.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
>

[junepeach]

I am Python 2.7.3, and SQLite version 3.7.9. I added the code you guys mentioned above to the env.py script:

from __future__ import with_statement
from alembic import context
from sqlalchemy import engine_from_config, pool
from logging.config import fileConfig

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)
......
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()

def run_migrations_offline():

...

g
Then I ran 'alembic revision --autogenerate' and 'alembic upgrade head' commands. My testing tables were created as usual. When I logged in sqlite by 'sqlite3 mydb', and checked 'PRAGMA foreign_keys', it is still 0. There is no change yet. What is wrong?

Thanks a lot!

[junepeach]

I think one of my problems is after defining:
def set_sqlite_pragma(dbapi_connection, connection_record) in env.py,
Where I should call it and what are the values of 2 arguments: dbapi_connection, connection_record?

[Audrius Kažukauskas]

On Wed, 2012-12-05 at 06:32:46 -0800, junepeach wrote:
> When I logged in sqlite by 'sqlite3 mydb', and checked 'PRAGMA
> foreign_keys', it is still 0. There is no change yet. What is wrong?

It doesn't work like that. From
<http://www.sqlite.org/foreignkeys.html>:

Foreign key constraints are disabled by default (for backwards
compatibility), so must be enabled separately for each database
connection separately.

That's the purpose of the event handler you have included in env.py --
to execute 'PRAGMA foreign_keys=ON' for each new connection.

[Audrius Kažukauskas]

You don't have to call it, the event handler is called every time when
new connection is made to your SQLite database (note the decorator above
it).

[junepeach]

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?

Thanks a lot!

[junepeach]

I was wrong in above post.

[Audrius Kažukauskas]

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.

[junepeach]

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 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

()

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' : 'ja...@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, 'ja...@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,

[Audrius Kažukauskas]

On Wed, 2012-12-05 at 09:07:20 -0800, junepeach wrote:
> 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 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()
>
> 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.

You need to register the event handler before you make any attempts to
connect to your database. See attached the example that works as
expected (fails with IntegrityError exception due to foreign key
constraint).

[junepeach]

Audrius Kažukauskas
Hi Audrius,

Your code works like a charm. Thank you so much for your help!
I really appreciate!