Python-Ref > Databases and Python > Python and MySQL > Dealing with NULL
 
 

<-^^
Klíčová slova
Moduly
Knihovní funkce

Dealing with NULL

How to work with NULL in queries.
In many cases it is necessary to include NULL in out query. It is important to distinguish between the value NULL and a string "NULL".
The following examples show how to ask and how not to ask for NULL.
Expand/Shrink
Zdroj: (mysql7-1.py)
  1   # encoding: utf-8
  2   import MySQLdb
  3   from MySQLdb.cursors import DictCursor
  4   
  5   # connect
  6   connection = MySQLdb.connect( user="test", passwd="pass123", db="world", host="localhost", cursorclass=DictCursor)
  7   cursor1 = connection.cursor()
  8   
  9   # the following does not work
 10   cursor1.execute( "SELECT * FROM Country WHERE HeadOfState='NULL';")  string 'NULL'!
 11   for country in cursor1.fetchall():
 12     print country['Name'], country['Code']
 13   
 14   cursor1.close()
 15   connection.close()
Doba běhu: 671.9 ms
Expand/Shrink
Zdroj: (mysql7-2.py)
  1   # encoding: utf-8
  2   import MySQLdb
  3   from MySQLdb.cursors import DictCursor
  4   
  5   # connect
  6   connection = MySQLdb.connect( user="test", passwd="pass123", db="world", host="localhost", cursorclass=DictCursor)
  7   cursor1 = connection.cursor()
  8   
  9   # the following does not work
 10   cursor1.execute( "SELECT * FROM Country WHERE HeadOfState=NULL;")  = does not work!
 11   for country in cursor1.fetchall():
 12     print country['Name'], country['Code']
 13   
 14   cursor1.close()
 15   connection.close()
Doba běhu: 566.4 ms
Expand/Shrink
Zdroj: (mysql7-3.py)
  1   # encoding: utf-8
  2   import MySQLdb
  3   from MySQLdb.cursors import DictCursor
  4   
  5   # connect
  6   connection = MySQLdb.connect( user="test", passwd="pass123", db="world", host="localhost", cursorclass=DictCursor)
  7   cursor1 = connection.cursor()
  8   
  9   # the following works!
 10   cursor1.execute( "SELECT * FROM Country WHERE HeadOfState IS NULL;") 
 11   for country in cursor1.fetchall():
 12     print country['Name'], country['Code']
 13   
 14   cursor1.close()
 15   connection.close()
stdout:
San Marino SMR
Doba běhu: 528.5 ms
The following code shows how to use NULL in inserts - either the hard way or by using the smart excaping mechanism described above.
Expand/Shrink
Zdroj: (mysql7-4.py)
  1   # encoding: utf-8
  2   import MySQLdb
  3   from MySQLdb.cursors import DictCursor
  4   
  5   # connect
  6   connection = MySQLdb.connect( user="test", passwd="pass123", db="world", host="localhost", cursorclass=DictCursor)
  7   cursor1 = connection.cursor()
  8   
  9   data = ("bbi","Big Beer Island","South America","NULL")
 10   # This does not work as expected
 11   cursor1.execute( "INSERT INTO Country (Code,Name,Continent,HeadOfState) VALUES ('%s','%s','%s','%s');" % data)  quotes around the last %s
 12   cursor1.execute( "SELECT * FROM Country WHERE Code='bbi';") 
 13   for country in cursor1.fetchall():
 14     print country['Name'], country['HeadOfState']
 15   
 16   cursor1.close()
 17   connection.close()
stdout:
Big Beer Island NULL
Doba běhu: 705.9 ms
Expand/Shrink
Zdroj: (mysql7-5.py)
  1   # encoding: utf-8
  2   import MySQLdb
  3   from MySQLdb.cursors import DictCursor
  4   
  5   # connect
  6   connection = MySQLdb.connect( user="test", passwd="pass123", db="world", host="localhost", cursorclass=DictCursor)
  7   cursor1 = connection.cursor()
  8   
  9   data = ("bbi","Big Beer Island","South America","NULL")
 10   # This works, but is prone to errors
 11   cursor1.execute( "INSERT INTO Country (Code,Name,Continent,HeadOfState) VALUES ('%s','%s','%s',%s);" % data)  no quotes around the last %s
 12   cursor1.execute( "SELECT * FROM Country WHERE Code='bbi';") 
 13   for country in cursor1.fetchall():
 14     print country['Name'], country['HeadOfState']
 15   
 16   cursor1.close()
 17   connection.close()
stdout:
Big Beer Island None
Doba běhu: 554.0 ms
Expand/Shrink
Zdroj: (mysql7-6.py)
  1   # encoding: utf-8
  2   import MySQLdb
  3   from MySQLdb.cursors import DictCursor
  4   
  5   # connect
  6   connection = MySQLdb.connect( user="test", passwd="pass123", db="world", host="localhost", cursorclass=DictCursor)
  7   cursor1 = connection.cursor()
  8   
  9   data = ("bbi","Big Beer Island","South America",None)
 10   # This works great
 11   cursor1.execute( "INSERT INTO Country (Code,Name,Continent,HeadOfState) VALUES (%s,%s,%s,%s);", data)  smart way
 12   cursor1.execute( "SELECT * FROM Country WHERE Code='bbi';") 
 13   for country in cursor1.fetchall():
 14     print country['Name'], country['HeadOfState']
 15   
 16   cursor1.close()
 17   connection.close()
stdout:
Big Beer Island None
Doba běhu: 822.2 ms