MySQL INSERT data does not get stored in proper db, only temporary?

Question

I'm having trouble with MySQL or Python and can't seem to isolate the problem. INSERTs only seem to last the run of the script and are not stored in the database.

I have this script:

import MySQLdb
db = MySQLdb.connect(host="localhost", user="user", passwd="password", db="example")
dbcursor = db.cursor()

dbcursor.execute("select * from tablename")
temp = dbcursor.fetchall()
print 'before: '+str(temp)

dbcursor.execute('INSERT INTO tablename (data1, data2, data3) VALUES ("1", "a", "b")')

dbcursor.execute("select * from tablename")
temp = dbcursor.fetchall()
print 'after: '+str(temp)

The first time I run it I get the expected output:

>>> 
before: ()
after: ((1L, 'a', 'b'),)

The problem is that if I run it again, the before comes out empty when it should already have the entry in it and the after doesn't break (data 1 is primary key).

>>> 
before: ()
after: ((1L, 'a', 'b'),)
>>> 
before: ()
after: ((1L, 'a', 'b'),)
>>> 
before: ()
after: ((1L, 'a', 'b'),)

If I try running the insert command twice in the same script it will break ("Duplicate entry for PRIMARY KEY")

Any idea what might be happening here?

Answer 1

You are not committing the transaction.

conn = MySQLdb.connect (host = "localhost",
                        user = "testuser",
                        passwd = "testpass",
                        db = "test")
cursor.execute(...)
conn.commit()

Reference

Answer 2

I think you need to call

db.commit()