CODEWITHSUNDEEP
pythonmysqldatabase
TIMEX
TIMEX

Reputation: 271764

Why doesn't my script work, but I can manually INSERT into mysql?

conn = MySQLdb.connect(host='db1', user='user', passwd='pass', db='db', port=3306)
cursor = conn.cursor()

count = int(sys.argv[1])
x = 0
while x < count:
    x += 1
    cursor.execute("INSERT INTO auth_group(name) VALUES(%s)", (str(x)))
    #if I change %s to 'kkkk', it doesn't work either.

    print str(x) + ' / ' + str(count)
print 'done'

However...if I go into "mysql -uuser -ppass db", it works:

mysql > INSERT INTO auth_group(name) VALUES('kkkkk');

I don't know if this could be a problem...but I was having a replication issue earlier.

I would like to INSERT 99999 rows into the database. But it's empty.

mysql> select * from auth_group;
Empty set (0.33 sec)

Upvotes: 6

Views: 4600

Answers (3)

Johannes Braunias
Johannes Braunias

Reputation: 3313

If it happens that none of your data get stored even after commiting, have a look at my answer to this issue: pymysql callproc() appears to affect subsequent selects

The basic idea there is to use executemany() instead of execute() to get the data stored. (pymysql 0.5 for Python 3.2).

A little later I found out that the actual problem is that exit() is executed too fast and because of that, the data don't get stored in the database.

So, finally the problem seems to be fixed with:

[…]
cur.execute("insert into …")
import time
time.sleep(2)

cur.close()
conn.close()
time.sleep(2)
exit()

It works!

Upvotes: 1

shylent
shylent

Reputation: 10086

You have to commit (by issuing a conn.commit()), because autocommit is off by default (and rightfully so).

Upvotes: 3

Jarret Hardie
Jarret Hardie

Reputation: 97922

If there's no particular error message (in other words, it seems to work, but the insert doesn't stick), make sure you commit() after the statement:

conn.commit()

Upvotes: 18

Related Questions