473,289 Members | 2,108 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,289 software developers and data experts.

Can't seem to insert rows into a MySQL table

I'm trying to add a row to a MySQL table using insert. Here is the code:

connection = MySQLdb.connect(host="localhost", user="root", passwd="pw",
db="japanese")
cursor = connection.cursor()
cursor.execute("INSERT INTO edict (kanji, kana, meaning) VALUES (%s, %s,
%s)", ("a", "b", "c") )
connection.close()

After running, a SELECT * on the table shows no new rows added. Adding
rows using the MySQL client works fine. With the Python script, nothing.
There are no exceptions raised or any output at all. The rowcount of the
cursor is 1 after the execute is 1 and the table's auto_increment value
is increased for each insert done. Can anybody help? I'm fairly new to
MySQL so I'm afraid its going to be a stupid oversight on my part. Thank
you.
Jul 18 '05 #1
11 7120
grumfish wrote:
connection = MySQLdb.connect(host="localhost", user="root", passwd="pw",
db="japanese")
cursor = connection.cursor()
cursor.execute("INSERT INTO edict (kanji, kana, meaning) VALUES (%s, %s,
%s)", ("a", "b", "c") )
connection.close()


Just a guess "in the dark" (I don't use MySQL): is "commit" implicit, or
do you have to add it yourself?

-pu
Jul 18 '05 #2
grumfish wrote:
The rowcount of the
cursor is 1 after the execute is 1 and the table's auto_increment value
is increased for each insert done.


If the auto_increment is increased, then it seems like the row was
inserted. Are you sure the problem is not with your SELECT attempt?
Just a guess, but it seems like the first time I used MySQLdb, I was
confused by the need to do a "fetchall()" (or "fetchone()" or
"fetchmany()") after executing the SELECT.

Something like this (not tested):

result = cursor.execute(SELECT * FROM edict WHERE kanji = 'a')
print result.fetchall()

HTH,
Steve P.
Jul 18 '05 #3
On Sat, 12 Mar 2005 18:24:10 GMT, grumfish <no****@nowhere.com>
declaimed the following in comp.lang.python:
I'm trying to add a row to a MySQL table using insert. Here is the code:

connection = MySQLdb.connect(host="localhost", user="root", passwd="pw",
db="japanese")
cursor = connection.cursor()
cursor.execute("INSERT INTO edict (kanji, kana, meaning) VALUES (%s, %s,
%s)", ("a", "b", "c") )
connection.close()
What is the definition of the table in use? Unique indices, etc.

My test, using the following test table definition:

mysql> show columns from edict;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| kanji | char(50) | | | | |
| kana | char(50) | | | | |
| meaning | char(50) | | | | |
| ID | int(10) unsigned | | PRI | NULL | auto_increment |
+---------+------------------+------+-----+---------+----------------+
4 rows in set (0.17 sec)

and this code:

-=-=-=-=-=-=-=-=-
import MySQLdb

cn = MySQLdb.connect(user="wulfraed",
passwd="tr3th4mk4r",
db="test",
host="localhost")
cr = cn.cursor()

res = cr.execute("insert into edict(kanji, kana, meaning) values (%s,
%s, %s)",
("a", "b", "c"))

print res

##cr.close()
##
##cr = cn.cursor()

res = cr.execute("select * from edict")

print res

for rw in cr.fetchall():
print rw

cr.close()
cn.close()
-=-=-=-=-=-=-=-

gives the following (it keeps adding a row each time I run)

G:\My Documents>python t.py
1
5
('a', 'b', 'c', 1L)
('a', 'b', 'c', 2L)
('a', 'b', 'c', 3L)
('a', 'b', 'c', 4L)
('a', 'b', 'c', 5L)

G:\My Documents>
-- ================================================== ============ <
wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
================================================== ============ <
Home Page: <http://www.dm.net/~wulfraed/> <
Overflow Page: <http://wlfraed.home.netcom.com/> <

Jul 18 '05 #4
grumfish wrote:
I'm trying to add a row to a MySQL table using insert. Here is the code:

connection = MySQLdb.connect(host="localhost", user="root", passwd="pw",
db="japanese")
cursor = connection.cursor()
cursor.execute("INSERT INTO edict (kanji, kana, meaning) VALUES (%s, %s,
%s)", ("a", "b", "c") )
connection.close()


