Skip to content

Commit 9fe89ac

Browse files
m-strzelczykm-strzelczyk
authored
fix: Clean up Python sample at cloud-sql/postgres/sqlalchemy (GoogleCloudPlatform#10023)
* fix: Clean up Python sample at cloud-sql/postgres/sqlalchemy * Update app.py
1 parent e2b2155 commit 9fe89ac

6 files changed

Lines changed: 58 additions & 33 deletions

File tree

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

Lines changed: 38 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,7 @@
3333

3434

3535
def init_connection_pool() -> sqlalchemy.engine.base.Engine:
36+
"""Sets up connection pool for the app."""
3637
# use a TCP socket when INSTANCE_HOST (e.g. 127.0.0.1) is defined
3738
if os.environ.get("INSTANCE_HOST"):
3839
return connect_tcp_socket()
@@ -45,7 +46,11 @@ def init_connection_pool() -> sqlalchemy.engine.base.Engine:
4546
if os.environ.get("INSTANCE_CONNECTION_NAME"):
4647
# Either a DB_USER or a DB_IAM_USER should be defined. If both are
4748
# defined, DB_IAM_USER takes precedence.
48-
return connect_with_connector_auto_iam_authn() if os.environ.get("DB_IAM_USER") else connect_with_connector()
49+
return (
50+
connect_with_connector_auto_iam_authn()
51+
if os.environ.get("DB_IAM_USER")
52+
else connect_with_connector()
53+
)
4954

5055
raise ValueError(
5156
"Missing database connection type. Please define one of INSTANCE_HOST, INSTANCE_UNIX_SOCKET, or INSTANCE_CONNECTION_NAME"
@@ -54,12 +59,15 @@ def init_connection_pool() -> sqlalchemy.engine.base.Engine:
5459

5560
# create 'votes' table in database if it does not already exist
5661
def migrate_db(db: sqlalchemy.engine.base.Engine) -> None:
62+
"""Creates the `votes` table if it doesn't exist."""
5763
with db.connect() as conn:
58-
conn.execute(sqlalchemy.text(
59-
"CREATE TABLE IF NOT EXISTS votes "
60-
"( vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL, "
61-
"candidate VARCHAR(6) NOT NULL, PRIMARY KEY (vote_id) );"
62-
))
64+
conn.execute(
65+
sqlalchemy.text(
66+
"CREATE TABLE IF NOT EXISTS votes "
67+
"( vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL, "
68+
"candidate VARCHAR(6) NOT NULL, PRIMARY KEY (vote_id) );"
69+
)
70+
)
6371
conn.commit()
6472

6573

@@ -75,32 +83,44 @@ def migrate_db(db: sqlalchemy.engine.base.Engine) -> None:
7583
# as the function is loaded. This is primarily to help testing.
7684
@app.before_first_request
7785
def init_db() -> sqlalchemy.engine.base.Engine:
86+
"""Initiates connection to database and its structure."""
7887
global db
7988
db = init_connection_pool()
8089
migrate_db(db)
8190

8291

8392
@app.route("/", methods=["GET"])
8493
def render_index() -> str:
94+
"""Serves the index page of the app."""
8595
context = get_index_context(db)
8696
return render_template("index.html", **context)
8797

8898

8999
@app.route("/votes", methods=["POST"])
90100
def cast_vote() -> Response:
91-
team = request.form['team']
101+
"""Processes a single vote from user."""
102+
team = request.form["team"]
92103
return save_vote(db, team)
93104

94105

95106
# get_index_context gets data required for rendering HTML application
96107
def get_index_context(db: sqlalchemy.engine.base.Engine) -> dict:
108+
"""Retrieves data from the database about the votes.
109+
110+
Args:
111+
db: Connection to the database.
112+
Returns:
113+
A dictionary containing information about votes.
114+
"""
97115
votes = []
98116

99117
with db.connect() as conn:
100118
# Execute the query and fetch all results
101-
recent_votes = conn.execute(sqlalchemy.text(
102-
"SELECT candidate, time_cast FROM votes ORDER BY time_cast DESC LIMIT 5"
103-
)).fetchall()
119+
recent_votes = conn.execute(
120+
sqlalchemy.text(
121+
"SELECT candidate, time_cast FROM votes ORDER BY time_cast DESC LIMIT 5"
122+
)
123+
).fetchall()
104124
# Convert the results into a list of dicts representing votes
105125
for row in recent_votes:
106126
votes.append({"candidate": row[0], "time_cast": row[1]})
@@ -122,6 +142,14 @@ def get_index_context(db: sqlalchemy.engine.base.Engine) -> dict:
122142

123143
# save_vote saves a vote to the database that was retrieved from form data
124144
def save_vote(db: sqlalchemy.engine.base.Engine, team: str) -> Response:
145+
"""Saves a single vote into the database.
146+
147+
Args:
148+
db: Connection to the database.
149+
team: The identifier of a team the vote is cast on.
150+
Returns:
151+
A HTTP response that can be sent to the client.
152+
"""
125153
time_cast = datetime.datetime.now(tz=datetime.timezone.utc)
126154
# Verify that the team is one of the allowed options
127155
if team != "TABS" and team != "SPACES":

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

Lines changed: 4 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -32,7 +32,9 @@ def connect_with_connector() -> sqlalchemy.engine.base.Engine:
3232
# Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
3333
# keep secrets safe.
3434

35-
instance_connection_name = os.environ["INSTANCE_CONNECTION_NAME"] # e.g. 'project:region:instance'
35+
instance_connection_name = os.environ[
36+
"INSTANCE_CONNECTION_NAME"
37+
] # e.g. 'project:region:instance'
3638
db_user = os.environ["DB_USER"] # e.g. 'my-db-user'
3739
db_pass = os.environ["DB_PASS"] # e.g. 'my-db-password'
3840
db_name = os.environ["DB_NAME"] # e.g. 'my-database'
@@ -61,18 +63,14 @@ def getconn() -> pg8000.dbapi.Connection:
6163
# [START_EXCLUDE]
6264
# Pool size is the maximum number of permanent connections to keep.
6365
pool_size=5,
64-
6566
# Temporarily exceeds the set pool_size if no connections are available.
6667
max_overflow=2,
67-
6868
# The total number of concurrent connections for your application will be
6969
# a total of pool_size and max_overflow.
70-
7170
# 'pool_timeout' is the maximum number of seconds to wait when retrieving a
7271
# new connection from the pool. After the specified amount of time, an
7372
# exception will be thrown.
7473
pool_timeout=30, # 30 seconds
75-
7674
# 'pool_recycle' is the maximum number of seconds a connection can persist.
7775
# Connections that live longer than the specified amount of time will be
7876
# re-established
@@ -81,4 +79,5 @@ def getconn() -> pg8000.dbapi.Connection:
8179
)
8280
return pool
8381

82+
8483
# [END cloud_sql_postgres_sqlalchemy_connect_connector]

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

Lines changed: 4 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -31,7 +31,9 @@ def connect_with_connector_auto_iam_authn() -> sqlalchemy.engine.base.Engine:
3131
# secure - consider a more secure solution such as
3232
# Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
3333
# keep secrets safe.
34-
instance_connection_name = os.environ["INSTANCE_CONNECTION_NAME"] # e.g. 'project:region:instance'
34+
instance_connection_name = os.environ[
35+
"INSTANCE_CONNECTION_NAME"
36+
] # e.g. 'project:region:instance'
3537
db_iam_user = os.environ["DB_IAM_USER"] # e.g. 'sa-name@project-id.iam'
3638
db_name = os.environ["DB_NAME"] # e.g. 'my-database'
3739

@@ -59,18 +61,14 @@ def getconn() -> pg8000.dbapi.Connection:
5961
# [START_EXCLUDE]
6062
# Pool size is the maximum number of permanent connections to keep.
6163
pool_size=5,
62-
6364
# Temporarily exceeds the set pool_size if no connections are available.
6465
max_overflow=2,
65-
6666
# The total number of concurrent connections for your application will be
6767
# a total of pool_size and max_overflow.
68-
6968
# 'pool_timeout' is the maximum number of seconds to wait when retrieving a
7069
# new connection from the pool. After the specified amount of time, an
7170
# exception will be thrown.
7271
pool_timeout=30, # 30 seconds
73-
7472
# 'pool_recycle' is the maximum number of seconds a connection can persist.
7573
# Connections that live longer than the specified amount of time will be
7674
# re-established
@@ -79,4 +77,5 @@ def getconn() -> pg8000.dbapi.Connection:
7977
)
8078
return pool
8179

