473,320 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Python, Mysql, insert NULL

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" ..............")

Oct 5 '05 #1
10 52133
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.
Oct 5 '05 #2

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'
Oct 5 '05 #3
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!

Oct 5 '05 #4
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/>


Oct 5 '05 #5
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

Oct 5 '05 #6
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/

Oct 5 '05 #7
"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
Oct 5 '05 #8
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 +44 150 684 7255 +1 800 494 3119
Holden Web LLC www.holdenweb.com
PyCon TX 2006 www.python.org/pycon/

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

Oct 6 '05 #10
"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

Oct 6 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Simon | last post by:
Hi, I am having a little problem with my PHP - MySQl code, I have two tables (shown below) and I am trying populate a template page with data from both. <disclaimer>Now I would like to say my...
2
by: Asfand Yar Qazi | last post by:
Ahem.. Anyway, here's whats happening... construct tables in MySQL: DROP TABLE EMP; CREATE TABLE EMP (EMPNO INT(4) NOT NULL, ENAME CHAR(6) NOT NULL,
0
by: Mike Chirico | last post by:
Interesting Things to Know about MySQL Mike Chirico (mchirico@users.sourceforge.net) Copyright (GPU Free Documentation License) 2004 Last Updated: Mon Jun 7 10:37:28 EDT 2004 The latest...
1
by: Saqib Ali | last post by:
I have created 2 tables in my MySQL database. A_TAB and B_TAB. They have auto-incrementing integer primary keys respectively named A_ID & B_ID. When I created B_TAB, I declared a field named A_ID...
1
by: jlee | last post by:
I'm pretty much a newbie on mysql, and I need some help. I am running mysql Ver 12.22 Distrib 4.0.24, for portbld-freebsd5.4 (i386) on a server hosting an active website. The site's developer...
17
by: erikcw | last post by:
Hi all, I'm trying to run the following query: amember_db = MySQLdb.connect(host="localhost", user="**********", passwd="*****", db="*******") # create a cursor self.amember_cursor =...
6
Atli
by: Atli | last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users. Anyone should be able to get...
1
ssnaik84
by: ssnaik84 | last post by:
Hi Guys, Last year I got a chance to work with R&D team, which was working on DB scripts conversion.. Though there is migration tool available, it converts only tables and constraints.. Rest of...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.