python mysqldb inserting NULL using a prepared statement

Question

I'm having issues with python when I try to insert NULL into a field using a prepared mysqldb statement. The problem is that python doesn't like NULL without quotes and then MySQL doesn't like 'NULL' with quotes; so I'm trying to find a way to insert NULL that both, python and MySQL, will allow. Here is my scenario:

    sqlFailInsert = """INSERT INTO efix_headers ( PathID ) VALUES ( %s );"""
    //then later in my code
    failcur = self.db.query( self.sqlFailInsert, ( pathID if pathID else NULL ) )

This generates a python error of: global name 'NULL' is not defined but when I use: pathID if pathID else 'NULL' MySQL treats it as a string (by the way, pathID field is an integer field) and enters 0 for the value (as it should if you were entering a string into an int field).

So, my questions is, how can I insert NULL into the int field using a prepared statement and not violate python rules?

GreyZmeem · Accepted Answer · 2013-08-05 18:31:55Z

7

Use None instead of NULL

sqlFailInsert = """INSERT INTO efix_headers ( PathID ) VALUES ( %s );"""
//then later in my code
failcur = self.db.query( self.sqlFailInsert, ( pathID if pathID else None ) )

answered Aug 5, 2013 at 18:31

GreyZmeem

6601 gold badge7 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sadmicrowave Over a year ago

And MySQL will evaluate 'None' to NULL automatically?

GreyZmeem Over a year ago

Yes. "SQL NULL values are represented by the Python None singleton on input and output". More information at: python.org/dev/peps/pep-0249

Collectives™ on Stack Overflow

python mysqldb inserting NULL using a prepared statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related