2

I am using flask-sqlalchemy to perform a ranking query on a PlayerKillData table, which has 4 columns: ref_id (Primary Key), kill, all_kill and lvl. Specifically, what I want to achieve is to Rank by kill for player on the same lvl and add a Rank column

#Columns define in Create Table SQL statement and resolved by reflection at runtime
class PlayerKillData(db.Model):
    __tablename__ = "player_kill_data"

The following python snippet is that I have tried but no matter how I have tried, it always return sqlite3.OperationalError:

subquery = excDB.db.session.query(
PlayerKillData,
excDB.db.func.rank().over(
    order_by = PlayerKillData.kill.desc(),
    partition_by = PlayerKillData.lvl).label('RANK')
)
subquery.all()

The SQL statement printed by the error message is:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "(": syntax error
[SQL: SELECT player_kill_data.ref_id AS player_kill_data_ref_id, player_kill_data.kill AS player_kill_data_kill, player_kill_data.all_kill AS player_kill_data_all_kill, player_kill_data.lvl AS player_kill_data_lvl, rank() OVER (PARTITION BY player_kill_data.lvl ORDER BY player_kill_data.kill DESC) AS "RANK"
FROM player_kill_data]

I have also tried running the SQL statement directly using Db Browser for SQLite and it returned the table successfully without any error.

I am really puzzled by this and would like any help I can get to solve this.

SuperShoot
  • 9,880
  • 2
  • 38
  • 55
ksming
  • 1,422
  • 1
  • 16
  • 26

1 Answers1

1

Your version of SQLite might be smaller than 3.25.0.

As mentionned here and confirmed in the Release History of SQLite, SQLite supports window function (RANK() OVER ...) since version 3.25.0, which was released on 2018-09-15.

To check your version of SQLite in python:

import sqlite3
print(sqlite3.sqlite_version)
# returns '3.22.0' for me

If many people work on the same codebase and you'd like to have a more explicit error message for other developers, you can assert:

assert sqlite3.sqlite_version_info >= (3,25,0), """sqlite3 version must be >= 3.25.0 because that's when it started to support window functions"""
user13976950
  • 11
  • 1