Skip to content

Commit e23adea

Browse files
kweinmeisterm-strzelczykjackwotherspoonleahecole
authored
chore: migrate cloud-sql to sqlalchemy 2 (GoogleCloudPlatform#9087)
* feat: migrate cloud-sql to sqlalchemy 2 * linting --------- Co-authored-by: Maciej Strzelczyk <strzelczyk@google.com> Co-authored-by: Jack Wotherspoon <jackwoth@google.com> Co-authored-by: Leah E. Cole <6719667+leahecole@users.noreply.github.com>
1 parent 5741faa commit e23adea

File tree

6 files changed

+22
-28
lines changed

6 files changed

+22
-28
lines changed

‎cloud-sql/mysql/sqlalchemy/app.py‎

Lines changed: 7 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -54,11 +54,11 @@ def init_connection_pool() -> sqlalchemy.engine.base.Engine:
5454
# create 'votes' table in database if it does not already exist
5555
def migrate_db(db: sqlalchemy.engine.base.Engine) -> None:
5656
with db.connect() as conn:
57-
conn.execute(
57+
conn.execute(sqlalchemy.text(
5858
"CREATE TABLE IF NOT EXISTS votes "
5959
"( vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL, "
6060
"candidate VARCHAR(6) NOT NULL, PRIMARY KEY (vote_id) );"
61-
)
61+
))
6262

6363

6464
# This global variable is declared with a value of `None`, instead of calling
@@ -96,9 +96,9 @@ def get_index_context(db: sqlalchemy.engine.base.Engine) -> Dict:
9696

9797
with db.connect() as conn:
9898
# Execute the query and fetch all results
99-
recent_votes = conn.execute(
99+
recent_votes = conn.execute(sqlalchemy.text(
100100
"SELECT candidate, time_cast FROM votes ORDER BY time_cast DESC LIMIT 5"
101-
).fetchall()
101+
)).fetchall()
102102
# Convert the results into a list of dicts representing votes
103103
for row in recent_votes:
104104
votes.append({"candidate": row[0], "time_cast": row[1]})
@@ -107,11 +107,9 @@ def get_index_context(db: sqlalchemy.engine.base.Engine) -> Dict:
107107
"SELECT COUNT(vote_id) FROM votes WHERE candidate=:candidate"
108108
)
109109
# Count number of votes for tabs
110-
tab_result = conn.execute(stmt, candidate="TABS").fetchone()
111-
tab_count = tab_result[0]
110+
tab_count = conn.execute(stmt, parameters={"candidate": "TABS"}).scalar()
112111
# Count number of votes for spaces
113-
space_result = conn.execute(stmt, candidate="SPACES").fetchone()
114-
space_count = space_result[0]
112+
space_count = conn.execute(stmt, parameters={"candidate": "SPACES"}).scalar()
115113

116114
return {
117115
"space_count": space_count,
@@ -140,7 +138,7 @@ def save_vote(db: sqlalchemy.engine.base.Engine, team: str) -> Response:
140138
# Using a with statement ensures that the connection is always released
141139
# back into the pool at the end of statement (even if an error occurs)
142140
with db.connect() as conn:
143-
conn.execute(stmt, time_cast=time_cast, candidate=team)
141+
conn.execute(stmt, parameters={"time_cast": time_cast, "candidate": team})
144142
except Exception as e:
145143
# If something goes wrong, handle the error in this section. This might
146144
# involve retrying or adjusting parameters depending on the situation.

‎cloud-sql/mysql/sqlalchemy/requirements.txt‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
Flask==2.1.0
2-
SQLAlchemy==1.4.38
2+
SQLAlchemy==2.0.1
33
PyMySQL==1.0.2
44
gunicorn==20.1.0
55
cloud-sql-python-connector==1.1.0

‎cloud-sql/postgres/sqlalchemy/app.py‎

Lines changed: 7 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -54,11 +54,11 @@ def init_connection_pool() -> sqlalchemy.engine.base.Engine:
5454
# create 'votes' table in database if it does not already exist
5555
def migrate_db(db: sqlalchemy.engine.base.Engine) -> None:
5656
with db.connect() as conn:
57-
conn.execute(
57+
conn.execute(sqlalchemy.text(
5858
"CREATE TABLE IF NOT EXISTS votes "
5959
"( vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL, "
6060
"candidate VARCHAR(6) NOT NULL, PRIMARY KEY (vote_id) );"
61-
)
61+
))
6262

6363

6464
# This global variable is declared with a value of `None`, instead of calling
@@ -96,9 +96,9 @@ def get_index_context(db: sqlalchemy.engine.base.Engine) -> Dict:
9696

9797
with db.connect() as conn:
9898
# Execute the query and fetch all results
99-
recent_votes = conn.execute(
99+
recent_votes = conn.execute(sqlalchemy.text(
100100
"SELECT candidate, time_cast FROM votes ORDER BY time_cast DESC LIMIT 5"
101-
).fetchall()
101+
)).fetchall()
102102
# Convert the results into a list of dicts representing votes
103103
for row in recent_votes:
104104
votes.append({"candidate": row[0], "time_cast": row[1]})
@@ -107,11 +107,9 @@ def get_index_context(db: sqlalchemy.engine.base.Engine) -> Dict:
107107
"SELECT COUNT(vote_id) FROM votes WHERE candidate=:candidate"
108108
)
109109
# Count number of votes for tabs
110-
tab_result = conn.execute(stmt, candidate="TABS").fetchone()
111-
tab_count = tab_result[0]
110+
tab_count = conn.execute(stmt, parameters={"candidate": "TABS"}).scalar()
112111
# Count number of votes for spaces
113-
space_result = conn.execute(stmt, candidate="SPACES").fetchone()
114-
space_count = space_result[0]
112+
space_count = conn.execute(stmt, parameters={"candidate": "SPACES"}).scalar()
115113

