Problem with inserts

[Scott White]

It took me for ever to figure this out but the 1.2 version of MySQLDb does not handle inserts properly (at least for me) when used with python 2.4 and MySQL 4.1. I tested the following code on both my laptop (WinXP) and at work(Win2000) and found that the code worked with MySQLdb version 1.0 but not 1.2. A bug fix for this would be great.

name = raw_input("Please enter a name: ")
species = raw_input("Please enter a species: ")

db = MySQLdb.connect(host="localhost", user="root", passwd="",db="test")

# create a cursor
cursor = db.cursor()

# execute SQL statement
cursor.execute("INSERT INTO animals (name, species) VALUES (%s, %s)",(name, species))

cursor.close()
db.close()

db = MySQLdb.connect(host="localhost", user="root", passwd="",db="test")
cursor = db.cursor()

# execute SQL statement
cursor.execute("SELECT * FROM animals")

# get the number of rows in the resultset
numrows = int(cursor.rowcount)

# get and display one row at a time
for x in range(0,numrows):
row = cursor.fetchone()
print row[0], "-->", row[1]

cursor.close()
db.close()

[Andy Dustman]

Here's a bug fix for you: Try executing db.commit() once in a while. I swear, people find this "bug" every week or two. Autocommit is off by default in 1.2, which is what PEP-249 specifies.

[Scott White]

Ah ok! My bad for saying it was a bug. It's just I was going through some of the tutorials on the web, e.g. at devx, and it never said anything about doing a db.commit() in the examples so that I why I assumed it was.

[Andy Dustman]

They are a little old; they assume either non-transactional tables or autocommit. The fact that you had this problem at all implies you are using InnoDB tables or another transactional table type/storage engine.

Autocommit can be turned back on with:

db.autocommit(True)

but I recommend using db.commit() instead.