Python flask-sqlalchemy - windows functions query error?

Question

I'm trying to use a windows function so that I can ultimately pull up just the last result frrom each eternal_id, and am getting stuck just trying to get a windows function to work. I see the error whether I connect to the engine and select from it, or initiate a session and query from it.

Here's what I tried (the "intiate a session and query from it" method):

engine = create_engine('sqlite:///../../app.db')
sessionfactory = sessionmaker(bind=engine)
s = sessionfactory()

myquery = s.query(models.case.id, 
                  models.case.eternal_id,
                  func.count().over(
                      partition_by=models.case.eternal_id).label('cnt'))
print myquery.all()

Here's the error I get:

OperationalError: (sqlite3.OperationalError) near "(": syntax error [SQL:    
u'SELECT "case".id AS case_id, "case".eternal_id AS case_eternal_id, 
count(*) OVER (PARTITION BY "case".eternal_id) AS cnt \nFROM "case"']

If I remove func.count().over(partition_by=models.case.eternal_id).label('cnt') from the query everything runs perfectly. Anyone have any idea why I'm getting an error??

Here's the model if it helps:

from app import db
class case(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    eternal_id = db.Column(db.Integer, db.ForeignKey('case_eternal.id'))
    eternal = db.relationship('case_eternal', back_populates='case_sids')
    def __repr__(self):
        return '<Case %r>' % (self.id)

class case_eternal(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    case_sids = db.relationship('case', back_populates='eternal')

CL. · Accepted Answer · 2016-01-08 20:11:50Z

1

SQLite does not have window functions.

answered Jan 8, 2016 at 20:11

CL.

182k18 gold badges241 silver badges282 bronze badges

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

Comments

eRunner · Accepted Answer · 2019-12-19 09:11:17Z

0

UPDATE: Window Functions are now supported

For those landing here, things have changed since this question was asked and answered. Support for window functions was added to SQLite on version 3.25.0 (2018-09-15).

Documentation: https://www.sqlite.org/windowfunctions.html
Release history: https://www.sqlite.org/changes.html#version_3_25_0

answered Dec 19, 2019 at 9:11

eRunner

2,0501 gold badge13 silver badges11 bronze badges

Collectives™ on Stack Overflow

Python flask-sqlalchemy - windows functions query error?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related