开发者

Python MySQL: clean multiple foreign keys table

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.c开发者_运维知识库ursorMySQL.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.


To remove all records from the table:

truncate table product

To reset next ID to 1:

ALTER TABLE product AUTO_INCREMENT = 1

or

SET insert_id;
INSERT INTO product ...;

(I haven't tested this. But i should work)


If you clean a table with truncate:

TRUNCATE TABLE product

As a side effect, that will reset the auto-increment counter. But you can also reset it manually:

ALTER TABLE product AUTO_INCREMENT = 1


Thanks a lot for all input and quick feedback. It did the trick!

Here is how it looks now:

def clean_tableProduct(self):
    cleanTabeMySQLQuery = """TRUNCATE TABLE product;"""
    self.cursorMySQL.execute(cleanTabeMySQLQuery)

    setIndexMySQLQuery = """ALTER TABLE product AUTO_INCREMENT = 1;"""
    self.cursorMySQL.execute(setIndexMySQLQuery)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