Jonas Jonas -3 years ago 359
Python Question

sqlAlchemy Select with like and max

I' trying to select the highest value from column 'id' in table 'tablename' which is beginning with 'v3' and Ends with 't'.

This works finde with the following SQL statement:

SELECT max(_id_)
FROM _db.tablename_
WHERE _id_ LIKE 'v3%'
AND _id_ NOT LIKE '%T'


But it seems that I have some bugs in my sqlAlchemy snippet and couldn't find the mistake.

query = session.query(_CLASSNAME_._ID_).filter(
func.max(_CLASSNAME_._ID_.like('v4%'), not_(_CLASSNAME_._ID_.like('%T'))))

for _res in query.all():
print(_res)

Answer Source

First, to calculate the max of a column you need to query like this:

session.query(func.max(Table.column))

To use Boolean operations in filtering, you need to import equivalent functions from sqlalchemy:

from sqlalchemy import and_
from sqlalchemy import not_

Your query could be something like this:

query = (session.query(func.max(_CLASSNAME_._ID_))
         .filter(and_(_CLASSNAME_._ID_.like('v4%'),
                      not_(_CLASSNAME_._ID_.like('%T'))))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download