473,842 Members | 1,757 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 52901
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(field name) 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.para mstyle

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(field name) 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("in sert into mytable(col1) values (%s)", (var1,))
var1 = None
cur.execute("in sert 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(sq l, data)

and

curs.execute(sq l % 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("loc alhost", db="temp", user="root")
curs = conn.cursor()
There's the obvious way:
curs.execute("I NSERT INTO t1 (f1, f2) VALUES ('row1', NULL)") 1L

Then there's the parameterised way:
curs.execute("I NSERT 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("I NSERT 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*******@hotm ail.com> wrote in message
news:11******** **************@ g49g2000cwa.goo glegroups.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 =
SomeThingOrOthe r"
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:
StrToConcatenat eIntoSqlStateme nt = "Null"
else:
StrToConcatenat eIntoSqlStateme nt = 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:
StrToConcatenat eIntoSqlStateme nt = "Null"
else:
StrToConcatenat eIntoSqlStateme nt = 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 StrToConcatenat eIntoSqlStateme nt
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

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

Similar topics

2
3955
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 skills, especially with MySQL are rudimentary</disclaimer> However my code (link below) fails, the nested database call does not return any data and this has me stumped. Any help will be much appreciated. Many thanks in advance
2
437
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
3955
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 version of this document can be found at: http://prdownloads.sourceforge.net/souptonuts/README_mysql.txt?download
1
3032
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 which references A_TAB.AID. I insert valid data into both tables. However, the foreign key constraint is NOT being enforced. The database allows be to enter any integer into B_TAB.AID regardless of weather that value exists anywhere in the...
1
3386
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 uses his own php shopping cart to receive customer orders. The configuration was done via cPanel with no external modifications - which produced no protests when built, ran and connected with no
17
1792
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 = amember_db.cursor() # execute SQL statement sql = """SELECT payment_id FROM amember_payments WHERE
6
38540
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 through this without much trouble. Programming knowledge is not required. Index What is SQL? Why MySQL? Installing MySQL. Using the MySQL command line interface
1
9599
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 things (stored procedures, functions).. we have to manually edit. That time, we face some interesting challenges.. I failed to document all of them, but whatever I can share with u.. I will try.. :) ...
0
9865
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9709
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10609
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10303
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9446
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7853
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7025
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5694
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5882
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

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.