80+
8281
# [END cloud_sql_postgres_sqlalchemy_auto_iam_authn]

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

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -23,12 +23,14 @@
2323

2424

2525
def connect_tcp_socket() -> sqlalchemy.engine.base.Engine:
26-
""" Initializes a TCP connection pool for a Cloud SQL instance of Postgres. """
26+
"""Initializes a TCP connection pool for a Cloud SQL instance of Postgres."""
2727
# Note: Saving credentials in environment variables is convenient, but not
2828
# secure - consider a more secure solution such as
2929
# Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
3030
# keep secrets safe.
31-
db_host = os.environ["INSTANCE_HOST"] # e.g. '127.0.0.1' ('172.17.0.1' if deployed to GAE Flex)
31+
db_host = os.environ[
32+
"INSTANCE_HOST"
33+
] # e.g. '127.0.0.1' ('172.17.0.1' if deployed to GAE Flex)
3234
db_user = os.environ["DB_USER"] # e.g. 'my-db-user'
3335
db_pass = os.environ["DB_PASS"] # e.g. 'my-db-password'
3436
db_name = os.environ["DB_NAME"] # e.g. 'my-database'
@@ -74,19 +76,16 @@ def connect_tcp_socket() -> sqlalchemy.engine.base.Engine:
7476
# The total number of concurrent connections for your application will be
7577
# a total of pool_size and max_overflow.
7678
# [END cloud_sql_postgres_sqlalchemy_limit]
77-
7879
# [START cloud_sql_postgres_sqlalchemy_backoff]
7980
# SQLAlchemy automatically uses delays between failed connection attempts,
8081
# but provides no arguments for configuration.
8182
# [END cloud_sql_postgres_sqlalchemy_backoff]
82-
8383
# [START cloud_sql_postgres_sqlalchemy_timeout]
8484
# 'pool_timeout' is the maximum number of seconds to wait when retrieving a
8585
# new connection from the pool. After the specified amount of time, an
8686
# exception will be thrown.
8787
pool_timeout=30, # 30 seconds
8888
# [END cloud_sql_postgres_sqlalchemy_timeout]
89-
9089
# [START cloud_sql_postgres_sqlalchemy_lifetime]
9190
# 'pool_recycle' is the maximum number of seconds a connection can persist.
9291
# Connections that live longer than the specified amount of time will be
@@ -97,6 +96,7 @@ def connect_tcp_socket() -> sqlalchemy.engine.base.Engine:
9796
)
9897
return pool
9998

