Clever Geek Handbook

Using IFNULL in sqlalchemy core

I am trying to use sqlalchemy core select rows from a table mysql

using IFNULL

.

Given the table:

id    int1    string1   other
1      7        NULL    other stuff
2      NULL     bar     more stuff

In sql it will be something like:

SELECT IFNULL(int1, 0) AS int1, IFNULL(string1, '') AS string1 FROM table

Is this possible using the kernel? What would be great would be something like

s = select(ifnull(table.c.int1, 0), ifnull(table.c.string1, ''))
+3


source to share


2 answers


You should be able to use the func from sqlalchemy.sql like this for any database functions (I think ifnull depends on db, 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, '')])
+3


source


PostgreSQL does not 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, ' ')
0


source






More articles:


All Articles