INSERT doesn't insert

[Johannes Braunias]

I could run a script successfully, and all of a sudden, the insert statements don't work any longer. Any combination with commits doesn't help.

Does anybody experience the same problem?

[Johannes Braunias]

The file (deletes and) creates the database mydb with around 20 tables.

It fills the lookup tables, but they remain empty.

Run the script with no argument.

There is some more code in the script which require an input file.

To test the database, the scripts exits after filling the lookup tables.

[Johannes Braunias]

I found a solution for me (posted also on  http://stackoverflow.com/questions/8218870/pymysql-callproc-appears-to-affect-subsequent-selects/8878775#8878775)

I switched from execute() to executemany().

The working code snippet ist now:

import pymysql
conn = pymysql.connect(host='localhost', user='root', passwd='', db='mydb', charset='utf8')
conn.autocommit(True)
cur = conn.cursor()

# CREATE tables (SQL statements generated by MySQL workbench, and exported with Menu -> Database -> Forward Engineer)
cur.execute("""
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS `mydb` ;
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;
# […]

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

""")
conn.commit()

cur.execute("set autocommit = 1;")
conn.commit()

# Fill lookup tables:

cur.executemany("insert into mydb.number(tagname,name,shortform) values (%s, %s, %s)", [('ЕД','singular','sg'), ('МН','plural','p')] )
cur.executemany("insert into mydb.person(tagname,name,shortform) values (%s, %s, %s)", [('1-Л','first','1st'), ('2-Л','second','2nd'), ('3-Л','third','3rd')] )
cur.executemany("insert into mydb.pos(tagname,name,shortform) values (%s, %s, %s)", [('S','noun','s'), ('A','adjective','a'), ('ADV','adverb','adv'), ('NUM','numeral','num'), ('PR','preposition','pr'), ('COM','composite','com'), ('CONJ','conjunction','conj'), ('PART','particle','part'), ('P','word-clause','p'), ('INTJ','interjection','intj'), ('NID','foreign-named-entity','nid'), ('V','verb','v')] )
#[…]

[Johannes Braunias]

Unfortunately, it is only a slightly better solution.

Only the first two or three insert statements get executed … any suggestions?

[Johannes Braunias]

So, finally the problem seems to be fixed:

Appearantly, to execute all the SQL statements in the script, the database connection must persist until the data arrived in the database.

To ensure this, I added a 

import time

time.sleep(2)

statement before

exit()

It works!

[Jay Summet]

Interesting....I would have thought that the executemany call was
blocking, I guess it is not?

Can you test something for my curiosity?

If you use a

db.commit()

call instead of the sleep, does that also ensure that all data is
written? (because if it does not, that's a problem...)

Jay

[Johannes Braunias]

Hi,

I had turned autocommit on …

[Jay Summet]

It sounds like autocommit was not actually auto-commiting. I was
wondering if a manual commit would work better.
Jay

On 01/17/2012 08:06 AM, Johannes Braunias wrote:
> Hi,

> I had turned autocommit on …

[Johannes Braunias]

Hi Jay, 

manual commit did not help, either. I just tried it.

Seems like mysql doesn't proceed with its work as soon as it detects a cut connection from the origin of the command.

[Johannes Braunias]

It is not yet the end of the story, though.

A subsequent 

cur.execute('SELECT * FROM person;')

row = cur.fetchone()

while row:

    f_out.write("ID=%d, Name=%s", (row[0], row[1]))

    row = cur.fetchone()

time.sleep(2)

exit()

doesn't return anything. 

Not even if I replace the select statement by "show tables;" or things alike.

[Johannes Braunias]

I tried to use MySQLdb instead now, but this is throwing another error: 

"Commands out of sync; you can't run this command now"

http://forums.mysql.com/read.php?168,507863,507863 says that it might be related to the versions of MySQL >= 5.5.16?

I'm using 5.5.20

Anybody having similar problems?

[chop]

I have a very similar problem.  I started another topic because I didn't see yours until just now.  My issue is written up at https://groups.google.com/forum/?fromgroups=#!topic/pymysql-users/ZTe1yTpZgYA

When I changed cur.execute to cur.executemany I got a different error:  This was the cur.executemany error

Traceback (most recent call last):

  File "C:/Users/ac33g1r1/Documents/BD_Scripts/test plist script.py", line 30, in <module>

    [plist[sid], lastQ[0]] )

  File "C:\Python33\pymysql\cursors.py", line 132, in executemany

    self.rowcount = sum([ self.execute(query, arg) for arg in args ])

  File "C:\Python33\pymysql\cursors.py", line 132, in <listcomp>

    self.rowcount = sum([ self.execute(query, arg) for arg in args ])

  File "C:\Python33\pymysql\cursors.py", line 105, in execute

    query = query % escaped_args

TypeError: not enough arguments for format string

[Johannes Braunias]

Dear "chop",

unfortunately I don't remember exactly what I did about this problem to solve it :-/ 

If I remember correctly, I moved the single statements of the SQL script into the python script and did the looping etc. there.

Hope this helps!

Johannes