move connection to context manager (fix deadlock?)

Postgres doesn't like us altering a table while a transaction is open,
and it seems that by not closing those connections in the update
scripts, we provoke a deadlock in the CI. Not sure what changed now
(because it worked previously), but I hope this fixes it by properly
closing connections as soon as they are no longer needed.

3 files changed, 163 insertions, 162 deletions

diff --git a/fietsboek/updater/scripts/upd_20230103_lu8w3rwlz4ddcpms.py b/fietsboek/updater/scripts/upd_20230103_lu8w3rwlz4ddcpms.py
index 4362c2d..10c42d0 100644
--- a/fietsboek/updater/scripts/upd_20230103_lu8w3rwlz4ddcpms.py
+++ b/fietsboek/updater/scripts/upd_20230103_lu8w3rwlz4ddcpms.py
@@ -29,42 +29,42 @@ alembic_revision = 'c939800af428'
 class Up(UpdateScript):
     def pre_alembic(self, config):
         engine = create_engine(config["sqlalchemy.url"])
-        connection = engine.connect()
         data_dir = Path(config["fietsboek.data_dir"])
 
-        sql = (
-            "SELECT tracks.id, tracks.title, tracks.description, tracks.date_raw, "
-            "tracks.date_tz, users.name "
-            "FROM tracks, users "
-            "WHERE tracks.owner_id = users.id;"
-        )
-        for row in connection.execute(text(sql)):
-            track_id, title, description, date_raw, date_tz, author_name = row
-            if isinstance(date_raw, str):
-                date_raw = datetime.datetime.strptime(date_raw, "%Y-%m-%d %H:%M:%S.%f")
-            if date_tz is None:
-                timezone = datetime.timezone.utc
-            else:
-                timezone = datetime.timezone(datetime.timedelta(minutes=date_tz))
-            date = date_raw.replace(tzinfo=timezone)
-
-            self.tell(f"Embedding metadata for track {track_id}")
-            track_dir = data_dir / "tracks" / str(track_id)
-            gpx_path = track_dir / "track.gpx.br"
-
-            raw_gpx = brotli.decompress(gpx_path.read_bytes())
-            gpx = gpxpy.parse(raw_gpx)
-
-            for track in gpx.tracks:
-                track.name = None
-                track.description = None
-
-            gpx.author_name = author_name
-            gpx.name = title
-            gpx.description = description
-            gpx.time = date
-
-            gpx_path.write_bytes(brotli.compress(gpx.to_xml().encode("utf-8"), quality=4))
+        with engine.connect() as connection:
+            sql = (
+                "SELECT tracks.id, tracks.title, tracks.description, tracks.date_raw, "
+                "tracks.date_tz, users.name "
+                "FROM tracks, users "
+                "WHERE tracks.owner_id = users.id;"
+            )
+            for row in connection.execute(text(sql)):
+                track_id, title, description, date_raw, date_tz, author_name = row
+                if isinstance(date_raw, str):
+                    date_raw = datetime.datetime.strptime(date_raw, "%Y-%m-%d %H:%M:%S.%f")
+                if date_tz is None:
+                    timezone = datetime.timezone.utc
+                else:
+                    timezone = datetime.timezone(datetime.timedelta(minutes=date_tz))
+                date = date_raw.replace(tzinfo=timezone)
+
+                self.tell(f"Embedding metadata for track {track_id}")
+                track_dir = data_dir / "tracks" / str(track_id)
+                gpx_path = track_dir / "track.gpx.br"
+
+                raw_gpx = brotli.decompress(gpx_path.read_bytes())
+                gpx = gpxpy.parse(raw_gpx)
+
+                for track in gpx.tracks:
+                    track.name = None
+                    track.description = None
+
+                gpx.author_name = author_name
+                gpx.name = title
+                gpx.description = description
+                gpx.time = date
+
+                gpx_path.write_bytes(brotli.compress(gpx.to_xml().encode("utf-8"), quality=4))
 
     def post_alembic(self, config):
         pass
