NULL vs. None and MySQLdb

[Steve Holden]

<disclaimer>
I'm not a MySQL user, and therefore must assume that it follows the
SQL standard at least to a certain degree
</disclaimer>

"Geoffrey L. Wright" wrote:
> 
> On 18 Sep 2000 19:15:07 -0700, aahz at panix.com (Aahz Maruch) wrote:
> 
> >None is the Python equivalent of NULL; what's wrong with None?
> 
> Well ... I'm passing the the result contents of str(row) strait back
> to MySQL -- so that my SQL literally ends up reading something like:
> 
>   INSERT INTO tableName
>     (column1, column2, column3, column4)
>   VALUES
>     ('ohNo', 'hereIt', 'comes', None)
> 
This is, in general, a Bad Thing.  If your SQL string values contain
apostrophes (single-quotes) then you can end up creating ill-formed
SQL statements like

    INSERT INTO tableName
      (column1, column2, column3, column4)
    VALUES
      ('oh, No', 'it's', 'the Spanish Inquisition', None)
                     ^
SQL chokes here on malformed statement.  The second string should be
represented as 'it''s' according to the SQL standard.

You should therefore ALWAYS (except when you don't need to ... ;-)
make sure that these quotes are escaped, by using a simple function.
This function can also handle the NULL values returned as Python None.

e.g.:

def SQLEsc(s):
        if s == None:
                return "NULL"
        else:
                return "'"+string.replace(s, "'", "''")+"'"

Your statement should, therefore, become:

    INSERT INTO tableName
      (column1, column2, column3, column4)
    VALUES
      ( SQLEsc('ohNo'), SQLEsc('it's'),
        SQLEsc('the Spanish Inquisition'), SQLEsc(None))

> And the problem is the MySQL chokes on the unquoted "None", whereas it
> would just treat NULL as ... well ... NULL.
> 
OK, but how would you then propose to distinguish between a NULL-valued
field and one whose value is the string 'NULL'?

> But I'm brand-spankin' new to both python and its SQL interaction, and
> I'm _sure_ there must be some simple way of doing this.  Just to get
> things working I'm actually replacing all instances of None with NULL.
> Given the specific nature of my data in this case, that's not a big
> deal, but it is certainly a bad general solution.  Plus I'm looking at
> 20,000 rows over 5 tables, so it's really kinda S-L-O-W right now as
> well.  I image that kind of regular expression substitution done
> 100,000 times doesn't help the performance of my little script...
> 
> //glw

I hope the SQLEsc function outlined above will give you a painless way
of doing what you want.  Bear in mind that when interacting with a
database system *nothing* is ever quite as easy as you'd like it to be!
NULL in SQL is the natural way to express "Not available", "Not
relevant" and various other conditions.  None is a comparable value
in Python.

Bear in mind, though, that Python does not use the three-valued logic
which SQL does!

It's not as bad as it might seem, since one seems to end up constructing
SQL statements inside loops for most of the hairier applications.

Good luck.

regards
 Steve-- 
Helping people meet their information needs with training and technology.
703 967 0887      sholden at bellatlantic.net      http://www.holdenweb.com/