Removing null values when concatenating columns in SQLAlchemy

Question

I am trying to implement a function to concatenate columns based on inputs provided by the user. I have implemented it using a SQLAlchemy update statement:

update_statement = table.update().values({
        new_column_name: assignable
    })

where assignable is:

assignable = column_1 + column_2

where column_1 and column_2 are instances of sqlalchemy.sql.column.

It works fine but if one of the rows in column is empty i.e. has a None value it returns None as the result of concatenation.

I understand that's logical but is there a way I can convert None to '' (empty string) using this implementation?

Answer 1

I think you can maybe use the coaslesce function

http://docs.sqlalchemy.org/en/latest/core/functions.html

class sqlalchemy.sql.functions.coalesce(*args, **kwargs)

You can specify default values for null result :)

Try googling coalesce for more results about this feature

So your "convert None to '' (empty string)" can be done like this

coalesce(column1, '') + coalesce(column2, '')