sqlite3 error while adding a table using python, but not with DB Browser for sqlite

Question

There seem to be similar but not quite the same issues discussed in some forums including StackOverflow, so they did not help with my problem below.

I can add a series of tables to a sqlite3 database without errors using either DB Browser for Sqlite or Python except in one case, in which case DB Browser works fine but python fails. My goal is to create a python program that sequentially runs sqlite3 scripts automatically.

The place where python hangs up is in the following script (One of several sequential "create table" scripts in the same file) near "AS" according to the error message:

DROP TABLE if EXISTS SheetGoods;

CREATE TABLE SheetGoods (
    SheetId INTEGER PRIMARY KEY,
    ItemNo INTEGER, 
    ItemCategory TEXT, 
    ItemName TEXT, 
    SheetName TEXT, 
    Width REAL, 
    Height REAL, 
    Depth REAL, 
    Thickness REAL,
    Area REAL GENERATED ALWAYS AS ((Width * Height)/144) STORED, 
    /* SYNTAX for line above similar to: column INT GENERATED ALWAYS AS (a*abs(b)) VIRTUAL or STORED 
    Consider putting this formula into a Python script and update table with python output*/
    for_Pricing INTEGER,
    for_Order_List INTEGER,
    Comments TEXT,
    FOREIGN KEY(ItemNo) REFERENCES Items(ItemId)
 );

The Python script that calls the sqlite3 query above works fine for all of my tables except for this one instance. If I comment out the end of the generated column after Column: "Area REAL" (i.e.: the following is left out - "GENERATED ALWAYS AS ((Width * Height)/144) STORED )" then python does not hang. I haven't been able to find a way to sort out the issue that Python has with this syntax.

The python code is:

# runNewProjectScript.py
import sqlite3
newDbName = "Johnston.db"
try:   
   sqliteConnection = sqlite3.connect(newDbName)
   cursor = sqliteConnection.cursor()
   print("Successfully Connected to SQLite")
   # the following was advice from sqlite3 website. But it seems there is issues.
   cursor.execute('pragma foreign_keys=ON')
   with open('C:\myPy38\sqlite\sqLiteCabTest\cabPricingScripts\cabPricing.sql', 'r') as sqlite_file:
       sql_script = sqlite_file.read()
   cursor.executescript(sql_script)
   print("SQLite script executed successfully")
   cursor.close()
except sqlite3.Error as error:
   print("Error while executing sqlite script", error)
finally:
   if (sqliteConnection):
       sqliteConnection.close()
       print("sqlite connection is closed")

The error message is as follows:

$ python runNewProjectScript.py
Successfully Connected to SQLite
Error while executing sqlite script near "AS": syntax error
sqlite connection is closed

score 1 · Answer 1 · answered Aug 31 '20 at 22:27

1

The SQLite site says that:

Generated column support was added with SQLite version 3.31.0 (2020-01-22).

I could not find what SQLite version is used by the sqlite3 Python module, but as the github repository for pysqlite has not been updated since 2016, I would not be surprised that it uses an older version.

Said differently, when you use Python, you actually use a SQLite engine that does not know about generated columns. I cannot imagine any workaround besides not using that feature.

answered Aug 31 '20 at 22:27

Serge Ballesta

143,923
11
122
252

Thanks. I now realize that the version of sqlite and python that are being used totally depends on which tool is being used. DB Browser for sqlite v. 3.12.0 works with at least sqlite 3.25.0, so reasonably up to date (latest version on sqlite download page is is 3.33.0). The matter of which version of sqlite python uses is quite complicated - see this post: https://stackoverflow.com/questions/1545479/force-python-to-forego-native-sqlite3-and-use-the-installed-latest-sqlite3-ver/1546162#1546162. I'm going to have to read it again, slowly. – MurtonenT Sep 01 '20 at 05:18
And the post I referred to is quite old. – MurtonenT Sep 01 '20 at 05:20
1

`print(sqlite3.sqlite_version)` dsplays the sqlite version, per https://docs.python.org/3/library/sqlite3.html#sqlite3.sqlite_version – snakecharmerb Sep 01 '20 at 06:52

sqlite3 error while adding a table using python, but not with DB Browser for sqlite

1 Answers1