CODEWITHSUNDEEP
mysqlsqlalchemyto-char
asb
asb

Reputation: 928

Database-Independent to_char Function in SQLAlchemy

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?

Upvotes: 3

Views: 4189

Answers (3)

Greg Payne
Greg Payne

Reputation: 21

Use the cast function, for example

cast(numberfield as char)

Upvotes: 2

zzzeek
zzzeek

Reputation: 75317

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.

Upvotes: 3

Eric
Eric

Reputation: 95203

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.

Upvotes: 0

Related Questions