which version of MySQLdb are you running? versions
1.1.6 and below gained a connection.autocommit) method set by default
to *false*.

Try this, instead:

connection = MySQLdb.connect(host="localhost", user="root", passwd="pw",
db="japanese")

connection.autocommit(True) # <--- note this
cursor = connection.cursor()

....other commands...

doing this you tell MySQL to automatically commit changes to db after
every UPDATE or INSERT. turning autocommit to false is useful when
you wish to do changes in batches and maybe rollback the entire operation
if something went wrong (see commit() and rollback() methods for this).

hope this helps,
deelan.

--
"Però è bello sapere che, di questi tempi spietati, almeno
un mistero sopravvive: l'età di Afef Jnifen." -- dagospia.com
Jul 18 '05 #5
Patrick Useldinger wrote:
Just a guess "in the dark" (I don't use MySQL): is "commit" implicit, or
do you have to add it yourself?


Thank you. Inserts work fine now.

Another question. I'm trying to insert Japanese text into the table. I
created the database using 'CHARACTER SET UTF8'. In Python I do a
..encode("utf-8") on any strings before inserting them into the database.
When I try to read them back from the database I don't get the original
string. I've played with .decode("utf-8") on strings returned from MySQL
but with no luck. How can I insert Japanese text into a MySQL database
and the read it out again?
Jul 18 '05 #6
deelan wrote:
which version of MySQLdb are you running? versions
1.1.6 and below gained a connection.autocommit) method set by default

ehm, 1.1.6 and *above*, of course. :)

--
"Però è bello sapere che, di questi tempi spietati, almeno
un mistero sopravvive: l'età di Afef Jnifen." -- dagospia.com
Jul 18 '05 #7
The default character set used by MySQL for the connection is latin1.
If you control the server, you can configure this in the system my.cnf.
Otherwise, it is possible to set it in a local configuration file and
use the read_default_file option to connect to set it.

http://dev.mysql.com/doc/mysql/en/ch...onnection.html

I think setting the default to utf-8 on the server is your best bet, if
you are able to.

Jul 18 '05 #8
Try to use % instead of a comma (a Python quirk) and quotes around your
strings (a MySQL quirk):

cursor.execute("INSERT INTO edict (kanji, kana, meaning) VALUES ('%s',
'%s', '%s')" % ("a", "b", "c") )

Frederic
----- Original Message -----
From: "grumfish" <no****@nowhere.com>
Newsgroups: comp.lang.python
To: <py*********@python.org>
Sent: Saturday, March 12, 2005 7:24 PM
Subject: Can't seem to insert rows into a MySQL table

I'm trying to add a row to a MySQL table using insert. Here is the code:

connection = MySQLdb.connect(host="localhost", user="root", passwd="pw",
db="japanese")
cursor = connection.cursor()
cursor.execute("INSERT INTO edict (kanji, kana, meaning) VALUES (%s, %s,
%s)", ("a", "b", "c") )
connection.close()

After running, a SELECT * on the table shows no new rows added. Adding
rows using the MySQL client works fine. With the Python script, nothing.
There are no exceptions raised or any output at all. The rowcount of the
cursor is 1 after the execute is 1 and the table's auto_increment value
is increased for each insert done. Can anybody help? I'm fairly new to
MySQL so I'm afraid its going to be a stupid oversight on my part. Thank
you.
--
http://mail.python.org/mailman/listinfo/python-list


Jul 18 '05 #9
In <ma*************************************@python.or g>, Anthra Norell
wrote:
Try to use % instead of a comma (a Python quirk) and quotes around your
strings (a MySQL quirk):

cursor.execute("INSERT INTO edict (kanji, kana, meaning) VALUES ('%s',
'%s', '%s')" % ("a", "b", "c") )


AFAIK grumfish made the Right Thingâ„¢. If you use '%', some interesting
things can happen, e.g. if your values contain "'" characters. The "%s"s
without quotes and the comma let the DB module format and *escape* the
inserted values for you in a safe way.

Ciao,
Marc 'BlackJack' Rintsch
Jul 18 '05 #10
Very true!
I could verify that cursor.execute () seems to understand "... %s ...",
...."string"... where print () doesn't.. I didn't know that.
I could also verify that gumfish's ineffective insertion command works fine
for me. (Python 2.4, mysql-3.23.38). So it looks like the problem is with
MySQL (e.g. table name, column names, ...)
Frederic

