CODEWITHSUNDEEP
pythonmysqlsqlalchemy
Luke
Luke

Reputation: 7089

Using IFNULL in sqlalchemy core

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, ''))

Upvotes: 9

Views: 16828

Answers (2)

Mohammad Aarif
Mohammad Aarif

Reputation: 1705

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, ' ')

Upvotes: 19

Ian Wilson
Ian Wilson

Reputation: 9059

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, '')])

Upvotes: 10

Related Questions