SQL null conversion

[Gerhard Häring]

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? 
> 
> ---snippet---
> 
> source_extract = "select foo,bar,baz from datatable"
> source.execute(source_extract)
> result_set = source.fetchall()
> 
> for row in result_set:
> 	# in reality, each feild here is being cleaned up in various ways-
> not shown for clarity
> 	insert_string = "insert into dest values (' " + row[0] + " ',' " +
> row[1] +" ',' " + row[3] +" ')"
> 	dest.execute(insert_string)

Aha. Your problem goes away if you use the DB-API way of quoting.
Let's suppose you're using a database module that uses '%s' as
placeholder (others like mxODBC use '?', Oracle adapters use named
paramters like ':p1', ':p2' - just check the docs of your module).

#v+
for row in result_set:
    ins_statement = "insert into dest values (%s)" % ", ".join(["%s"] * len(row))
    cursor.execute(ins_statement, row)
#v-

This uses cursor.execute with two parameters. If you've never seen
this, it's probably time to read the DB-API specification [1] ;-)

Gerhard

[1] http://www.python.org/peps/pep-0249.html
-- 
Favourite database:             http://www.postgresql.org/
Favourite programming language: http://www.python.org/
Combine the two:                http://pypgsql.sf.net/
Embedded database for Python:   http://pysqlite.sf.net/