Right way to set a variable to NULL?

[John Machin]

On Dec 27, 7:33 am, Gilles Ganault <nos... at nospam.com> 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)
> =======

You have MULTIPLE problems here.

PROBLEM 1:
That code won't even create the "sql" string; you have %s 5 times, but
only 4 elements in the tuple. When asking for help, always post (1)
the code that you actually ran (2) the result or error message that
you got [merely saying "doesn't work" is not very useful at all].

PROBLEM 2:
Having fixed problem 1, the result is not valid SQL; you get VALUES
("Gilles", ...; it should be VALUES('Gilles', ...

PROBLEM 3:
Having fixed problem 2: If you have a name like "L'Hopital" or
"O'Reilly" the result is VALUES('L'Hopital', ...; it should be VALUES
('L''Hopital', ...

*AND* you don't have to worry about all the rules for SQL constant
values; the worrying and work has been done for you.

> Is there a better way in Python to have a variable match NULL than
> building the SQL query step by step?

Yes. Don't construct the SQL query by Python string formatting. In
fact, don't *ever* construct *any* SQL query that needs args by using
string formatting/interpolation in *any* language, even when you think
it's "working" -- see http://en.wikipedia.org/wiki/SQL_injection ...
search for "SQL injection attack" for more references.

Essential reference: the Python Database API Specification v2.0
(http://www.python.org/dev/peps/pep-0249/)

You need something like:
sql = "INSERT INTO mytable (name,address,web,mail) VALUES(?,?,?,?)"
cursor.execute(sql, (name, address, web, mail))

Some database software uses something other than ? to mark parameter
positions; you may need (for example) ... VALUES(%s,%s,%s,%s) ...
(*NOT* the same meaning as %s in Python!). Read about "paramstyle" in
the DB API spec, and check the documentation for the DB software that
you are using.

HTH,
John