I'm trying to use sqlalchemy core select rows from a mysql table using IFNULL.
Given a table like so:
id int1 string1 other
1 7 NULL other stuff
2 NULL bar more stuff
The sql would be something like:
SELECT IFNULL(int1, 0) AS int1, IFNULL(string1, '') AS string1 FROM table
Is this possible using the core? What would be great would be something like
s = select(ifnull(table.c.int1, 0), ifnull(table.c.string1, ''))
PostgreSQL doesn't support if null
instead of ifnull() you can use func.coalesce()
Syntax -
func.coalesce(Table.field, default_value)
Example -
func.coalesce(Order.price, 0)
func.coalesce(Student.name, ' ')
You should be able to use func from sqlalchemy.sql like this for arbitrary database functions (I think ifnull is db dependent, I use coalesce for postgresql):
from sqlalchemy.sql import select, func
# assuming you have imported table from somewhere!!
s = select([func.ifnull(table.c.int1, 0), func.ifnull(table.c.string1, '')])
