Why does PyMySQL delete query execute but does not delete records without error?

Question

Issue:

I'm trying to run a delete query using pymysql via my function delEODTmp but it’s not doing anything (i.e. deleting the records). It also does not throw back any errors, making it hard to understand why it is not working.

Troubleshooting:

1) The function retSQL works like a charm, retrieving whatever Select string I throw at it. This confirms that my user login settings are correct input to pymysql.connect. I am doing this from a client to a Mysql 5.7 server on the same network.

2) On the same client connecting to the same server with the same credentials I can:

Run the SQL DELETE FROM t_EOD_tmp; successfully from workbench
Run the SQL DELETE FROM t_EOD_tmp; successfully from phpMyAdmin

3) I tried running the py script locally on the server but the delete query still does not work. Same outcome as from client.

Questions:

Seems like destructive queries are being blocked when executed from PyMySQL. Do I need to enable something from Python, from MySQL?

There doesn't seem to be extensive documentation on PyMySQL, so maybe it would be better to develop via a different MySQL Python library. Is there a better way?

Code:

import pymysql
import pandas

    query1 = '''SELECT * FROM t_EOD limit 1000;'''

    def retSQL(query):
    conn = pymysql.connect(host=myServer, port=myPort, user=myUsr, password=myPwd, db=myDB)
    df = pd.read_sql(query,conn)
    print(df.head(5)) 
    conn.close() # closeSQL connection

def delEODTmp():
    conn = pymysql.connect(host=myServer, port=myPort, user=myUsr, password=myPwd, db=myDB)
    cur = conn.cursor()
    q1 = '''DELETE FROM t_EOD_tmp;'''
    cur.execute(q1) 
        cur.close()
        conn.close()

retSQL(query1)
delEODTmp()

Remember to `conn.commit()`, before you close it. – Ilja Everilä Jun 11 '18 at 04:56 — Ilja Everilä, Jun 11 '18 at 04:56

score 7 · Accepted Answer · answered Jun 11 '18 at 04:55

7

You will have to commit the changes before closing the connection:

conn.commit()

answered Jun 11 '18 at 04:55

Jonny Kong

197
1
6

Ok great - now good. Found this post on conn.commit which explains why its needed [link](https://stackoverflow.com/questions/2847999/why-the-need-to-commit-explicitly-when-doing-an-update) – bassmann Jun 11 '18 at 05:01

score 5 · Answer 2 · answered Jun 11 '18 at 04:56

Post-execution of your query, I think you have to call a commit to apply the changes to the DB.

    @staticmethod
    def delete(query=None):
        """
        This methods deletes the record
        :param query:
        :return:
        """
        try:
            db.query(Users).\
                filter_by(**query).\
                delete()
            db.commit()
        except Exception as e:
            db.rollback()
            raise Exception(e.message)

Hope this helps !

Why does PyMySQL delete query execute but does not delete records without error?

2 Answers2