DB - Log guesses to sqlite db

1 files changed, 52 insertions, 6 deletions

diff --git a/src/main.py b/src/main.py
index a55662a..a3e6b11 100644
--- a/src/main.py
+++ b/src/main.py
@@ -1,7 +1,8 @@
 import os
 import random
-from pprint import pprint
 import json
+import sqlite3
+from contextlib import closing # with close sqlite
 
 import flask
 from PIL import Image as PIL_Image # Image file exif data for GPS coordinate extraction
@@ -20,18 +21,18 @@ def decimal_coords(coords, ref):
         decimal_degrees = -decimal_degrees
     return decimal_degrees
 
-def image_coordinates(image_path: os.PathLike) -> (float, float):
-    image = PIL_Image.open(image_path)  # Load the image from the specified path
-    exif_data = image._getexif()    # Extract EXIF metadata from the image
-
+def get_exif(image_path: os.PathLike) -> dict:
     # Extract EXIF metadata as a dictionary with readable tags
+    image = PIL_Image.open(image_path)  # Load the image from the specified path
     exif = {
         PIL_ExifTags.TAGS[k]: v
         for k, v in image._getexif().items()
         if k in PIL_ExifTags.TAGS
     }
+    return exif
 
 
+def image_coordinates(exif: dict) -> (float, float):
     # Extract raw GPS data for latitude (north) and longitude (east)
     north = exif['GPSInfo'][2]  # Latitude data in degrees, minutes, and seconds
     east = exif['GPSInfo'][4]   # Longitude data in degrees, minutes, and seconds
@@ -53,16 +54,61 @@ def get_random_image():
 @app.route("/guess.json", methods=['POST'])
 def guess():
     data = json.loads(flask.request.data)
+    exif = get_exif(os.path.join(pic_root_path, data['image_name']))
 
-    correct_coordinates = image_coordinates(os.path.join(pic_root_path, data['image_name']))
+    correct_coordinates = image_coordinates(exif)
     guess_coordinates   = (data['coordinates']['lat'], data['coordinates']['lng'])
 
     distance = geopy.distance.geodesic(correct_coordinates, guess_coordinates)
     distance_meters = round(distance.meters)
 
+    image_unique_id = exif['ImageUniqueID']
+    with closing(sqlite3.connect("guess_where.db")) as connection:
+        with closing(connection.cursor()) as cursor:
+            cursor.execute("""INSERT INTO guesses(
+                                                picture_name,
+                                                image_unique_id,
+                                                correct_coordinates,
+                                                guess_coordinates,
+                                                distance_meters
+                                                )
+                           VALUES (?, ?, ?, ?, ?)
+                           """, (
+                                                data['image_name'],
+                                                image_unique_id,
+                                                f"{correct_coordinates[0]}, {correct_coordinates[1]}",
+                                                f"{data['coordinates']['lat']}, {data['coordinates']['lng']}",
+                                                distance_meters
+                               ))
+            connection.commit()
+
     return {'correct_coordinates': correct_coordinates, 'distance_meters': distance_meters}
 
 @app.route("/")
 def game():
     return  flask.render_template('game.html')
 
+
+with closing(sqlite3.connect("guess_where.db")) as connection:
+    with closing(connection.cursor()) as cursor:
+        rows = cursor.execute("""
+                              CREATE TABLE IF NOT EXISTS guesses (
+                                  picture_name        TEXT,
+                                  image_unique_id     TEXT,
+                                  correct_coordinates TEXT,
+                                  guess_coordinates   TEXT,
+                                  distance_meters     INT,
+                                  timestamp           DATETIME DEFAULT CURRENT_TIMESTAMP
+                                  )
+                              """).fetchall()
+
+
+
+# Check if all images have GPS coordinates on application start
+for pic in pics:
+    img_path = os.path.join(pic_root_path, pic)
+    try:
+        correct_coordinates = image_coordinates(img_path)
+    except Exception as ex:
+        print(f'Error loading GPS coordinates for: {img_path}')
+