classification
Title: sqlite3: PRAGMA foreign_keys = ON doesn't work
Type: behavior Stage: resolved
Components: None Versions: Python 2.7
process
Status: closed Resolution: not a bug
Dependencies: Superseder:
Assigned To: Nosy List: Mark.Bucciarelli, ghaering, ned.deily, poq
Priority: normal Keywords:

Created on 2011-09-16 22:54 by Mark.Bucciarelli, last changed 2011-10-17 22:48 by ned.deily. This issue is now closed.

Files
File name Uploaded Description Edit
t.py Mark.Bucciarelli, 2011-09-16 22:54 Script that should raise error but does not.
Messages (8)
msg144166 - (view) Author: Mark Bucciarelli (Mark.Bucciarelli) Date: 2011-09-16 22:54
If I read http://bugs.python.org/issue10740#msg132470 correctly, the foreign_keys PRAGMA is a no-op b/c "The python sqlite module automatically commits open transactions when it encounters a DDL statement."

Entering as a separate issue as I'm not sure solving 10740 is required to solve this one.
msg144220 - (view) Author: (poq) Date: 2011-09-17 21:26
Works for me?

$ python2.7 t.py
Traceback (most recent call last):
  File "t.py", line 13, in <module>
    con.execute("insert into track (artist_id) values (1)")
sqlite3.IntegrityError: foreign key constraint failed
$ python3.2 t.py
Traceback (most recent call last):
  File "t.py", line 13, in <module>
    con.execute("insert into track (artist_id) values (1)")
sqlite3.IntegrityError: foreign key constraint failed
msg144222 - (view) Author: Mark Bucciarelli (Mark.Bucciarelli) Date: 2011-09-17 23:17
huh.  is it already on in your sqlite  install?

$ sqlite3
SQLite version 3.7.7.1 2011-06-28 17:39:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma foreign_keys;
0
sqlite> 

is what i get (it's off).
msg144225 - (view) Author: (poq) Date: 2011-09-18 02:57
Nope.

$ sqlite3
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma foreign_keys;
0
sqlite>

$ python
Python 2.7.1+ (r271:86832, Apr 11 2011, 18:13:53) 
[GCC 4.5.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> c = sqlite3.connect(':memory:')
>>> list(c.execute('pragma foreign_keys'))
[(0,)]
>>> list(c.execute('pragma foreign_keys = on'))
[]
>>> list(c.execute('pragma foreign_keys'))
[(1,)]
msg145604 - (view) Author: Mark Bucciarelli (Mark.Bucciarelli) Date: 2011-10-15 18:52
Something strange is going on.

I just built pysql 2.6.3 from source, and now my unit test gives the expected "IntegrityError: foreign key constraint failed" message.

poq, what do you get when you run this script:

import sqlite3
print "sqlite3.version_info =", sqlite3.version_info
print "sqlite3.sqlite_version_info =", sqlite3.sqlite_version_info
from pysqlite2 import dbapi2 as sqlite3
print "pysqlite2.version_info =", sqlite3.version_info
print "pysqlite2.sqlite_version_info =", sqlite3.sqlite_version_info

I get the following:

sqlite3.version_info = (2, 6, 0)
sqlite3.sqlite_version_info = (3, 6, 12)
pysqlite2.version_info = (2, 6, 3)
pysqlite2.sqlite_version_info = (3, 7, 8)
msg145624 - (view) Author: (poq) Date: 2011-10-16 15:57
sqlite3.version_info = (2, 6, 0)
sqlite3.sqlite_version_info = (3, 7, 4)
pysqlite2.version_info = (2, 6, 0)
pysqlite2.sqlite_version_info = (3, 7, 4)
msg145625 - (view) Author: Mark Bucciarelli (Mark.Bucciarelli) Date: 2011-10-16 16:30
BAD : sqlite3.sqlite_version_info = (3, 6, 12)
GOOD: sqlite3.sqlite_version_info = (3, 7, 4)
GOOD: sqlite3.sqlite_version_info = (3, 7, 8)

I guess this is the cause of the different behavior.

sqlite_version_info is the version of the underlying sqlite3 database library.

So, the sqlite3 is loading an older dynamic lib on my OSX.
msg145760 - (view) Author: Ned Deily (ned.deily) * (Python committer) Date: 2011-10-17 21:31
Mark, you don't say what version of OS X or which Python you are using but, if you are using an Apple-supplied Python on 10.6 or 10.7 or if you are using python.org 64-bit/32-bit installers for 2.7 or 3.2, the Python standard library sqlite3 modules dynamically links to the system-supplied /usr/lib/libsqlite3.dylib.  On 10.6.8 Snow Leopard that is 3.6.12.  On Lion (10.7.2) that has been updated by Apple to 3.7.5.  (BTW, the most recent 32-bit-only python.org installers for 3.2.x and 2.7.x statically link a version of 3.7.4 sqlite3 in order to work on older versions of OS X which have much older or even no system versions of sqlite3.)

Can this issue be closed?
History
Date User Action Args
2011-10-17 22:48:01ned.deilysetstatus: open -> closed
stage: resolved
2011-10-17 22:26:33Mark.Bucciarellisetresolution: not a bug
2011-10-17 21:31:22ned.deilysetnosy: + ned.deily
messages: + msg145760
2011-10-16 16:30:29Mark.Bucciarellisetmessages: + msg145625
2011-10-16 15:57:37poqsetmessages: + msg145624
2011-10-15 18:52:26Mark.Bucciarellisetmessages: + msg145604
2011-09-18 02:57:01poqsetmessages: + msg144225
2011-09-17 23:17:35Mark.Bucciarellisetmessages: + msg144222
2011-09-17 21:26:43poqsetnosy: + poq
messages: + msg144220
2011-09-16 23:08:09ned.deilysetnosy: + ghaering
2011-09-16 22:54:18Mark.Bucciarellicreate