116114
return {
117115
"space_count": space_count,
@@ -140,7 +138,7 @@ def save_vote(db: sqlalchemy.engine.base.Engine, team: str) -> Response:
140138
# Using a with statement ensures that the connection is always released
141139
# back into the pool at the end of statement (even if an error occurs)
142140
with db.connect() as conn:
143-
conn.execute(stmt, time_cast=time_cast, candidate=team)
141+
conn.execute(stmt, parameters={"time_cast": time_cast, "candidate": team})
144142
except Exception as e:
145143
# If something goes wrong, handle the error in this section. This might
146144
# involve retrying or adjusting parameters depending on the situation.

‎cloud-sql/postgres/sqlalchemy/requirements.txt‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
Flask==2.1.0
22
pg8000==1.24.2
3-
SQLAlchemy==1.4.38
3+
SQLAlchemy==2.0.1
44
cloud-sql-python-connector==1.1.0
55
gunicorn==20.1.0

‎cloud-sql/sql-server/sqlalchemy/app.py‎

Lines changed: 5 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -95,9 +95,9 @@ def get_index_context(db: sqlalchemy.engine.base.Engine) -> Dict:
9595
votes = []
9696
with db.connect() as conn:
9797
# Execute the query and fetch all results
98-
recent_votes = conn.execute(
98+
recent_votes = conn.execute(sqlalchemy.text(
9999
"SELECT TOP(5) candidate, time_cast FROM votes ORDER BY time_cast DESC"
100-
).fetchall()
100+
)).fetchall()
101101
# Convert the results into a list of dicts representing votes
102102
for row in recent_votes:
103103
votes.append({"candidate": row[0], "time_cast": row[1]})
@@ -106,11 +106,9 @@ def get_index_context(db: sqlalchemy.engine.base.Engine) -> Dict:
106106
"SELECT COUNT(vote_id) FROM votes WHERE candidate=:candidate"
107107
)
108108
# Count number of votes for tabs
109-
tab_result = conn.execute(stmt, candidate="TABS").fetchone()
110-
tab_count = tab_result[0]
109+
tab_count = conn.execute(stmt, parameters={"candidate": "TABS"}).scalar()
111110
# Count number of votes for spaces
112-
space_result = conn.execute(stmt, candidate="SPACES").fetchone()
113-
space_count = space_result[0]
111+
space_count = conn.execute(stmt, parameters={"candidate": "SPACES"}).scalar()
114112
return {
115113
"recent_votes": votes,
116114
"space_count": space_count,
@@ -138,7 +136,7 @@ def save_vote(db: sqlalchemy.engine.base.Engine, team: str) -> Response:
138136
# Using a with statement ensures that the connection is always released
139137
# back into the pool at the end of statement (even if an error occurs)
140138
with db.connect() as conn:
141-
conn.execute(stmt, time_cast=time_cast, candidate=team)
139+
conn.execute(stmt, parameters={"time_cast": time_cast, "candidate": team})
142140
except Exception as e:
143141
# If something goes wrong, handle the error in this section. This might
144142
# involve retrying or adjusting parameters depending on the situation.

‎cloud-sql/sql-server/sqlalchemy/requirements.txt‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,6 @@ Flask==2.1.0
22
gunicorn==20.1.0
33
python-tds==1.11.0
44
pyopenssl==23.0.0
5-
SQLAlchemy==1.4.38
5+
SQLAlchemy==2.0.1
66
cloud-sql-python-connector==1.1.0
77
sqlalchemy-pytds==0.3.4

0 commit comments

Comments
 (0)