RIGHT() function in sqlalchemy

Question

Can I implement the following in SQLAlchemy,

SELECT * 
FROM TABLE 
WHERE RIGHT(COLUMN_CODE, 2) = 'AX'

here RIGHT( ) returns the right part of a character string with the specified number of characters.

Is there a SQLAlchemy implementation of the RIGHT function?

Answer 1

You'd be better off using the .endswith() method instead:

select([tabledef]).where(tabledef.c.column_code.endswith('AX'))

or, when filtering with a mapper config and a session:

session.query(mappedobject).filter(mappedobject.column_code.endswith('AX'))

The column_code.endswith() method will be translated to whatever SQL is best for your particular engine, matching column values that end with AX.

You can always use the function generator to create arbitrary SQL functions if you have to use the RIGHT() sql function directly:

from sqlalchemy.sql.expression import func

select([tabledef]).where(func.right(tabledef.c.column_code, 2) == 'AX')

and the func.right() call will be translated to RIGHT(column_code, 2) by the SQL generation layer.

Answer 2

The documentation does not make it clear, but you can write any function using func.funcname sytle. funcname does not have to be defined natively by SQLAlchemy module. SQLAlchemy knows about common functions like min, max etc. and if there is dialect to dialect variation amongst those functions, SQLAlchemy takes care of that for you.

But the functions that SQLAlchemy does not know about are passed as is. So you can create your query that generates a SQL statement with the required RIGHT function like so

>>> from sqlalchemy import func
>>> select([table]).where(func.RIGHT(users.c.column_code, 2)='AX')