diff --git a/fietsboek/updater/scripts/upd_20251109_nm561argcq1s8w27.py b/fietsboek/updater/scripts/upd_20251109_nm561argcq1s8w27.py
index e3e5e47..ae6c29a 100644
--- a/fietsboek/updater/scripts/upd_20251109_nm561argcq1s8w27.py
+++ b/fietsboek/updater/scripts/upd_20251109_nm561argcq1s8w27.py
@@ -36,127 +36,128 @@ class Up(UpdateScript):
         connection = engine.connect()
         data_dir = Path(config["fietsboek.data_dir"])
 
-        # This can happen in a fresh instance
-        if not (data_dir / "tracks").exists():
-            return
-
-        for track_dir in (data_dir / "tracks").iterdir():
-            track_id = int(track_dir.name)
-            self.tell(f"Loading track {track_id}")
-
-            gpx_path = track_dir / "track.gpx.br"
-
-            # We're careful here, in case a previous update was interrupted
-            if not gpx_path.exists():
-                continue
-
-            gpx_bytes = brotli.decompress(gpx_path.read_bytes())
-
-            track = convert.smart_convert(gpx_bytes)
-            with connection.begin():
-                connection.execute(
-                    text("DELETE FROM track_points WHERE track_id = :id;"),
-                    {"id": track_id},
-                )
-                connection.execute(
-                    text("DELETE FROM waypoints WHERE track_id = :id;"),
-                    {"id": track_id},
-                )
-                for index, point in enumerate(track.path().points):
+        with engine.connect() as connection:
+            # This can happen in a fresh instance
+            if not (data_dir / "tracks").exists():
+                return
+
+            for track_dir in (data_dir / "tracks").iterdir():
+                track_id = int(track_dir.name)
+                self.tell(f"Loading track {track_id}")
+
+                gpx_path = track_dir / "track.gpx.br"
+
+                # We're careful here, in case a previous update was interrupted
+                if not gpx_path.exists():
+                    continue
+
+                gpx_bytes = brotli.decompress(gpx_path.read_bytes())
+
+                track = convert.smart_convert(gpx_bytes)
+                with connection.begin():
                     connection.execute(
-                        text("""INSERT INTO track_points (
-                            track_id, "index", longitude, latitude, elevation, time_offset
-                        ) VALUES (
-                            :track_id, :index, :longitude, :latitude, :elevation, :time_offset
-                        );"""),
-                        {
-                            "track_id": track_id,
-                            "index": index,
-                            "longitude": point.longitude,
-                            "latitude": point.latitude,
-                            "elevation": point.elevation,
-                            "time_offset": point.time_offset,
-                        },
+                        text("DELETE FROM track_points WHERE track_id = :id;"),
+                        {"id": track_id},
                     )
-                for waypoint in track.waypoints:
                     connection.execute(
-                        text("""INSERT INTO waypoints (
-                            track_id, longitude, latitude, elevation, name, description
-                        ) VALUES (
-                            :track_id, :longitude, :latitude, :elevation, :name, :description
-                        );"""),
-                        {
-                            "track_id": track_id,
-                            "longitude": waypoint.longitude,
-                            "latitude": waypoint.latitude,
-                            "elevation": waypoint.elevation,
-                            "name": waypoint.name,
-                            "description": waypoint.description,
-                        },
+                        text("DELETE FROM waypoints WHERE track_id = :id;"),
+                        {"id": track_id},
+                    )
+                    for index, point in enumerate(track.path().points):
+                        connection.execute(
+                            text("""INSERT INTO track_points (
+                                track_id, "index", longitude, latitude, elevation, time_offset
+                            ) VALUES (
+                                :track_id, :index, :longitude, :latitude, :elevation, :time_offset
+                            );"""),
+                            {
+                                "track_id": track_id,
+                                "index": index,
+                                "longitude": point.longitude,
+                                "latitude": point.latitude,
+                                "elevation": point.elevation,
+                                "time_offset": point.time_offset,
+                            },
+                        )
+                    for waypoint in track.waypoints:
+                        connection.execute(
+                            text("""INSERT INTO waypoints (
+                                track_id, longitude, latitude, elevation, name, description
+                            ) VALUES (
+                                :track_id, :longitude, :latitude, :elevation, :name, :description
+                            );"""),
+                            {
+                                "track_id": track_id,
+                                "longitude": waypoint.longitude,
+                                "latitude": waypoint.latitude,
+                                "elevation": waypoint.elevation,
+                                "name": waypoint.name,
+                                "description": waypoint.description,
+                            },
+                        )
+
+                    gpx_path.unlink()
+                    shutil.move(
+                        track_dir / "track.bck.gpx.br",
+                        track_dir / "track.bck.br",
                     )
