sqlite3 and pysqlite3 incompatibility

[rt121212121]

I develop against sqlite3 on Windows and my unit tests pass, but I have noticed that if I go to CI and look at the unit tests run on Linux they are failing when being run against pysqlite3.

The connection execute method fails with a different exception on each as reported by pytest:

    #     Windows: "sqlite3.IntegrityError: FOREIGN KEY constraint failed"
    #     Linux:   "pysqlite3.dbapi2.OperationalError: FOREIGN KEY constraint failed"

Windows has the official x64 3.10.0 install and the corresponding sqlite3 version distributed in this:

Python 3.10.0 (tags/v3.10.0:b494f59, Oct  4 2021, 19:00:18) [MSC v.1929 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version, sqlite3.sqlite_version
('2.6.0', '3.35.5')
>>>

Linux is on Azure Devops and has:

 Python 3.10.2 x64

Collecting pysqlite3-binary==0.4.7
  Downloading pysqlite3_binary-0.4.7-cp310-cp310-manylinux_2_24_x86_64.whl (3.6 MB)

[coleifer]

Check the output of

conn.execute('pragma foreign_keys')

Are they different?

[rt121212121]

Windows:

(1,)

Linux:

(1,)

[coleifer]

That's really odd, I agree. Are you able to output the value of sqlite_version on your CI? I'll be away from the computer for a while but we'll get it sorted out.

[rt121212121]

3.38.0

[rt121212121]

That should be what comes in the pysqlite3 wheel listed above, in theory.

[coleifer]

I cannot replicate this. I created a new virtualenv using Python 3.10:

$ pip install pysqlite3-binary
Collecting pysqlite3-binary
  Using cached pysqlite3_binary-0.4.7-cp310-cp310-manylinux_2_24_x86_64.whl (3.6 MB)                                    
Installing collected packages: pysqlite3-binary
Successfully installed pysqlite3-binary-0.4.7

Then here's a script:

from pysqlite3 import dbapi2 as sqlite3
print(sqlite3)
print(sqlite3.sqlite_version)

conn = sqlite3.connect(':memory:')
conn.execute('pragma foreign_keys=1')

conn.execute('create table users (id integer not null primary key, username text)')
conn.execute('create table tweets (id integer not null primary key, '
             'user_id integer not null references users(id), '
             'message text)')

conn.execute('insert into users (username) values (?)', ('u1',))
conn.execute('insert into tweets (user_id, message) values (?, ?)', (1, 't1'))
conn.execute('insert into tweets (user_id, message) values (?, ?)', (2, 'tx'))

The last line should raise the exception. Output:

<module 'pysqlite3.dbapi2' from '.../testenv/lib/python3.10/site-packages/pysqlite3/dbapi2.py'>
3.38.0
Traceback (most recent call last):
  File ".../testenv/x.py", line 16, in <module>
    conn.execute('insert into tweets (user_id, message) values (?, ?)', (2, 'tx'))
pysqlite3.dbapi2.IntegrityError: FOREIGN KEY constraint failed

I would venture a guess that something on your CI setup differs from what you've reported.

[rt121212121]

Sorry, I should have done more investigation and given you a reproduction case.

Try:

from pysqlite3 import dbapi2 as sqlite3
print(sqlite3)
print(sqlite3.sqlite_version)

conn = sqlite3.connect(':memory:')
conn.execute('pragma foreign_keys=1')

conn.execute('create table users (id integer not null primary key, username text)')
conn.execute('create table tweets (id integer not null primary key, '
             'user_id integer not null references users(id), '
             'message text)')

conn.execute('insert into users (username) values (?)', ('u1',))
conn.execute('insert into tweets (user_id, message) values (?, ?)', (1, 't1'))
conn.execute('insert into tweets (user_id, message) values (?, ?) returning id, user_id, message', (2, 'tx'))

This alters the error from IntegrityError to OperationalError on Linux, but not Windows.

[coleifer]

Are you able to print out the value of exc.sqlite_errorcode?

try:
    conn.execute('insert into tweets (user_id, message) values (?, ?) returning id, user_id, message', (2, 'tx'))
except Exception as exc:
    print('got %s - %s' % (exc, exc.sqlite_errorcode))

On my linux this is returning "1" (generic SQLITE_ERROR).

Update: even attempting to use the sqlite3_extended_errcode still returns 1 in this case.

One more request is to check the behavior of the stdlib sqlite3 (provided it is new enough) on your linux installs and see if it matches what you see on windows.

[rt121212121]

The value of exc.sqlite_errorcode is 1.

The stdlib sqlite3 version is too old and does not support RETURNING, I have no idea how to use a newer one to see.

[coleifer]

Honestly I'm a bit at a loss. If you're able to set up a Linux box that replicates what you see on windows I'd be happy to look into it, but my python 3.10 sqlite3 behaves the exact same as pysqlite3 so I can't replicate.

If the error code on windows is also 1 though I'm not sure what could be happening, as that's the value that determines the error class.

[coleifer]

I think I may have gotten somewhere, finally. I think this is possibly a regression in sqlite3. I grabbed sqlite3 3.35.5 and compiled pysqlite3 on a spare linux box I had, and am getting the integrity error instead of the operational error. Additionally, I had to modify the script a bit, to actually step through the RETURNING result set, which may be part of the reason for this regression.

At any rate, here are the changes to the script:

conn.execute('insert into users (username) values (?)', ('u1',))
conn.execute('insert into tweets (user_id, message) values (?, ?)', (1, 't1'))
curs = conn.execute('insert into tweets (user_id, message) values (?, ?) returning id, user_id, message', (2, 'tx'))
curs.fetchall()

And here is the output:

<module 'pysqlite3.dbapi2' from '.../pysqlite3/build/lib.linux-x86_64-3.6/pysqlite3/dbapi2.py'>
3.35.5
Traceback (most recent call last):
  File "x.py", line 16, in <module>
    curs.fetchall()
pysqlite3.dbapi2.IntegrityError: FOREIGN KEY constraint failed

Note that the error occurs when we run fetchall() as opposed to simply executing it.

If you want to follow this up on the sqlite mailing list, you're welcome to, but as far as I'm concerned pysqlite3 is not the cause of the issue here.

[rt121212121]

Can you describe the information I need to give them? What the sqlite3 API difference is between the integrity error and the operational error, in terms of what pysqlite uses/expects/sees?

[coleifer]

The error code returned by SQLite 3.35 does not occur until one starts stepping through the results. And furthermore seems the code is SQLITE_CONSTRAINT. In 3.38 it looks like the error occurs during query compilation (perhaps) and is a generic error

[rt121212121]

We'll see how we go. Posted here:

https://www.sqlite.org/forum/forumpost/e6be6e82c8