MySQL rows delete when program exits

Question

I'm using Python and MySQLdb to add rows to my database. It seems that when my script exits, the rows get deleted. My last lines before the script exits do a "select *" on the table, which shows my one row. When I re-run the script, the first lines (after opening the connection) do the same "select *" and return zero results. I'm really at a loss here. I've been working for about 2 hours on this, and can't understand what could be accessing my database.

Also, between running the scripts, I run the "select *" manually from a terminal with zero results.

If I manually add a row from the terminal, it seems to last.

The query to insert the row:

cursor.execute("INSERT INTO sessions(username, id, ip) VALUES (%s, %s, %s)", (username, SessionID, IP]))

The query I use to check the data:

cursor.execute("select * from sessions")
print cursor.fetchall()

This shows the row before the program exits, then shows nothing when the program is run again.

Thanks in advance for all the help.

My guess is you need to `commit` your changes to the database. — Keith Randall, Aug 02 '12 at 20:54
Definitely sounds like the transaction isn't getting committed. — Mike Christensen, Aug 02 '12 at 20:55
Chris, transactions make sure all operations are successful or none at all. So if for some reason you are using transactions, and it remains open when the script closes, all your changes are rolled back, basically nothing is added. — WebDevNewbie, Aug 02 '12 at 20:57

score 5 · Accepted Answer · edited Mar 14 '21 at 19:06

5

Looks like you need to connection.commit() your changes after you execute the query (replace connection with your DB connection variable).

http://docs.python.org/library/sqlite3.html

Connection.commit(): This method commits the current transaction. If you don’t call this method, anything you did since the last call to commit() is not visible from other database connections. If you wonder why you don’t see the data you’ve written to the database, please check you didn’t forget to call this method.

edited Mar 14 '21 at 19:06

Barmar

741,623
53
500
612

answered Aug 02 '12 at 21:03

WebDevNewbie

1,833
1
13
17

You were right. I found this on http://mysql-python.sourceforge.net/FAQ.html#my-data-disappeared-or-won-t-go-away: "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." So even though MySQL was set to auto-commit, I still needed to commit my changes because of MySQLdb. Thanks so much! – Chris Aug 02 '12 at 21:16
It should be `connection.commit()`, not `cursor.commit()`. – Barmar Oct 15 '20 at 06:50

score 0 · Answer 2 · edited May 23 '17 at 12:23

0

Check this other question: Python MySQLdb update query fails

You can find some examples on how to commit, how to connect using autocommit, etc.

edited May 23 '17 at 12:23

Community

1
1

answered Aug 02 '12 at 21:05

gepatino

160
2

MySQL rows delete when program exits

2 Answers2

Linked

Related