CODEWITHSUNDEEP
pythonmysqlphpmyadminmysql-workbench
bassmann
bassmann

Reputation: 260

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

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:

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

Upvotes: 2

Views: 4035

Answers (2)

vizsatiz
vizsatiz

Reputation: 2183

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 !

Upvotes: 5

Jonny Kong
Jonny Kong

Reputation: 196

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

conn.commit()

Upvotes: 7

Related Questions