SQLite3 syntax error on tested sql script

I’m using python 3.6.4 and sqlite3 2.6.0 to query the nearest consecutive dates in my table in a sqlite 3.27.2 file.

I’ve tried to get the actual sql string with vscode debugger and test it with DB Browser for SQLite. It works as I expect.

Here’s the code:

sql =  'WITH 
            dates(cast_date) AS (
                SELECT DISTINCT play_date
                FROM TimeTable
            ),
            groups AS (
                SELECT
                    date(cast_date, '-'||(ROW_NUMBER() OVER (ORDER BY cast_date))||' days') AS grp,
                    cast_date
                FROM dates
            )
        SELECT
            MIN(cast_date) AS date_start,
            MAX(cast_date) AS date_end
        FROM groups GROUP BY grp ORDER BY 2 DESC LIMIT 1'

cursor = conn.cursor() 
result = []
try:
    cursor.execute(sql)
    result = cursor.fetchone()
except sqlite3.OperationalError:
    FileLogger.exception('Exception at '+__file__+' '+__name__)

An exception occurs:

cursor.execute(sql)
sqlite3.OperationalError: near "OVER": syntax error

Answer

Window functions support was first added to SQLite with release version 3.25.0 (2018-09-15), according to official documentation.

When using Python, you are using Python SQLite3 client library (which is distributed with Python) instead of your system SQLite3 installation. For Python 2.7, the version is 3.11.0, which is below your required version.

You may try using a newer SQLite3 client library, as suggested by these answers.