SQL null conversion

Jeff Epler jepler at unpythonic.net
Wed Jan 22 11:53:36 EST 2003


On Wed, Jan 22, 2003 at 11:23:51AM -0500, Jeff Sacksteder wrote:
> I am moving/converting data from a production system to a data warehouse
> using python for the data transformation. I iterate through the result list
> building sql strings to do the inserts that then get sent to the destination
> connection. Occasionally I hit a row where a feild is null, returning
> 'None'. When I try to build a string containing 'None' I get an exception.
> What would be the best way to convert that to an empty string in the general
> case? 

You want your query to look like
    data.execute("insert into dest values (?, ?, ?)", row)
or, if your database uses the %-style formats, 
    data.execute("insert into dest values (%s, %s, %s)", row)
read the db-api spec for more about this, as well as how to check which
style your module uses.

If you really want to replace SQL null / Python None with the empty
string, then use something like the following:
    def replace_none_with_empty_string(i):
        if i is None: return ""
        return i

    row = [replace_none_with_empty_string(item) for item in row]

Jeff





More information about the Python-list mailing list