Can't seem to insert rows into a MySQL table

[grumfish]

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

connection = MySQLdb.connect (host="localhos t", user="root", passwd="pw",
db="japanese")
cursor = connection.curs or()
cursor.execute( "INSERT INTO edict (kanji, kana, meaning) VALUES (%s, %s,
%s)", ("a", "b", "c") )
connection.clos e()

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.

[Patrick Useldinger]

grumfish wrote:

connection = MySQLdb.connect (host="localhos t", user="root", passwd="pw",
db="japanese")
cursor = connection.curs or()
cursor.execute( "INSERT INTO edict (kanji, kana, meaning) VALUES (%s, %s,
%s)", ("a", "b", "c") )
connection.clos e()

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

-pu

[Stephen Prinster]

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.

[Dennis Lee Bieber]

On Sat, 12 Mar 2005 18:24:10 GMT, grumfish <no****@nowhere .com>
declaimed the following in comp.lang.pytho n:

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

connection = MySQLdb.connect (host="localhos t", user="root", passwd="pw",
db="japanese")
cursor = connection.curs or()
cursor.execute( "INSERT INTO edict (kanji, kana, meaning) VALUES (%s, %s,
%s)", ("a", "b", "c") )
connection.clos e()
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="tr3th4m k4r",
db="test",
host="localhost ")
cr = cn.cursor()

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

print res

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

res = cr.execute("sel ect * 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>pytho n 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.netc om.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
=============== =============== =============== =============== == <
Home Page: <http://www.dm.net/~wulfraed/> <
Overflow Page: <http://wlfraed.home.ne tcom.com/> <

[deelan]

grumfish wrote:

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

connection = MySQLdb.connect (host="localhos t", user="root", passwd="pw",
db="japanese")
cursor = connection.curs or()
cursor.execute( "INSERT INTO edict (kanji, kana, meaning) VALUES (%s, %s,
%s)", ("a", "b", "c") )
connection.clos e()

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

Try this, instead:

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

connection.auto commit(True) # <--- note this
cursor = connection.curs or()

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

[grumfish]

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?

[deelan]

deelan wrote:

which version of MySQLdb are you running? versions
1.1.6 and below gained a connection.auto commit) 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

[Andy Dustman]

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_fi le 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.

[Anthra Norell]

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.pytho n
To: <py*********@py thon.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="localhos t", user="root", passwd="pw",
db="japanese")
cursor = connection.curs or()
cursor.execute( "INSERT INTO edict (kanji, kana, meaning) VALUES (%s, %s,
%s)", ("a", "b", "c") )
connection.clos e()

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

[Marc 'BlackJack' Rintsch]

In <ma************ *************** **********@pyth on.org>, 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