CODEWITHSUNDEEP
pythonmysql
puk
puk

Reputation: 16782

Can't add rows to DB using Python's MYSQLdb module

For the life of me I can't figure out why the below module won't add new rows to my DB. I can add them using the command line interface. I can also add them by using other means (ie. writing commands to a script file and using os.system('...'), but if I use cursor.execute(), no rows are added (even though the table is created). Here is a minimal script for your viewing pleasure. Note that I am getting no errors or warnings when I run this script 

#!/usr/bin/env python

import MySQLdb

if __name__ == '__main__':
   db = MySQLdb.connect ( host="localhost", user="user", passwd="passwd", db="db" )
   cursor = db.cursor()

   cursor.execute ( 
   """
      CREATE TABLE IF NOT EXISTS god_i_really_hate_this_stupid_library
      (
         id            INT NOT NULL auto_increment,
         username      VARCHAR(32) NOT NULL UNIQUE,
         PRIMARY KEY(id)
      ) engine=innodb;
   """
   )

   cursor.execute ( 
   """
      INSERT INTO god_i_really_hate_this_stupid_library
      ( username )
      VALUES
      ( 'Booberry' );
   """
   )

   cursor.close()

Upvotes: 0

Views: 95

Answers (2)

Paolo Bergantino
Paolo Bergantino

Reputation: 488434

From the FAQ of MySQLdb:

Starting with 1.2.0, MySQLdb disables autocommit by default, as required by the DB-API standard (PEP-249). If you are using InnoDB tables or some other type of transactional table type, you'll need to do connection.commit() before closing the connection, or else none of your changes will be written to the database.

Conversely, you can also use connection.rollback() to throw away any changes you've made since the last commit.

Important note: Some SQL statements -- specifically DDL statements like CREATE TABLE -- are non-transactional, so they can't be rolled back, and they cause pending transactions to commit.

You can call db.autocommit(True) to turn autocommit on for the connection or just call db.commit() manually whenever you deem it necessary.

Upvotes: 2

mata
mata

Reputation: 69042

you need to call commit on your connection, otherwise all the changes made will be rolled back automatically.

Upvotes: 2

Related Questions