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" ..")
--
http://mail.python.org/mailman/listinfo/python-list
Python, Mysql, insert NULL
Hi, I've been having problems using Python to insert data into a MySQL database. I've donw web searches and pulled my hairs out getting it to work but cannot. I have no trouble doing select queries, but when I use Python to insert, though I do not receive any errors from python, the table does not get populated. I've just followed the information from here by Steve Holden: http://mail.python.org/pipermail/python-list/2005-October/302405.html to the tee. No errors and the same output as shown in the above example. Yet, there isn't any data in my table. Am I missing something so obvious?? Please help! This has frustrated me for days now. regards, Yaron -- http://mail.python.org/mailman/listinfo/python-list
Re: Python, Mysql, insert NULL
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.
--
http://mail.python.org/mailman/listinfo/python-list
Re: Python, Mysql, insert NULL
> > 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' -- http://mail.python.org/mailman/listinfo/python-list
Re: Python, Mysql, insert NULL
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! -- http://mail.python.org/mailman/listinfo/python-list
Re: Python, Mysql, insert NULL
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
--
http://mail.python.org/mailman/listinfo/python-list
Re: Python, Mysql, insert NULL
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/
--
http://mail.python.org/mailman/listinfo/python-list
Re: Python, Mysql, insert NULL
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!
Re: Python, Mysql, insert NULL
"Python_it" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: Python, Mysql, insert NULL
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/ -- http://mail.python.org/mailman/listinfo/python-list
Re: Python, Mysql, insert NULL
Thanks for the many replies! The problem was that is use '%s', i have to use %s and then my problem is solved. -- http://mail.python.org/mailman/listinfo/python-list
Re: Python, Mysql, insert NULL
"Steve Holden" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > > 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: Python, Mysql, insert NULL
I figured this out. Because I am using InnoDB, I need to add the commit statement. Phew. Yaron Butterfield wrote: > Hi, > > I've been having problems using Python to insert data into a MySQL > database. I've donw web searches and pulled my hairs out getting it > to work but cannot. I have no trouble doing select queries, but when I > use Python to insert, though I do not receive any errors from python, > the table does not get populated. > > I've just followed the information from here by Steve Holden: > http://mail.python.org/pipermail/python-list/2005-October/302405.html > > to the tee. No errors and the same output as shown in the above > example. Yet, there isn't any data in my table. > Am I missing something so obvious?? Please help! This has frustrated > me for days now. > > regards, > Yaron > -- http://mail.python.org/mailman/listinfo/python-list
