Python, Mysql, insert NULL

[Python_it]

Python 2.4
MySQL-python.exe-1.2.0.win32-py2.4.zip

How can I insert a NULL value in a table (MySQL-database).
I can't set a var to NULL? Or is there a other possibility?
My var must be variable string or NULL.
Becaus i have a if statement:
if ....
cursor.execute(".................insert NULL ..............")
if ....
cursor.execute(".................insert "string" ..............")

[Laszlo Zsolt Nagy]

Python_it wrote:

Python 2.4
MySQL-python.exe-1.2.0.win32-py2.4.zip

How can I insert a NULL value in a table (MySQL-database).
I can't set a var to NULL? Or is there a other possibility?
My var must be variable string or NULL.
Becaus i have a if statement:
if ....
cursor.execute(".................insert NULL ..............")
if ....
cursor.execute(".................insert "string" ..............")

Use parameters! For example, did you try:

cursor.execute(" insert into tablename(fieldname) values (%s)",[value])

None will be converted to NULL, any other value will be quoted as neccesary.

BTW, you did not write which driver are you using.
Usage of parameters is different for each driver, but it is standardized.
If it is DB API 2.0 compatible, then parametrized queries should work as
desicribed in PEP 0249:

http://www.python.org/peps/pep-0249.html

Under 'cursor objects' section, look for the '.execute' method.

[Laszlo Zsolt Nagy]

BTW, you did not write which driver are you using.

Oh, you did. Sorry. :-( Import your DB module 'yourmodule' and then

print yourmodule.paramstyle

Description of paramstyle is also in PEP249:

paramstyle

String constant stating the type of parameter marker
formatting expected by the interface. Possible values are
[2]:

'qmark' Question mark style,
e.g. '...WHERE name=?'
'numeric' Numeric, positional style,
e.g. '...WHERE name=:1'
'named' Named style,
e.g. '...WHERE name=:name'
'format' ANSI C printf format codes,
e.g. '...WHERE name=%s'
'pyformat' Python extended format codes,
e.g. '...WHERE name=%(name)s'
Best,

Les

e.g. '...WHERE name=%(name)s'

[Python_it]

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.
None is een object in Python and NULL not.
None is not converted to NULL?
Table shows None and not NULL!

[deelan]

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.
None is een object in Python and NULL not.
None is not converted to NULL?
Table shows None and not NULL!

None is converted to mysql's NULL and vice versa. It sounds
you are passing the *string* "None" to mysql, with it isn't
the same thing.

Adapting the Laszlo's example already posted:

cursor.execute("insert into tablename(fieldname) values (%s)", [None])

HTH.

--
deelan, #1 fan of adriana lima!
<http://www.deelan.com/>

[Gerhard Häring]

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.
None is een object in Python and NULL not.
None is not converted to NULL?
Table shows None and not NULL!

As Laszlo wrote, "None will be converted to NULL" for the Python => SQL
direction. And also, NULL will be converted to None for SQL => Python
direction.

And to avoid unneccessary if-then-else you should follow his advice and
use parametrized queries. I. e.

cursor's execute method has two parameteres:

1) SQL query with placeholders
2) parameters

For example:

var1 = "Joe's dog"
cur.execute("insert into mytable(col1) values (%s)", (var1,))
var1 = None
cur.execute("insert into mytable(col1) values (%s)", (var1,))

if you use MySQLdb (the most sensible choice for a MySQL Python database
adapter).

Because MySQLdb uses the pyformat param style, you use the %s
placeholder always, no matter which type your parameter will be.

Also, the tip to read the DB-API specification
http://www.python.org/peps/pep-0249.html is a good one in my opinion.
It really pays off to learn how to do things the DB-API way.

HTH,

-- Gerhard

[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 [phone] [phone]
Holden Web LLC www.holdenweb.com
PyCon TX 2006 www.python.org/pycon/

[Thomas Bartkus]

"Python_it" <py*******@hotmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

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.
None is een object in Python and NULL not.
None is not converted to NULL?
Table shows None and not NULL!

MySQL accepts and understands the keyword "Null" directly.
When you list your VALUE parameters in an INSERT statement, a null is simply
the string "Null".
Don't quote the word "Null" inside your sql statement, MySQL understands it
directly. If you do quote it, MySQL will think you are talking about a
string value that happens to be spelled "N u l l"

Your sql string needs to end up looking something like this -
sql = "INSERT INTO SomeTable (This, That, TheOther) VALUES (3, 12, Null)"
or
sql = "UPDATE sometable SET TheOther=Null WHERE something =
SomeThingOrOther"
before you
cursor.execute(sql)

Notice that the MySQL keyword "Null" stands naked within each string.

Others here have pointed out that the Python keyword "None" is converted to
"Null" when passed to MySQL. I don't quite understand this and don't really
care. If I have a Python variable that has a value None, and I want to
transmit this to MySQL as Null - I would:

if somevar == None:
StrToConcatenateIntoSqlStatement = "Null"
else:
StrToConcatenateIntoSqlStatement = somevar

All of which assumes, of course, that the field you are targeting will
accept a Null value.
Thomas Bartkus

[Steve Holden]

Thomas Bartkus wrote:
[...]

Others here have pointed out that the Python keyword "None" is converted to
"Null" when passed to MySQL. I don't quite understand this and don't really
care. If I have a Python variable that has a value None, and I want to
transmit this to MySQL as Null - I would:

if somevar == None:
StrToConcatenateIntoSqlStatement = "Null"
else:
StrToConcatenateIntoSqlStatement = somevar

All of which assumes, of course, that the field you are targeting will
accept a Null value.
Thomas Bartkus

If you don't understand parameterized SQL queries you would do well to
refrain from offering database advice :-)

Presumably you always check whether StrToConcatenateIntoSqlStatement
contains no apostrophes before you actually construct the SQL?

Can we say "SQL injection exploit"?

regards
Steve
--
Steve Holden [phone] [phone]
Holden Web LLC www.holdenweb.com
PyCon TX 2006 www.python.org/pycon/

[Python_it]

Thanks for the many replies!
The problem was that is use '%s', i have to use %s and then my problem
is solved.

[Thomas Bartkus]

"Steve Holden" <st***@holdenweb.com> wrote in message
news:ma*************************************@pytho n.org...

If you don't understand parameterized SQL queries you would do well to
refrain from offering database advice :-)

Did the poster ask a question about parameterized queries or server
security?
Presumably you always check whether StrToConcatenateIntoSqlStatement
contains no apostrophes before you actually construct the SQL?

Can we say "SQL injection exploit"?

Not every query passes along public internet wires and all the guy asked for
was how to insert a Null.

But - I really do appreciate your concern :-)
Thomas Bartkus