Delete older data from MySQL database using Python

Question

I want to delete records older than 7 days automatically, but it doesn't work. Message is showing that older data is deleted, but actually it did not delete the data.

My code is:

try:
    db= MySQLdb.connect("localhost","root","","testDB")
    cursor=db.cursor()
    sql="DELETE FROM CALLRECORD WHERE DATE< DATE_SUB(NOW(), INTERVAL 7 DAY)"
    try:
        cursor.execute(sql)
        db.commit()
        print "Deleted Older Data from database"

    except:
        db.rollback()
        print "Cann't delete older data"
    db.close()

except:
    print "localserver not connected"

Hey @Akash Nil did you try changing the operator? Did it work? — Misa Lazovic
– Misa Lazovic, Commented Feb 2, 2016 at 12:12
as my stored date format in callrecord is dd/mm/yy so I tried sql="DELETE FROM CALLRECORD WHERE DATE< GET_FORMAT( DATE_SUB(NOW(), INTERVAL 7 DAY),'EUR')" but it shows error — Akash Nil
– Akash Nil, Commented Feb 4, 2016 at 12:26

score 2 · Accepted Answer · 2016-02-14 18:37:33Z

2

I think you've made a mistake in your query, you are deleting records which inserted in last 7 days. Use below query instead of yours:

DELETE FROM CALLRECORD
WHERE DATE < DATE_SUB(NOW(), INTERVAL 7 DAY)

edited Feb 14, 2016 at 18:37

answered Feb 2, 2016 at 11:22

user5069269

Sign up to request clarification or add additional context in comments.

4 Comments

Akash Nil Over a year ago

my stored date format is dd/mm/yy when i am using your code it deleting current record. i am using linux for database. i tested WHERE DATE < DATE_SUB(NOW(date +'%d-%m-%y'), INTERVAL 7 DAY) but the result is same. if i am using > sign it did not delete data

user5069269 Over a year ago

Try UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))

Akash Nil Over a year ago

in my database data coming every minutes your code deleted all the record

Akash Nil Over a year ago

date entered my callrecord by python command date=datetime.now().strftime("%d/%m/%y"). then this date related information will stored into callrecord after that it will delete older data suppose stored date is 21/01/16. and it will delete all the record before 14/01/16.

Misa Lazovic · Accepted Answer · 2016-02-02 11:23:48Z

1

You are using > instead of <, so you're deleting new records instead of old ones. You need to delete records with date LESS (not greater) than the date that was seven days ago.

answered Feb 2, 2016 at 11:23

Misa Lazovic

2,83110 gold badges34 silver badges39 bronze badges

Collectives™ on Stack Overflow

Delete older data from MySQL database using Python

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related