Python MySQL: clean multiple foreign keys table

[Threader Slash]

Hi Everybody,

I am working with Python MySQL, and need to clean a table in my database
that has 13328 rows.

I can not make a simple drop table, because this table is child and also
father of other child foreign-keys linked on it. If I try drop table, the
system forbidden me. The table is defined with ON UPDATE CASCADE, ON DELETE
CASCADE and InnoDB; The primary_key index to that table is defined as

    productID INT(6) NOT NULL AUTO_INCREMENT ...
    PRIMARY KEY (productID,productNO)

Therefore, I just have to clean; this will automatically restore the table
primary key index to 1 for the next input. Right?

This procedure worked fine for another table that was a father table, but
not also a father and child table. But, for this table, which is child and
father of other tables, I got stuck on it.

Here is the code - productID is my primary index key to this table:


    def clean_tableProduct(self):
        getMaxID_MySQLQuery = """SELECT MAX(productID)
        FROM product;"""

        cleanTabeMySQLQuery="""DELETE FROM product WHERE productID
<=%s;"""

        self.cursorMySQL.execute(getMaxID_MySQLQuery)

        for row in self.cursorMySQL:
            self.cursorMySQL.execute(cleanTabeMySQLQuery,(row[0],))

If I go to the MySQL console to check the processing results, it gets me:

    mysql> SELECT MIN(productID)
        FROM product;
    4615748

    mysql> SELECT MAX(productID)
        FROM product;
    4629075

If I run the same command on console to clean the table, it works:

    mysql> DELETE FROM product WHERE productID <='4629075';
    Query OK, 13328 rows affected (0.64 sec)


and shows me what I would normally expect.

However, if I go to Python function after having cleaned the table on
console, and run the program again, and clean the table to restart the
processing, it restarts the table index not with MIN:1, but instead 4629076.

Any suggestion?

All comments and suggestions are highly appreciated and welcome.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20091128/ce96198e/attachment.html>