#!/env/python
import pymysql.cursors
connection = pymysql.connect(host='localhost',
                             port=3307,
                             user='web',
                             password='secret',
                             database='movienight',
                             cursorclass=pymysql.cursors.DictCursor)

with connection:
    with connection.cursor() as cursor:
        sql = "SELECT id, title, last_watched FROM movies"
        cursor.execute(sql)
        movies = cursor.fetchall()

for movie in movies:
    # Keep the connection alive in case it takes too long
    connection.ping()

    with connection.cursor() as cursor:
        # Get latest showing for movie
        sql = "SELECT id, showtime FROM showings WHERE movie_id =%s ORDER BY showtime DESC LIMIT 1"
        cursor.execute(sql, (movie["id"],))
        last_watched = cursor.fetchone()

        if last_watched:
            # Update last watched to most recent showing
            sql = "UPDATE movies SET last_watched =%s WHERE id =%s"
            cursor.execute(sql,(last_watched["showtime"], movie["id"]))

    connection.commit()