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')
    


Answer: SQLite does not have window functions.

