34 lines
1.1 KiB
Python
34 lines
1.1 KiB
Python
|
#!/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()
|
||
|
|
|
||
|
|
|