Why isn't the 'insert' function adding rows using MySQLdb?

Question

I'm trying to figure out how to use the MySQLdb library in Python (I am novice at best for both of them).

I'm following the code here, specifically:

cursor = conn.cursor ()
cursor.execute ("DROP TABLE IF EXISTS animal")
cursor.execute ("""
   CREATE TABLE animal
   (
     name     CHAR(40),
     category CHAR(40)
   )
 """)
cursor.execute ("""
   INSERT INTO animal (name, category)
   VALUES
     ('snake', 'reptile'),
     ('frog', 'amphibian'),
     ('tuna', 'fish'),
     ('racoon', 'mammal')
 """)
print "Number of rows inserted: %d" % cursor.rowcount
cursor.close ()
conn.close ()

I can change this code to create or drop tables, but I can't get it to actually commit the INSERT. It returns the row.count value as expected (even when I change the value in the table, it changes to what I expect it to be).

Every time I look into the database with PHPMyAdmin there are no inserts made. How do I commit the INSERT to the database?

dani herrera · Accepted Answer · 2012-02-07 08:32:23Z

18

You forget commit data changes, autocommit is disabled by default:

   cursor.close ()
   conn.commit ()
   conn.close ()

Quoting Writing MySQL Scripts with Python DB-API documentation:

"The connection object commit() method commits any outstanding changes in the current transaction to make them permanent in the database. In DB-API, connections begin with autocommit mode disabled, so you must call commit() before disconnecting or changes may be lost."

answered Feb 7, 2012 at 8:32

dani herrera

52k11 gold badges133 silver badges188 bronze badges

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

2 Comments

Jay Gattuso Over a year ago

Aw man. Thank you. Thats a lesson I won't forget again! Of course, that works. (And I had all the clues in my question! sigh.)

dani herrera Over a year ago

@JayGattuso, you are right, your question contains the answer: "but I can't get it to actually commit the INSERT"

Collectives™ on Stack Overflow

Why isn't the 'insert' function adding rows using MySQLdb?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related