----- Original Message -----
From: "Marc 'BlackJack' Rintsch" <bj****@gmx.net>
Newsgroups: comp.lang.python
To: <py*********@python.org>
Sent: Monday, March 14, 2005 11:55 PM
Subject: Re: Can't seem to insert rows into a MySQL table

In <mailman.410.1110836641.1799.py*********@python.or g>, Anthra Norell
wrote:
Try to use % instead of a comma (a Python quirk) and quotes around your
strings (a MySQL quirk):

cursor.execute("INSERT INTO edict (kanji, kana, meaning) VALUES ('%s', '%s', '%s')" % ("a", "b", "c") )


AFAIK grumfish made the Right Thingâ„¢. If you use '%', some interesting
things can happen, e.g. if your values contain "'" characters. The "%s"s
without quotes and the comma let the DB module format and *escape* the
inserted values for you in a safe way.

Ciao,
Marc 'BlackJack' Rintsch
--
http://mail.python.org/mailman/listinfo/python-list


Jul 18 '05 #11
Anthra Norell wrote:
Very true!
I could verify that cursor.execute () seems to understand "... %s ....", ..."string"... where print () doesn't.. I didn't know that.
I could also verify that gumfish's ineffective insertion command works fine for me. (Python 2.4, mysql-3.23.38). So it looks like the problem is with MySQL (e.g. table name, column names, ...)


I'm not sure if this is what you are referring to, but here's the
definitive answer:

MySQLdb uses %s as a parameter placeholder. When you pass the
parameters in a tuple as the second argument (as PEP-249 and the API
documentation tells you to), MySQLdb escapes any special characters
that may be present, and adds quotation marks as required.

However, the only parameters you can pass in that way are SQL literal
values, i.e. 15, 'string literal', '2005-03-15', etc. You can NOT pass
in things like names (column, table, database) or other pieces of
arbitrary SQL; these would be treated as strings, and thus be quoted.

Anything other than literal values has to be added some other way,
either by use of format strings and % or string concatenation, or
whatnot. You can double the % (i.e. %%s) so that if you also have
parameters to pass, their placeholder will be preserved.

You need to be very careful about what you allow to be inserted into
your SQL query when not using the quoting/escaping of execute().
In <ma*************************************@python.or g>, Anthra Norell wrote:
Try to use % instead of a comma (a Python quirk) and quotes around your strings (a MySQL quirk):

cursor.execute("INSERT INTO edict (kanji, kana, meaning) VALUES ('%s', '%s', '%s')" % ("a", "b", "c") )


Don't do this.

Jul 18 '05 #12

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

Similar topics

2
by: edward hage | last post by:
Hello, I don't seem to get the following right. In the following I try to fill a MySQL table named 'mutation'. The first element is a date but MySQL sees this as 0000-00-00 instead of the date...
3
by: kiqyou_vf | last post by:
im sort of a newbie to all of this and my terminology isnt accurate but i have a couple simple questions. ive been looking around for a way to insert multiple and seperate strings (like an array or...
2
by: edward hage | last post by:
Hello, I don't seem to get the following right. In the following I try to fill a MySQL table named 'mutation'. The first element is a date but MySQL sees this as 0000-00-00 instead of the date...
2
by: Spanky | last post by:
Thanks for any help in advance! I have this order form where you add rows as you need them. The routine to add fields is working fine. I am trying to add the ability to delete rows if you...
4
by: RG | last post by:
Using VB.NET, How do I insert rows from a SQL Server table into an Access table with the same structure (and also the reverse, from Access to SQL)? I’m new to this, so here’s what I’ve...
5
by: Stephen Preston | last post by:
Trying to load an multidimensional array into a MySQL table with columns as follows, level1,level2,level3,illust,item,description,partNo,qua,price,remarks,weight ,size,mass the array first...
12
by: mantrid | last post by:
Hello Can anyone point me in the right direction for the way to read a text file a line at a time and separate the fields on that line and use them as data in an INSERT to add a record to a mysql...
3
by: Waruna | last post by:
Is there a way to block insert into mysql(5.0) using c api of mysql db.. i.e. say there is a table with 2 columns, one contains char other int then i want to insert 500 records at once,, as i...
3
by: hnmcc | last post by:
I don't know whether this belongs here or in the MySQL section. I'm trying to write a couple of values from a search page to a database: but no matter how I tweak the code, I always end up with...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

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.