python dealing with Nonetype before cast\addition

Question

I'm pulling a row from a db and adding up the fields (approx 15) to get a total. But some field values will be Null, which causes an error in the addition of the fields (TypeError: unsupported operand type(s) for +: 'NoneType' and 'int')

Right now, with each field, I get the field value and set it to 'x#', then check if it is None and if so set 'x#' to 0.

Not very elegant...any advice on a better way to deal with this in python?

cc

Answer 1

You can do it easily like this:

result = sum(field for field in row if field)

Answer 2

Another (better?) option is to do this in the database. You can alter your db query to map NULL to 0 using COALESCE.

Say you have a table with integer columns named col1, col2, col3 that can accept NULLs.

Option 1:

SELECT coalesce(col1, 0) as col1, coalesce(col2, 0) as col2, coalesce(col3, 0) as col3
FROM your_table;

Then use sum() in Python on the returned row without having to worry about the presence of None.

Option 2: Sum the columns in the database and return the total in the query:

SELECT coalesce(col1, 0) + coalesce(col2, 0) + coalesce(col3, 0) as total
FROM your_table;

Nothing more to do in Python. One advantage of the second option is that you can select other columns in your query that are not part of your sum (you probably have other columns in your table and are making multiple queries to get different columns of the table?)