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
– Gordon Linoff, Commented Jul 29, 2019 at 11:49
In general if you have a string over multiple lines you might want to use triple quatation marks (''' or """). — Klaus D.
– Klaus D., Commented Jul 29, 2019 at 11:53
Not related but consider using ''' (multiline strings) instead of backslashes. It can make your life a bit easier. — DeepSpace
– DeepSpace, Commented Jul 29, 2019 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
– Ryan Asperger, Commented Jul 29, 2019 at 11:59

jjmontes · Accepted Answer · 2019-08-20 18:00:58Z

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, 2019 at 18:00

answered Jul 29, 2019 at 12:10

jjmontes

27.2k5 gold badges47 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ryan Asperger Over a year ago

My code works after I upgrade sqlite3 version. Thanks!

Collectives™ on Stack Overflow

SQLite3 syntax error on tested sql script

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related