title: Efficient way to delete a large amount of records from a big table using python
body: <p>I have a large table (About 10 million rows) that I need to delete records that are &quot;older&quot; than 10 days (according to created_at column). I have a python script that I run to do this. created_at is a varchar(255) and has values like for e.g. <code>1594267202000</code></p>
  <pre><code>import mysql.connector
  import sys
  from mysql.connector import Error
  
  table = sys.argv[1]
  deleteDays = sys.argv[2]
  
  sql_select_query = &quot;&quot;&quot;SELECT COUNT(*) FROM {} WHERE created_at / 1000 &lt; UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL %s DAY))&quot;&quot;&quot;.format(table)
  sql_delete_query = &quot;&quot;&quot;DELETE FROM {} WHERE created_at / 1000 &lt; UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL %s DAY)) LIMIT 100&quot;&quot;&quot;.format(table)
  
  try:
      connection = mysql.connector.connect(host=localhost,
                                           database=myDatabase,
                                           user=admin123,
                                           password=password123)
      cursor = connection.cursor()
  
          
      #initial count of rows before deletion
      cursor.execute(sql_select_query, (deleteDays,))
      records = cursor.fetchone()[0]
  
  
      while records &gt;= 1:
          # stuck at following below line and time out happens....
          cursor.execute(sql_delete_query, (deleteDays,))
          connection.commit()
          cursor.execute(sql_select_query, (deleteDays,))
          records = cursor.fetchone()[0]
  
      #final count of rows after deletion
      cursor.execute(sql_select_query, (deleteDays,))
      records = cursor.fetchone()[0]
  
      if records == 0:
          print(&quot;\nRows deleted&quot;)
      else:
          print(&quot;\nRows NOT deleted&quot;)
  
  except mysql.connector.Error as error:
      print(&quot;Failed to delete: {}&quot;.format(error))
  finally:
      if (connection.is_connected()):
          cursor.close()
          connection.close()
          print(&quot;MySQL connection is closed&quot;)
  </code></pre>
  <p>When I run this script and it runs the DELETE QUERY however... it fails due to:</p>
  <blockquote>
  <p>Failed to delete: 1205 (HY000): Lock wait timeout exceeded; try restarting transaction</p>
  </blockquote>
  <p>I know that the <strong>innodb_lock_wait_timeout</strong> is currently set to 50 seconds and I can increase it to overcome this problem, however i'd rather not touch the timeout and.... I want to basically delete in chunks maybe? Anyone know how I can do it here using my code as example?</p>