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?

Timo Tijhof · Accepted Answer · 2012-03-19 00:29:06Z

10

You are not committing the transaction.

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

cursor.execute(...)
conn.commit()

Reference

edited Mar 19, 2012 at 0:29

Timo Tijhof

10.4k6 gold badges38 silver badges53 bronze badges

answered Sep 7, 2009 at 4:40

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

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

2 Comments

greye Over a year ago

If it isn't in the DB, why does the second select return that value? Is this the difference between transactional and non-transactional DBs?

Camilo Díaz Repka Over a year ago

@joaoc The second select returns the value because the result of the INSERT is visible to your own session, but not visible for others until you commit it

Timothy Pratley · Accepted Answer · 2009-09-07 04:46:49Z

3

I think you need to call

db.commit()

answered Sep 7, 2009 at 4:46

Timothy Pratley

10.7k4 gold badges36 silver badges66 bronze badges

1 Comment

andrebruton Over a year ago

After every dbcursor.execute or only once at the end?

Sahan · Accepted Answer · 2012-11-02 06:02:19Z

2

The problem is you are not committing the changes. it can be done by conn.commit()

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related