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

Discussion in 'Python' started by Gilles Ganault, Dec 26, 2008.

  1. 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.
    Gilles Ganault, Dec 26, 2008
    #1
    1. Advertising

  2. Gilles Ganault

    Martin Guest

    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

    2008/12/26 Gilles Ganault <>:
    > 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://mail.python.org/mailman/listinfo/python-list
    >




    --
    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
    Martin, Dec 26, 2008
    #2
    1. Advertising

  3. Gilles Ganault

    John Machin Guest

    Re: Right way to set a variable to NULL?

    On Dec 27, 7:33 am, 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)
    > =======


    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, Dec 26, 2008
    #3
  4. Gilles Ganault

    John Machin Guest

    Re: Right way to set a variable to NULL?

    On Dec 27, 8:16 am, Scott David Daniels <> wrote:
    > Martin wrote:
    > > ...
    > > class MailAddress(object):
    > >   def __init__(self, address=None):
    > >     self.address = address
    > >   def __str__(self):
    > >     if address:
    > >       return self.adress
    > >     return "NULL"

    >
    > 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.
    John Machin, Dec 26, 2008
    #4
  5. Gilles Ganault

    Martin Guest

    Re: Right way to set a variable to NULL?

    2008/12/26 John Machin <>:
    > 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
    Martin, Dec 27, 2008
    #5
  6. Gilles Ganault

    John Machin Guest

    Re: Right way to set a variable to NULL?

    On Dec 27, 11:05 am, Martin <> wrote:
    > 2008/12/26 John Machin <>:
    >
    > > 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 ''.


    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.
    John Machin, Dec 27, 2008
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Kivak Wolf
    Replies:
    2
    Views:
    10,064
    Kivak Wolf
    Jun 28, 2005
  2. Replies:
    5
    Views:
    26,599
    Mike Schilling
    Mar 29, 2006
  3. =?Utf-8?B?UmV6YSBTb2xvdWtp?=

    How to set DateTime variable to Null in C#?

    =?Utf-8?B?UmV6YSBTb2xvdWtp?=, Jun 29, 2006, in forum: ASP .Net
    Replies:
    3
    Views:
    144,737
    Mark Rae
    Jun 29, 2006
  4. G Fernandes
    Replies:
    9
    Views:
    590
    DHOLLINGSWORTH2
    Feb 27, 2005
  5. David Thielen

    Is TabIndex the right way to set the order of controls?

    David Thielen, Feb 27, 2007, in forum: ASP .Net Web Controls
    Replies:
    1
    Views:
    193
    Steven Cheng[MSFT]
    Feb 28, 2007
Loading...

Share This Page