MySQLdb python 2.5 windows XP problem

[micmast mantis]

Hey,

I'm trying to add a user into a mysql database using a python script and for some reason I cannot seem to get it working

code:
import MySQLdb

try:
db = MySQLdb.connect(host="localhost",user="dbuser",passwd="pass",db="database)
db_cursor = db.cursor()
db_cursor.execute("INSERT INTO users (username,password,Tel,gsm,fax,email,title,complete_name,update_date) VALUES ('me', 'ab86a1e1ef70dff97959067b723c5c24', 'me', 'me', 'me', 'me', 'me', 'me', '1229088563.42')")
db_cursor.execute("SELECT * FROM users")
print db_cursor.fetchall()

        db.close()
        db = MySQLdb.connect(host="localhost",user="pythontool",passwd="DDPB2htw!",db="pythontool")
        db_cursor = db.cursor()
        db_cursor.execute("SELECT * FROM users")
        print db_cursor.fetchall()

except Exception, e:
print "bugger: \n"+str(e)

Output:
D:\DDTool\version0.1\testfiles>python data.py
((45L, 'me', 'ab86a1e1ef70dff97959067b723c5c24', 'me', 'me', 'me'
, 'me', 'me', 'me', '1229088563.42'))
()

I have been working with mysqldb for some time now, but I never had this problem. I hope somebody could help me out.

Version numbers:
winxp sp3
python 2.5
mysql 5.1.30-community
MySQL-python-1.2.2.win32-py2.5.exe

Thank you in advance

[micmast mantis]

I noticed I forgot to delete the second line of the database connection (why isn't there an edit button?), but the database connections are correct

[Kyle VanderBeek]

Per PEP 249, all DB connections (including MySQLdb) are opened with automatic transactions turned off. I assume you're using a transactional table type (like InnoDB), so that is in effect.

In the first chunk of code, you insert a row, then read it back (this is internally consistent with the active transaction). However, since you never call db.commit() before db.close() the transaction is rolled back and the row discarded. That's what you're seeing when you reconnect and try to look up the row again.

You won't see this behavior with MyISAM tables and a couple other types since they don't support transactions at all.

I also recommend getting in the habit of putting an explicit cursor.execute("START TRANSACTION") before other SELECT, UPDATE, etc work. Eventually you may find that useful when you get around to nested tranasactions and other more clever stuff.