CODEWITHSUNDEEP
pythonmysql
Cmag
Cmag

Reputation: 15750

python mysqldb, for loop not deleting records

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

Upvotes: 0

Views: 550

Answers (2)

Cmag
Cmag

Reputation: 15750

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

Upvotes: 2

yurisich
yurisich

Reputation: 7119

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

Upvotes: 1

Related Questions