-
-                gpx_path.unlink()
-                shutil.move(
-                    track_dir / "track.bck.gpx.br",
-                    track_dir / "track.bck.br",
-                )
 
 
 class Down(UpdateScript):
     def pre_alembic(self, config):
         engine = create_engine(config["sqlalchemy.url"])
-        connection = engine.connect()
         data_dir = Path(config["fietsboek.data_dir"])
 
         query = text("SELECT id, title, description, date_raw FROM tracks;")
 
-        for row in connection.execute(query):
-            gpx = gpxpy.gpx.GPX()
-            gpx.description = row.description
-            gpx.name = row.title
-
-            start_date = row.date_raw
-            if isinstance(start_date, str):
-                start_date = datetime.datetime.fromisoformat(start_date)
-
-            segment = gpxpy.gpx.GPXTrackSegment()
-            points_query = text("""
-                SELECT longitude, latitude, elevation, time_offset
-                FROM track_points WHERE track_id = :track_id ORDER BY "index";
-            """)
-            for point in connection.execute(points_query, {"track_id": row.id}):
-                segment.points.append(
-                    gpxpy.gpx.GPXTrackPoint(
-                        latitude=point.latitude,
-                        longitude=point.longitude,
-                        elevation=point.elevation,
-                        time=start_date + datetime.timedelta(seconds=point.time_offset),
+        with engine.connect() as connection:
+            for row in connection.execute(query):
+                gpx = gpxpy.gpx.GPX()
+                gpx.description = row.description
+                gpx.name = row.title
+
+                start_date = row.date_raw
+                if isinstance(start_date, str):
+                    start_date = datetime.datetime.fromisoformat(start_date)
+
+                segment = gpxpy.gpx.GPXTrackSegment()
+                points_query = text("""
+                    SELECT longitude, latitude, elevation, time_offset
+                    FROM track_points WHERE track_id = :track_id ORDER BY "index";
+                """)
+                for point in connection.execute(points_query, {"track_id": row.id}):
+                    segment.points.append(
+                        gpxpy.gpx.GPXTrackPoint(
+                            latitude=point.latitude,
+                            longitude=point.longitude,
+                            elevation=point.elevation,
+                            time=start_date + datetime.timedelta(seconds=point.time_offset),
+                        )
                     )
-                )
-            track = gpxpy.gpx.GPXTrack()
-            track.segments.append(segment)
-            gpx.tracks.append(track)
-
-            waypoints_query = text("""
-                SELECT longitude, latitude, elevation, name, description
-                FROM waypoints WHERE track_id = :track_id;
-            """)
-            for wpt in connection.execute(waypoints_query, {"track_id": row.id}):
-                gpx.waypoints.append(
-                    gpxpy.gpx.GPXWaypoint(
-                        longitude=wpt.longitude,
-                        latitude=wpt.latitude,
-                        elevation=wpt.elevation,
-                        name=wpt.name,
-                        comment=wpt.description,
-                        description=wpt.description,
+                track = gpxpy.gpx.GPXTrack()
+                track.segments.append(segment)
+                gpx.tracks.append(track)
+
+                waypoints_query = text("""
+                    SELECT longitude, latitude, elevation, name, description
+                    FROM waypoints WHERE track_id = :track_id;
+                """)
+                for wpt in connection.execute(waypoints_query, {"track_id": row.id}):
+                    gpx.waypoints.append(
+                        gpxpy.gpx.GPXWaypoint(
+                            longitude=wpt.longitude,
+                            latitude=wpt.latitude,
+                            elevation=wpt.elevation,
+                            name=wpt.name,
+                            comment=wpt.description,
+                            description=wpt.description,
+                        )
                     )
-                )
 
-            xml_data = gpx.to_xml(prettyprint=False).encode("utf-8")
-            track_dir = data_dir / "tracks" / str(row.id)
-            (track_dir / "track.gpx.br").write_bytes(brotli.compress(xml_data))
-            shutil.move(track_dir / "track.bck.br", track_dir / "track.bck.gpx.br")
+                xml_data = gpx.to_xml(prettyprint=False).encode("utf-8")
+                track_dir = data_dir / "tracks" / str(row.id)
+                (track_dir / "track.gpx.br").write_bytes(brotli.compress(xml_data))
+                shutil.move(track_dir / "track.bck.br", track_dir / "track.bck.gpx.br")
 
     def post_alembic(self, config):
         pass
diff --git a/fietsboek/updater/scripts/upd_30ppwg8zi4ujb46f.py b/fietsboek/updater/scripts/upd_30ppwg8zi4ujb46f.py
index e900c7a..cdc09f6 100644
--- a/fietsboek/updater/scripts/upd_30ppwg8zi4ujb46f.py
+++ b/fietsboek/updater/scripts/upd_30ppwg8zi4ujb46f.py
@@ -25,18 +25,18 @@ alembic_revision = 'c939800af428'
 class Up(UpdateScript):
     def pre_alembic(self, config):
         engine = create_engine(config["sqlalchemy.url"])
-        connection = engine.connect()
-        data_dir = Path(config["fietsboek.data_dir"])
+        with engine.connect() as connection:
+            data_dir = Path(config["fietsboek.data_dir"])
 
-        for row in connection.execute(text("SELECT id, gpx FROM tracks;")):
-            self.tell(f"Moving GPX data for track {row.id} from database to disk")
-            track_dir = data_dir / "tracks" / str(row.id)
-            track_dir.mkdir(parents=True, exist_ok=True)
+            for row in connection.execute(text("SELECT id, gpx FROM tracks;")):
+                self.tell(f"Moving GPX data for track {row.id} from database to disk")
+                track_dir = data_dir / "tracks" / str(row.id)
+                track_dir.mkdir(parents=True, exist_ok=True)
 
-            raw_gpx = gzip.decompress(row.gpx)
-            gpx_path = track_dir / "track.gpx.br"
-            gpx_path.write_bytes(brotli.compress(raw_gpx, quality=5))
-            shutil.copy(gpx_path, track_dir / "track.bck.gpx.br")
+                raw_gpx = gzip.decompress(row.gpx)
+                gpx_path = track_dir / "track.gpx.br"
+                gpx_path.write_bytes(brotli.compress(raw_gpx, quality=5))
+                shutil.copy(gpx_path, track_dir / "track.bck.gpx.br")
 
     def post_alembic(self, config):
         pass
@@ -48,18 +48,18 @@ class Down(UpdateScript):
 
     def post_alembic(self, config):
         engine = create_engine(config["sqlalchemy.url"])
-        connection = engine.connect()
-        data_dir = Path(config["fietsboek.data_dir"])
+        with engine.connect() as connection:
+            data_dir = Path(config["fietsboek.data_dir"])
 
-        for track_path in (data_dir / "tracks").iterdir():
-            track_id = int(track_path.name)
-            self.tell(f"Moving GPX data for track {track_id} from disk to database")
-            brotli_data = (track_path / "track.gpx.br").read_bytes()
-            gzip_data = gzip.compress(brotli.decompress(brotli_data))
-            connection.execute(
-                text("UPDATE tracks SET gpx = :gpx WHERE id = :id;"),
-                gpx=gzip_data, id=track_id
-            )
+            for track_path in (data_dir / "tracks").iterdir():
+                track_id = int(track_path.name)
+                self.tell(f"Moving GPX data for track {track_id} from disk to database")
+                brotli_data = (track_path / "track.gpx.br").read_bytes()
+                gzip_data = gzip.compress(brotli.decompress(brotli_data))
+                connection.execute(
+                    text("UPDATE tracks SET gpx = :gpx WHERE id = :id;"),
+                    gpx=gzip_data, id=track_id
+                )
 
-            (track_path / "track.gpx.br").unlink()
-            (track_path / "track.bck.gpx.br").unlink(missing_ok=True)
+                (track_path / "track.gpx.br").unlink()
+                (track_path / "track.bck.gpx.br").unlink(missing_ok=True)