Problems inserting records

[mjlivi]

Hi all,

This seems like something really trivial, but I cant for the life of me figure it out. Basically Im finding the insertion of records from a python script to a MYSQL database really, really slow. As a test, Ive written the following code:

conn = MySQLdb.connect(host=host, user=user, passwd=password, db=Test)
cursor = conn.cursor()

for i in range(1000):
cursor.execute(Insert into table01 (dataitem) value ( + str(i) + ))

conn.close()
db.close()

table01 has two columns, an auto-incrementing primary key and dataitem which is an integer.

Running this code takes around 40 seconds which just seems absurdly slow to me. Any ideas about what might be going wrong here?

Thanks a lot.

[Andy Dustman]

I know you've said you've figured this out, but for posterity, here is the right way to do it (you did more than one dumb thing):

import MySQLdb
db = MySQLdb.connect(host=host, user=user, passwd=password, db=Test)
c = db.cursor()

make a list of 1-tuples of integers (contrived example)

data = [ (i,) for i in xrange(1000)]
c.executemany(""Insert into table01 (dataitem) value (%s)"", data)

db.commit() # if you are using transactional tables
db.close()

[Andy Dustman]

Oh, and it's VALUES,not VALUE in the SQL, even if there's only one.

[mjlivi]

Forget about it, I'm an idiot. I've fixed it up so that everything is appended in 1 Insert query and things have improved greatly.