Database-Independent to_char Function in SQLAlchemy

Question

I am using SQLAlchemy to make database-independent querys.

I am facing one issue with to_char function.

Consider the simple query like:

select to_char(id,'999') from xyz 

It's working on Postgres but MySQL doesn't support it.

How can I make this query database-independent using SQLAlchemy?

Answer 1

use the CAST function for simple type conversions. it is SQL standard and SQLAlchemy supports it directly via the cast() function: http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/expressions.html?highlight=cast#sqlalchemy.sql.expression.cast .

for date values, SQLA has an extract() function that produces the SQL EXTRACT function - a translation layer translates common fieldnames like "month", "day", "minute", etc. into the appropriate keyword for the backend in use.