New submission from Vitaminus Maximus:
Let me provide a completely reproducible code, which demonstrates the issue:
>>> import sqlite3
>>> cnx = sqlite3.connect("mytest.db")
>>> cnx.isolation_level = None
>>> cursor = cnx.cursor()
>>> cnx.execute("BEGIN")
<sqlite3.Cursor object at 0x7f0ab0923490>
>>> cursor.execute("CREATE TABLE test_table (id integer)")
<sqlite3.Cursor object at 0x7f0ab0923420>
>>> cursor.execute("CREATE UNIQUE INDEX id_primary ON test_table(id)")
<sqlite3.Cursor object at 0x7f0ab0923420>
>>> cursor.execute("INSERT INTO test_table (id) VALUES (1),(2),(3)")
<sqlite3.Cursor object at 0x7f0ab0923420>
>>> cursor.execute("CREATE TABLE test_table_2(id_fk integer, txt text,
FOREIGN KEY (id_fk) REFERENCES test_table(id) ON DELETE CASCADE)")
<sqlite3.Cursor object at 0x7f0ab0923420>
>>> cursor.execute("INSERT INTO test_table_2 (id_fk, txt) VALUES
(1,\"one\"),(2,\"two\"),(3,\"three\")")
<sqlite3.Cursor object at 0x7f0ab0923420>
>>> res = cursor.execute("SELECT * FROM test_table_2")
>>> res
<sqlite3.Cursor object at 0x7f0ab0923420>
>>> for r in res:
... print(r)
...
(1, 'one')
(2, 'two')
(3, 'three')
>>> cursor.execute("PRAGMA foreign_keys = ON")
<sqlite3.Cursor object at 0x7f0ab0923420>
>>> cursor.execute("DELETE FROM test_table WHERE id = 1")
<sqlite3.Cursor object at 0x7f0ab0923420>
>>> res = cursor.execute("SELECT * FROM test_table_2")
>>> for r in res:
... print(r)
...
(1, 'one')
(2, 'two')
(3, 'three')
As you can see, even though I explicitly set isolation_level, start transaction
and specify PRAGMA foreign_keys = ON, it does not work. The expected behaviour
is that when I run the last SELECT command, it should return only two rows
(because a "parent" raw with id = 1 was deleted). By the way, if I run these
commands in pure sqlite prompt, then I get what I expect to see.
----------
components: Library (Lib)
messages: 257709
nosy: Vitaminus Maximus
priority: normal
severity: normal
status: open
title: ON DELETE CASCADE does not work when using sqlite3 library
type: behavior
versions: Python 3.4
_______________________________________
Python tracker <rep...@bugs.python.org>
<http://bugs.python.org/issue26043>
_______________________________________
_______________________________________________
Python-bugs-list mailing list
Unsubscribe:
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
