Tags: cant, codeconnection, connect, insert, localhost, mysql, mysqldb, programming, python, root, row, rows, table, user

Can't seem to insert rows into a MySQL table

On Programmer » Python

11,548 words with 9 Comments; publish: Wed, 30 Apr 2008 15:43:00 GMT; (20062.50, « »)

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.

All Comments

Leave a comment...

  • 9 Comments
    • 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

      #1; Wed, 30 Apr 2008 15:44:00 GMT
    • 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

      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.

      #2; Wed, 30 Apr 2008 15:45:00 GMT
    • On Sat, 12 Mar 2005 18:24:10 GMT, grumfish <nobody.python.todaysummary.com.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>

      --

      > ========================================

      ====================== <

      > wlfraed.python.todaysummary.com.ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <

      > wulfraed.python.todaysummary.com.dm.net | Bestiaria Support Staff <

      > ========================================

      ====================== <

      > Home Page: <http://www.dm.net/~wulfraed/> <

      > Overflow Page: <http://wlfraed.home.netcom.com/> <

      #3; Wed, 30 Apr 2008 15:46:00 GMT
    • 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

      #4; Wed, 30 Apr 2008 15:47:00 GMT
    • 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

      #5; Wed, 30 Apr 2008 15:48:00 GMT
    • 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" <nobody.python.todaysummary.com.nowhere.com>

      Newsgroups: comp.lang.python

      To: <python-list.python.todaysummary.com.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

      #6; Wed, 30 Apr 2008 15:49:00 GMT
    • In <mailman.410.1110836641.1799.python-list.python.todaysummary.com.python.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

      #7; Wed, 30 Apr 2008 15:50:00 GMT
    • 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_666.python.todaysummary.com.gmx.net>

      Newsgroups: comp.lang.python

      To: <python-list.python.todaysummary.com.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.python-list.python.todaysummary.com.python.org>, Anthra Norell

      > wrote:

      >

      ('%s',

      > 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

      #8; Wed, 30 Apr 2008 15:52:00 GMT
    • 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().

      Norell

      around your

      VALUES ('%s',

      Don't do this.

      #9; Wed, 30 Apr 2008 15:52:00 GMT