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

You're looking for format in MySQL:

select format(id, '999') from xyz

To answer your all-in-one question: There's no cross-database way to do this. Every RDBMS is very different, and there's no real standardization past the fairly simple queries. This is especially true with string manipulations.

Answer 2

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.