Find place number in queue, peewee producing an illegal sqlite3 query

Question

I'm trying to find the place number of a ticket in a queue. The query fails at sqlite3, and the error given is quite peculiar: sqlite3.OperationalError: near "(": syntax error. I don't seem to understand how the column references are used.

I've tried separating into more subqueries, looked at countless examples of aggregate use, window functions, etc. in peewee, sqlite and MySQL. What I haven't found is an example of fn.ROW_NUMBER in peewee. I checked the query on multiple websites, and they would parrot the same error.

Query generated by peewee:

SELECT ROW_NUMBER() OVER (ORDER BY "t1"."ordinal") AS ordinal_index 
FROM "dbticket" AS t1 
WHERE (((("t1"."called_at" IS ?) AND ("t1"."abandoned_at" IS ?)) AND ("t1"."removed_at" IS ?)) AND ("ticket_id" = ?))

Parameters:

[None, None, None, 'red-shibe'

The code responsible for this hell:

from pathlib import Path
import os
THIS_FOLDER = Path(os.path.dirname(os.path.abspath(__file__)))
db_file = THIS_FOLDER / 'queue.db'
sqlite_db = SqliteDatabase(str(db_file), pragmas=[('journal_mode', 'wal')])



class DBTicket(BaseModel):
    ordinal = PrimaryKeyAutoIncrementField()
    ticket_id = TextField(unique=True)
    queue_name = TextField()

    initiated_at = DateTimeField(default=datetime.utcnow)
    called_at = DateTimeField(null=True)
    abandoned_at = DateTimeField(null=True)
    removed_at = DateTimeField(null=True)

    def __str__(self):
        return f'{self.ordinal}:{self.ticket_id}'

in_queue_predicate = (DBTicket.called_at.is_null() &
                      DBTicket.abandoned_at.is_null() &
                      DBTicket.removed_at.is_null())

def ticket_place_number(ticket_id):
    queue = DBTicket.select() \
                    .where(in_queue_predicate)
    place_number = queue.select(fn.ROW_NUMBER().over(order_by=[DBTicket.ordinal]).alias('ordinal_index')) \
                        .where(queue.c.ticket_id == ticket_id) \
                        .scalar()
    return place_number

score 1 · Accepted Answer · answered Aug 10 '19 at 15:31

1

I don't see any immediate issues, other than that you might check your sqlite version. Window functions are a relatively recent addition and you need 3.25.0 or newer.

import sqlite3
sqlite3.sqlite_version

answered Aug 10 '19 at 15:31

coleifer

24,887
6
60
75

My .exe says 3.29.0, whereas the Python console (in Pycharm) says 3.21.0 :\ – Lupilum Aug 10 '19 at 15:39
This is most likely it, I'll have to investigate further. Thanks. – Lupilum Aug 10 '19 at 15:39
It seems there aren't good solutions. I would have to update Python to the latest version 3.7.4 to get sqlite 3.28.0, or use hacks that seem quite inefficient: [exh. 1](https://stackoverflow.com/questions/16847574/how-to-use-row-number-in-sqlite) [exh .2](https://stackoverflow.com/questions/14023292/how-to-get-rownum-like-column-in-sqlite-iphone/19199219#19199219) – Lupilum Aug 10 '19 at 16:07
Ended up upgrading Python to 3.7.4. – Lupilum Aug 10 '19 at 20:51
That isn't necessary. You can always compile sqlite and install it system-wide, or build a statically-linked sqlite3 driver: http://charlesleifer.com/blog/compiling-sqlite-for-use-with-python-applications/ – coleifer Aug 12 '19 at 23:51
I explored that possibility. I don't want to force my collaborators to do the same. Even needing to use the latest version of Python is hard. In the end, to help everything go smoothly, I did the indexOf operation in Python. The amount of data is small enough for this, in exceptional circumstances. – Lupilum Aug 14 '19 at 13:42

Find place number in queue, peewee producing an illegal sqlite3 query

1 Answers1