For a while now, I've been messing around with a Python + MySQL web thingy to keep track of my pedometer data. (Yes, I could use Excel. It would probably be easier, and Excel certainly has fancier data-analysis tools than anything I could hope to create for myself. But half the reason behind doing this is to learn how to write a model-view-controller web application. And it's fun.)
This morning, I patched in some code to save new records in the database: fill out the form, hit the Save button, and - presto! - new record.
But it didn't work. No errors, no warnings, no messages of any kind - but also no new record.
Executing the REPLACE statement manually - by pasting it into a MySQL command-line session - worked just fine. But executing it from a Python script refused to create the new record.
I thought maybe the problem was my table design. Maybe using a date column as the primary key isn't allowed, or maybe there are bugs in MySQL / MySQLdb. So I added a numeric column and made it the primary key. No improvement.
I changed the table definition from the InnoDB engine to the MyISAM engine, and - surprise! - suddenly it worked. But why won't it work with InnoDB?
A bit of searching turned up somebody out in the world who had the same problem: his code seemed to work, but never actually modified the database. The very first reply to his cry for help:
InnoDB tables are transactional. Are you committing your transactions
after inserts and deletes?
Um. I knew that.
One commit statement later, I can save records into my database.