[New-bugs-announce] [issue26043] ON DELETE CASCADE does not work when using sqlite3 library

[Vitaminus Maximus]

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 <report at bugs.python.org>
<http://bugs.python.org/issue26043>
_______________________________________