fix(cloudsql): make connections commit as you go (GoogleCloudPlatform#9160)

jackwotherspoon · web-flow · commit 7c183c00ad57 · 2023-02-22T14:14:30.000-05:00
diff --git a/cloud-sql/mysql/sqlalchemy/app.py b/cloud-sql/mysql/sqlalchemy/app.py
@@ -59,6 +59,7 @@ def migrate_db(db: sqlalchemy.engine.base.Engine) -> None:
             "( vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL, "
             "candidate VARCHAR(6) NOT NULL, PRIMARY KEY (vote_id) );"
         ))
+        conn.commit()
 
 
 # This global variable is declared with a value of `None`, instead of calling
@@ -139,6 +140,7 @@ def save_vote(db: sqlalchemy.engine.base.Engine, team: str) -> Response:
         # back into the pool at the end of statement (even if an error occurs)
         with db.connect() as conn:
             conn.execute(stmt, parameters={"time_cast": time_cast, "candidate": team})
+            conn.commit()
     except Exception as e:
         # If something goes wrong, handle the error in this section. This might
         # involve retrying or adjusting parameters depending on the situation.
diff --git a/cloud-sql/postgres/sqlalchemy/app.py b/cloud-sql/postgres/sqlalchemy/app.py
@@ -59,6 +59,7 @@ def migrate_db(db: sqlalchemy.engine.base.Engine) -> None:
             "( vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL, "
             "candidate VARCHAR(6) NOT NULL, PRIMARY KEY (vote_id) );"
         ))
+        conn.commit()
 
 
 # This global variable is declared with a value of `None`, instead of calling
@@ -139,6 +140,7 @@ def save_vote(db: sqlalchemy.engine.base.Engine, team: str) -> Response:
         # back into the pool at the end of statement (even if an error occurs)
         with db.connect() as conn:
             conn.execute(stmt, parameters={"time_cast": time_cast, "candidate": team})
+            conn.commit()
     except Exception as e:
         # If something goes wrong, handle the error in this section. This might
         # involve retrying or adjusting parameters depending on the situation.
diff --git a/cloud-sql/sql-server/sqlalchemy/app.py b/cloud-sql/sql-server/sqlalchemy/app.py
@@ -137,6 +137,7 @@ def save_vote(db: sqlalchemy.engine.base.Engine, team: str) -> Response:
         # back into the pool at the end of statement (even if an error occurs)
         with db.connect() as conn:
             conn.execute(stmt, parameters={"time_cast": time_cast, "candidate": team})
+            conn.commit()
     except Exception as e:
         # If something goes wrong, handle the error in this section. This might
         # involve retrying or adjusting parameters depending on the situation.
