[SQL] Right way to set a variable to NULL?

[Steve Holden]

Gilles Ganault wrote:
> Hello
> 
> I use regexes to extract information from a text file. Some of the
> records don't have e-mails or www addresses, so those must match Null
> in SQL, but None doesn't work as expected:
> 
> =======
> 	if itemmatch:
> 		web = itemmatch.group(1).strip()
> 	else:
> 		web = None
> 
> 	sql = 'INSERT INTO mytable  (name,address,web,mail) VALUES
> ("%s","%s","%s","%s","%s")' % (name,address,web,mail)
> =======
> 
I take it this code isn't pasted from an actual program, since the
statement above would give a run time ValueError.

> Is there a better way in Python to have a variable match NULL than
> building the SQL query step by step?
> 
You could, of course, have replaced None in your code with "NULL", and
that should have removed the database errors you were observing.

Retaining the None, however, you could move to using parameterized
queries. Replace each '"%s"' in your sql string with the appropriate
parameter mark (could be '%s', could be '?', could be three other things
depending on your database platform.

Then you provide a tuple of data items (using None for the NULL values)
as a second argument to the cursor's .execute() method, and the database
driver module inserts the data in to the SQL statement, escaping things
where appropriate.

So, supposing you were using MySQL or PostgreSQL you would write

	if itemmatch:
		web = itemmatch.group(1).strip()
	else:
		web = None

	sql = '''INSERT INTO mytable  (name,address,web,mail)
                 VALUES (%s, %s, %s, %s)'''
        cursor.execute(sql, (name,address,web,mail))

Don't forget to call the connection's .commit() method to ensure the
database changes are made permanent.

regards
 Steve
-- 
Steve Holden        +1 571 484 6266   +1 800 494 3119
Holden Web LLC              http://www.holdenweb.com/