Python+MySQLdb weird problem

Question

I have a problem using the MySQLdb library. I'm trying to insert values into my table but MySQL isn't showing them. What's more strange is the primary key is changing when I manually insert values using command prompt.

To show you an example:

'786', '2011-02-16 14:52:38', NULL
'787', '2011-02-16 14:52:52', NULL
'792', '2011-02-16 14:53:25', NULL

I manually insert some value at 786 and 787 (primary key), then I run my python script, stopping it after I got 4 values. I type in 'SELECT * from table' and I see no changes. Then I inset another value (manually) and it shows a new primary key '792'. It seems like the python script is deleting things...

Here's my python code:

try:
 conn = MySQLdb.connect(host = "127.0.0.1", 
 user = "root", 
 passwd = "eBao1234", 
 db = "test")
 cursor = conn.cursor()
 print 'data base connected'
 try:
 while 1:
 localtime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
 i = i + 1
 aRead = ser.readline()
 aSplit = aRead.split(",")
 if aSplit[0] == "t":
 print str(i) + ", " + localtime + ", " + aSplit[1]
 tableName = "sampdb"
 dbquery = "INSERT INTO %s (timestamp, tempReading) VALUES (NOW(), %s);" % (tableName, aSplit[1])
 cursor.execute(dbquery)
 except KeyboardInterrupt:
 ser.close()
 #csvResults.close()
 #cursor.close()
 #conn.close()
 print "\nInterrupted."
 raw_input("Press enter to exit.")
 except Exception,e:
 print str(e)
 print 'error connecting to database'
 cursor.close()
 conn.close()

Answer 1

Turn on autocommit or commit your changes, see: My data disappeared!

• Ocaso Protal: Normally right after you create your Connection (MySQLdb.connect). And it is conn.autocommit(True)

Answer 2

If you (also) experience a problem with executing multiple INSERT statements not getting stored to the database, have a look at my answer to pymysql callproc() appears to affect subsequent selects

However, a little later I found out that my problem was that the exit() was exectued too fast. So, I added a time.sleep(3) before calling exit() - it worked!

import time
time.sleep(3)
cur.close()
conn.close()
time.sleep(3)
exit()

Update: Again, the story has not yet ended ... I experienced a similar error later in my script when selecting from the database and not retrieving any results. This doesn't seem related to the mentioned time.sleep(x) behaviour.

Answer 3

When you execute statements that modify the database, you have to commit, as has already been answered. If you're only using SELECT, the you don't need to commit. Be careful with autocommit, because if someone else is working on the database and you have autocommit enabled, his/hers changes will be committed when you execute your script, even if he/she isn't finished or doesn't want to commit.