Python, Mysql, insert NULL

[Steve Holden]

Python_it wrote:
> I know how to insert values in a database.
> That's not my problem!
> My problem is how i insert NULL values in de mysql-database.

So you *don't* know how to insert values in a database: as Laszlo wrote, 
you might be best using parameterized queries.

> None is een object in Python and NULL not.
> None is not converted to NULL?
> Table shows None and not NULL!
> 
If that's the case then perhaps the field isn't nullable? Or perhaps you 
mader a mistake ...

Pay careful attention to the difference between

   curs.execute(sql, data)

and

   curs.execute(sql % data)

Let's suppose I create a MySQL table:

mysql> create table t1(
     ->   f1 varchar(10) primary key,
     ->   f2 varchar(20)
     -> );
Query OK, 0 rows affected (0.44 sec)

Let's try and create a few records in Python:

 >>> conn = db.connect("localhost", db="temp", user="root")
 >>> curs = conn.cursor()

There's the obvious way:

 >>> curs.execute("INSERT INTO t1 (f1, f2) VALUES ('row1', NULL)")
1L

Then there's the parameterised way:

 >>> curs.execute("INSERT INTO t1 (f1, f2) VALUES (%s, %s)", ("row2", None))
1L

This is to be preferred because the data tuple can contain general 
expressions, so you just have to ensure that the name bound to the 
column value contains None rather than some string.

Then there's the wrong way"

 >>> curs.execute("INSERT INTO t1 (f1, f2) VALUES ('%s', '%s')" % 
("row3", None))
1L
 >>>

This really executes

INSERT INTO t1 (f1, f2) VALUES ('row3', 'None')

What does MySQL have to say about all this?

mysql> select * from t1;
+------+------+
| f1   | f2   |
+------+------+
| row1 | NULL |
| row2 | NULL |
| row3 | None |
+------+------+
3 rows in set (0.00 sec)

And the moral of the story is to believe someone is actually trying to 
help you unless you have definite evidence to the contrary. Otherwise 
people will pretty soon stop trying to help you ...

regards
  Steve
-- 
Steve Holden       +44 150 684 7255  +1 800 494 3119
Holden Web LLC                     www.holdenweb.com
PyCon TX 2006                  www.python.org/pycon/