I have a python code and a table that add a row and delete it after a few minutes. I need to see how many row have been added from the beginning until now.

Note : I have a primary key in my table too. Note : COUNT(*) only returns number of rows

CodePudding user response：

Queries in MySQL only request the current status of the database, so you cannot use a simple query to count inserted and deleted rows. The usual solution for this problem is by implementing an archiving mechanism instead of a simple deletion.

So you will have to add a new column in the table to store the fact that the row has been archived, and each time your application has to delete a row it will update this column instead. Then a simple count(*) will count all inserted and archived rows.

Here are a few ideas for the new column (from the simplest to the most useful for future evolution):

• Column name is deleted and it can take 2 values: 0 or 1 (boolean)
• Column name is status and it can take 2 values: "active" or "archived"
• Column name is deleted_at so it is null if the row is still valid, and it contains the datetime of deletion if the row has been archived.

In addition, this solution will empower your application with the "unarchive" ability.