python mysqldb, for loop not deleting records

Question

Somewhere in here lies a problem. http://paste.pocoo.org/show/528559/

Somewhere between lines 32 and 37. As you can see the DELETE FROM is inside a for loop.

Running the script just makes the program go through the loop and exit, without actually removing any records.

Any help would be greatly appreciated! Thanks!

#!/usr/bin/env python
# encoding: utf-8

import os, os.path, MySQLdb, pprint, string

class MySQLclass(object):
    """Learning Classes"""
    def __init__(self, db):
        self.db=db
        self.cursor = self.db.cursor()

    def sversion(self):
        self.cursor.execute ("SELECT VERSION()")
        row = self.cursor.fetchone ()
        server_version =  "server version:", row[0]
        return server_version

    def getRows(self, tbl):
        """ Returns the content of the table tbl """
        statmt="select * from %s" % tbl
        self.cursor.execute(statmt)
        rows=list(self.cursor.fetchall())
        return rows

    def getEmailRows(self, tbl):
        """ Returns the content of the table tbl """
        statmt="select email from %s" % tbl
        self.cursor.execute(statmt)
        rows=list(self.cursor.fetchall())
        return rows

    def removeRow(self,tbl,record):
        """ Remove specific record """
        print "Removing %s from table %s" %(record,tbl)
        print tbl

        self.cursor.execute ("""DELETE FROM maillist_frogs where email LIKE %s""", (record,))


def main():

    #####connections removed

    sql_frogs = MySQLclass(conn_frogs)
    sql_mailgust = MySQLclass(conn_mailgust)

    frogs_emails = sql_frogs.getEmailRows ("emails")
    frogs_systemcatch = sql_frogs.getEmailRows ("systemcatch")
    mailgust_emails = sql_mailgust.getEmailRows ("maillist_frogs")


    aa = set(mailgust_emails)
    remove = aa.intersection(frogs_emails)
    remove = remove.union(aa.intersection(frogs_systemcatch))

    for x in remove:
        x= x[0]
        remove_mailgust = sql_mailgust.removeRow ("maillist_frogs",x)

    conn_frogs.close ()
    conn_mailgust.close ()

if __name__ == '__main__':
    main()

paste here : paste.pocoo.org/show/528559 , look at lines 32-37, for loop is at 56-58 THANKS! — Cmag
– Cmag, Commented Jan 1, 2012 at 20:54
Have you tried to print the query and try in the mysql shell ? If you are passing a string (or a letter) to the query, the like clausole should be LIKE '%s' — user1092803
– user1092803, Commented Jan 1, 2012 at 22:09
Just add a self.db.commit() in the RemoveRow function after the execute() statement — RedBaron
– RedBaron, Commented Jan 2, 2012 at 6:07

Cmag · Accepted Answer · 2012-01-02 19:11:38Z

2

the problem is python-msyqldb specific.:

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.

therefore, after the DELETE, you must self.db.commit

answered Jan 2, 2012 at 19:11

Cmag

15.9k26 gold badges97 silver badges147 bronze badges

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

Comments

yurisich · Accepted Answer · 2012-01-02 05:29:40Z

1

The removeRow() method does not return a value, yet remove_mailgust is expecting to receive this non-existent value.

Also, your removeRow() class method is statically fixed to only search the table maillist_frogs in its query. You should probably set the table name to accept the second parameter of the method, tbl.

Finally, your removeRow() method is comparing the value of a record (presumably, an id) using LIKE, which is typically used for more promiscuous string comparison. Is email your primary key in this table? If so, I would suggest changing it to:

self.cursor.execute ("""DELETE FROM %s where email_id = %s""", (tbl, record,))

answered Jan 2, 2012 at 5:29

yurisich

7,1698 gold badges46 silver badges69 bronze badges

Collectives™ on Stack Overflow

python mysqldb, for loop not deleting records

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related