SQLite3 syntax error on tested sql script

Question

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

What version of SQLite are you using? Not all versions support window functions. — Gordon Linoff, Jul 29 '19 at 11:49
In general if you have a string over multiple lines you might want to use triple quatation marks (`'''` or `"""`). — Klaus D., Jul 29 '19 at 11:53
Not related but consider using `'''` (multiline strings) instead of backslashes. It can make your life a bit easier. — DeepSpace, Jul 29 '19 at 11:54
I'm using sqlite3 of 2.6.0 and sqlite file of 3.27.2 now. And sadly `'''` doesn't solve this. — Ryan Asperger, Jul 29 '19 at 11:59

jjmontes · Accepted Answer · 2019-08-20T18:00:58.583

1

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.

edited Aug 20 '19 at 18:00

answered Jul 29 '19 at 12:10

jjmontes

24,679
4
39
51

1

My code works after I upgrade sqlite3 version. Thanks! – Ryan Asperger Jul 29 '19 at 13:19

SQLite3 syntax error on tested sql script

1 Answers1