[Python] query from python for rolling avg from sqlite db giving errors

I am trying to calculate rolling avg in sqlite database by sending the query from my python code as below.

import sqlite3
sql_db_path = "/home/Documents/src"
sql_db_name = "temp.db"
try:
sqlConn = sqlite3.connect(f'{sql_db_path}/{sql_db_name}')
sqlCursor = sqlConn.cursor()
print(f'Connected Successfully to SQL db :: [{sql_db_path}/{sql_db_name}]')
except:
print(f'Could not connect to SQL db :: [{sql_db_path}/{sql_db_name}], Exiting........... ')
exit(-1)
r = sqlCursor.execute("""select DelivQTY, sqlDate, AVG(DelivQTY) OVER (ORDER BY sqlDate DESC ROWS BETWEEN 1 FOLLOWING AND 5 FOLLOWING) sma5_delivQty FROM (SELECT * FROM bcRawData WHERE symbol = 'INFY')""")


This query runs perfectly in the interactive sql session (sqlitebrowser) But I am getting the following error when I run the same thru python.

File "", line 1, in r=sqlCursor.execute("""select DelivQTY, sqlDate, AVG(DelivQTY) OVER (ORDER BY sqlDate DESC ROWS BETWEEN 1 FOLLOWING AND 5 FOLLOWING) sma5_delivQty FROM (SELECT * FROM bcRawData WHERE symbol = 'INFY')""") sqlite3.OperationalError: near "(": syntax error

Looks like it is not able to understand something about the OVER keyword. I have tried all I could including web search, but with no luck. Appreciate if anyone can help me on this one.

Python version: 3.9 sqlite3.version: '2.6.0' sqlite3.sqlite_version_info: 3.22.0 SQLite version 3.35.4 2021-04-02 15:20:15

Thanks.

Continue reading...