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

[Gilles Ganault]

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)
=======

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

Thank you.

 

[Martin]

Hi,

I'd create a simple wrapper object

class MailAddress(object):
def __init__(self, address=None):
self.address = address
def __str__(self):
if address:
return self.adress
return "NULL"

you can keep most of your code just replace the original instantiation
of the mail str-instance with the MailAddress

This is just scratched up quickly. Hope it helps

Martin

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

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

Thank you.

--
http://soup.alt.delete.co.at
http://www.xing.com/profile/Martin_Marcher
http://www.linkedin.com/in/martinmarcher

You are not free to read this message,
by doing so, you have violated my licence
and are required to urinate publicly. Thank you.

Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html

 

[John Machin]

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

 

[John Machin]

There is an obvious typo above:
 >     if address:
should be:
       if self.address:

Or, you could replace the __str__ function with:
     def __str__(self):
         return self.address or "NULL"

The above all have the same characteristic: if the input is a zero-
length string, then NULL is inserted into the database instead of a
zero-length string. Some folks (not just pedants!) regard that as an
important difference.

 

[Martin]

The above all have the same characteristic: if the input is a zero-
length string, then NULL is inserted into the database instead of a
zero-length string. Some folks (not just pedants!) regard that as an
important difference.

agreed but I understood the OP specifically wanted NULL and not ''.

of course for data gathering in web apps I'd personally make the mail
attribute a NOT NULL and reject anybody who wouldn't give me their
mail address. After all mail is the way to get in touch with my
customers/user if they register on my site, and if it's only for a
password reset link.

/martin


--
http://soup.alt.delete.co.at
http://www.xing.com/profile/Martin_Marcher
http://www.linkedin.com/in/martinmarcher

You are not free to read this message,
by doing so, you have violated my licence
and are required to urinate publicly. Thank you.

Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html

 

[John Machin]

agreed but I understood the OP specifically wanted NULL and not ''.

He wanted None inserted into the database as NULL if his regex didn't
match. He didn't invite you to change a matching '' to NULL with no
announcement.