Can't execute an INSERT statement in a Python script via MySQLdb

Question

I'm trying to execute a basic INSERT statement on a MySQL table from a Python script using MySQLdb. My table looks like this:

CREATE TABLE `testtable` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `testfield` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
)

Running this query from the MySQL command line works fine:

INSERT INTO `testtable` (`id`, `testfield`) VALUES (NULL, 'testvalue');

But when I try to execute the query from a Python script, no rows get inserted. Here's my code:

conn = MySQLdb.connect(host=db_host, port=db_port, user=db_user, passwd=db_password, db=db_database)
cursor = conn.cursor ()
cursor.execute ("INSERT INTO `testtable` (`id`, `testfield`) VALUES (NULL, 'testvalue')")
print "Number of rows inserted: %d" % cursor.rowcount
cursor.close()
conn.close()

Oddly, this will print "Number of rows inserted: 1." I can also confirm that this query increments the ID field, because when I add another row via the command line, the value of its ID is the same as if the Python script had successfully inserted its rows. However, running a SELECT query returns none of the rows from the script.

Any idea what's going wrong?

Answer 1

You either need to set conn.autocommit(), or you need to do conn.commit() - see the FAQ

Answer 2

you need to commit:

conn.commit()

http://mysql-python.sourceforge.net/FAQ.html#my-data-disappeared-or-won-t-go-away