99+
100100
# [END cloud_sql_postgres_sqlalchemy_connect_tcp_sslcerts]
101101
# [END cloud_sql_postgres_sqlalchemy_sslcerts]
102102
# [END cloud_sql_postgres_sqlalchemy_connect_tcp]

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

Lines changed: 5 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -19,15 +19,17 @@
1919

2020

2121
def connect_unix_socket() -> sqlalchemy.engine.base.Engine:
22-
""" Initializes a Unix socket connection pool for a Cloud SQL instance of Postgres. """
22+
"""Initializes a Unix socket connection pool for a Cloud SQL instance of Postgres."""
2323
# Note: Saving credentials in environment variables is convenient, but not
2424
# secure - consider a more secure solution such as
2525
# Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
2626
# keep secrets safe.
2727
db_user = os.environ["DB_USER"] # e.g. 'my-database-user'
2828
db_pass = os.environ["DB_PASS"] # e.g. 'my-database-password'
2929
db_name = os.environ["DB_NAME"] # e.g. 'my-database'
30-
unix_socket_path = os.environ["INSTANCE_UNIX_SOCKET"] # e.g. '/cloudsql/project:region:instance'
30+
unix_socket_path = os.environ[
31+
"INSTANCE_UNIX_SOCKET"
32+
] # e.g. '/cloudsql/project:region:instance'
3133

3234
pool = sqlalchemy.create_engine(
3335
# Equivalent URL:
@@ -45,18 +47,14 @@ def connect_unix_socket() -> sqlalchemy.engine.base.Engine:
4547
# [START_EXCLUDE]
4648
# Pool size is the maximum number of permanent connections to keep.
4749
pool_size=5,
48-
4950
# Temporarily exceeds the set pool_size if no connections are available.
5051
max_overflow=2,
51-
5252
# The total number of concurrent connections for your application will be
5353
# a total of pool_size and max_overflow.
54-
5554
# 'pool_timeout' is the maximum number of seconds to wait when retrieving a
5655
# new connection from the pool. After the specified amount of time, an
5756
# exception will be thrown.
5857
pool_timeout=30, # 30 seconds
59-
6058
# 'pool_recycle' is the maximum number of seconds a connection can persist.
6159
# Connections that live longer than the specified amount of time will be
6260
# re-established
@@ -65,4 +63,5 @@ def connect_unix_socket() -> sqlalchemy.engine.base.Engine:
6563
)
6664
return pool
6765

66+
6867
# [END cloud_sql_postgres_sqlalchemy_connect_unix]

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@
1212
# See the License for the specific language governing permissions and
1313
# limitations under the License.
1414

15-
from flask import render_template, Response
15+
from flask import render_template, Request, Response
1616

1717
import functions_framework
1818

@@ -27,7 +27,7 @@
2727

2828

2929
@functions_framework.http
30-
def votes(request):
30+
def votes(request: Request) -> Response:
3131
if request.method == "GET":
3232
context = get_index_context(db)
3333
return render_template("index.html", **context)

0 commit comments

Comments
 (0)