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

Question

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)

Would you mind telling us, how exactly it doesn't work?

shylent
– shylent

2009-12-08 21:09:12 +00:00
Commented Dec 8, 2009 at 21:09 — shylent
– shylent, Commented Dec 8, 2009 at 21:09

Jarret Hardie · Accepted Answer · 2009-12-08 21:10:02Z

18

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()

answered Dec 8, 2009 at 21:10

Jarret Hardie

98.4k10 gold badges135 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

shylent Over a year ago

I don't know what you mean by this comment, but I most strongly suggest NOT turning autocommit on, unless you are absolutely sure, that you want it (hint: you don't). Transactions are there for a reason. Use them.

shylent · Accepted Answer · 2009-12-08 21:12:19Z

3

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

answered Dec 8, 2009 at 21:12

shylent

10.1k6 gold badges41 silver badges56 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:25:40Z

1

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!

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered Jan 16, 2012 at 14:54

Johannes Braunias

3,4031 gold badge15 silver badges8 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related