title: Deleting some rows in a MySQL table
body: <p>I have a MySQL db which includes 2 interlinked tables called 'files' and 'path'. The 'files' table has several hundred rows and I would like to delete rows if the corresponding file has been deleted from the file system, ie 90+%.</p>
  
  <p>I can list the files which are no longer present with the following Python code: </p>
  
  <pre><code>import pymysql
  import pymysql.cursors
  import os.path
  
  con = pymysql.connect(host='localhost',
        db='MyVideos116',
        user='kodi',
        password='****',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor)
  
  with con:  
    cur = con.cursor()
    cur.execute("SELECT f.*, p.* FROM files f INNER JOIN path p ON f.idPath = p.idPath")
    rows = cur.fetchall()
  
    for row in rows:
       fileExists = os.path.isfile(row["strPath"] + row["strFilename"])
       if not fileExists:
          print(row["idFile"], row["strPath"], row["strFilename"])
  </code></pre>
  
  <p>I have tried to use the os.path.isfile() phrase as part in a WHERE clause such as:</p>
  
  <pre><code>cur.execute("SELECT f.*, p.* FROM files f INNER JOIN path p ON p.idPath = f.idPath WHERE os.path.isfile(p.strPath+f.strFilename")
  </code></pre>
  
  <p>but that does not seem to be permissible. In pseudo code what I want to do is:</p>
  
  <pre><code>for each row in files table
     if file does not exist then
        delete row
     end if
  next row
  </code></pre>
  
  <p>Looks pretty simple, how do I do it?</p>