3

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?

Eric
  • 92,005
  • 12
  • 114
  • 115
asb
  • 910
  • 3
  • 10
  • 19

3 Answers3

3

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.

zzzeek
  • 72,307
  • 23
  • 193
  • 185
2

Use the cast function, for example

cast(numberfield as char)
Greg Payne
  • 21
  • 1
0

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.

Eric
  • 92,005
  • 12
  • 114
  • 115
  • I agree .. to u .. i read some where that using sqlalchemy we can come over the difference. I tried but failed :( – asb Aug 04 '09 at 12:28
  • @asb: You'll have to check which RDBMS you're using before making the call, sadly. This would either be in the function itself, or just use different functions with different engines. – Eric Aug 04 '09 at 12:47