How to use the new SQLite STRICT keyword with Python 3?

Python Help
1

SQLite 3.37 added support for strict type checking via the new STRICT keyword.
However, the SQLite3 module bundled with Python 3.7.9 cannot work with it (sqlite3.DatabaseError: malformed database schema (tableName) - near “STRICT”: syntax error).
What can I do about this? Do I need to build Python with the latest SQLite myself?
Also, which (future) Python version will start supporting this new feature?

2

SQLite 3.37 added support for strict type checking via the new STRICT
keyword.
However, the SQLite3 module bundled with Python 3.7.9 cannot work with it (sqlite3.DatabaseError: malformed database schema (tableName) - near “STRICT”: syntax error).

Can you show a small piece of code demonstrating this error please?

What can I do about this? Do I need to build Python with the latest SQLite myself?

Maybe.

Here’s a Mac homebrew Python 3.9.9:

Python 3.9.9 (main, Nov 21 2021, 03:22:47)
[Clang 12.0.0 (clang-1200.0.32.29)] on darwin
Type "help", "copyright", "credits" or "license" for more 
information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.37.0'
>>>

which suggests it has been built against sqlite3 3.37.0. Which may
simply the be sqlite3 available when homebrew built it here on my
machine (or when a homebrew binary install image was built).

What does your Python report for the above?

Also, which (future) Python version will start supporting this new
feature?

When the 3.37 come out? And Python 3.7.9? It may be as simple as 3.37
not being available when the Python 3.7.9 prebuilt bundle was
constructed for the download page.

So it may not be “Python supporting this keyword” but “SQLite3 3.37
being present at build time”. Try building Python yourself with that
revision of SQLite present, or fetching a newer Python.

Cheers,
Cameron Simpson cs@cskk.id.au

1 Like
3

Can you show a small piece of code demonstrating this error please?

First create a sqlite3 database with a STRICT table:

sqlite3 test.db
sqlite>create table test (testcol int) STRICT;

Then in Python:

import sqlite3
con = sqlite3.connect('./test.db')
cur = con.cursor()
cur.execute('select * from test')

What does your Python report for the above?

Python 3.7.9 (tags/v3.7.9:13c94747c7, Aug 17 2020, 18:58:18) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.31.1'

Try building Python yourself with that
revision of SQLite present, or fetching a newer Python.

I will try building Python myself.
BTW, is there a Python version that support SQLite 3.37 out of the box?

4

The python.org installers for Windows and macOS will ship with SQLite 3.37.2 for the upcoming releases of Python 3.11, 3.10, and possibly also 3.9.

For Python 3.8 and 3.7, use Homebrew if you’re on a Mac, or build from source :slight_smile:

1 Like
5

Can you show a small piece of code demonstrating this error please?

First create a sqlite3 database with a STRICT table:

sqlite3 test.db
>create table test (testcol int) STRICT;

This works with a 3.37 sqlite:

CSS[~/hg/css(hg:default)]fleet2*> /usr/local/Cellar/sqlite/3.37.2/bin/sqlite3 test.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
> create table test (testcol int) STRICT;
>

By contrast, the sqlite3 shipped with my local MacOS is 3.28.0:

CSS[~/hg/css(hg:default)]fleet2*> sqlite3 test2.db
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
> create table test (testcol int) STRICT;
Error: near "STRICT": syntax error
>

import sqlite3
con = sqlite3.connect(‘./test.db’)
cur = con.cursor()
cur.execute(‘select * from test’)

This also works here.

I will try building Python myself.
BTW, is there a Python version that support SQLite 3.37 out of the box?

I suspect it is an artifact of the sqlite used to build the Python, not
Python itself. The error you are getting comes from the sqlite3 library,
not the Python module itself. Build against a 3.37 sqlite and you should
be good.

Cheers,
Cameron Simpson cs@cskk.id.au

1 Like
6

FYI, the Windows installer has now been updated, so those upcoming 3.9, 3.10, and 3.11 releases will ship with SQLite 3.37.2.

1 Like