Results 1 to 4 of 4
Nov 28, 2009, 04:34 #1
- Join Date
- Aug 2009
- 0 Post(s)
- 0 Thread(s)
MySQL Python auto-increment foreign-key clean-up
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)
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,))
mysql> SELECT MIN(productID) FROM product; 4615748 mysql> SELECT MAX(productID) FROM product; 4629075
mysql> DELETE FROM product WHERE productID <='4629075'; Query OK, 13328 rows affected (0.64 sec)
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.
All comments and suggestions are highly appreciated and welcome.