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

Question

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()

have you tried db.commit() before cursor.close()?

Paolo Bergantino
– Paolo Bergantino

2012-05-28 21:07:03 +00:00
Commented May 28, 2012 at 21:07 — Paolo Bergantino
– Paolo Bergantino, Commented May 28, 2012 at 21:07
@PaoloBergantino no I have not

puk
– puk

2012-05-28 21:09:26 +00:00
Commented May 28, 2012 at 21:09 — puk
– puk, Commented May 28, 2012 at 21:09

mata · Accepted Answer · 2012-05-28 21:07:01Z

2

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

answered May 28, 2012 at 21:07

mata

69.3k10 gold badges168 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

puk Over a year ago

I knew it was something trivial like that. Let me give it a quick try. Do I have to riddle my code commits, or just one at the end?

mata Over a year ago

You have to call commit every time you want some change commited to the database. If you want every change commited automatically, the you can execute SET autocommit = 1 or use this.

ypercubeᵀᴹ Over a year ago

"all the changes made will be rolled back" Not all the changes will be rolled back. The rollback will be up to last commit (and because some statements, especially DDL like CREATE TABLE are implicitedly committed and cannot be rolled back, anything before such a statement will not be rolled back, too.)

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 28, 2012 at 21:11

Paolo Bergantino

490k83 gold badges523 silver badges437 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related