GoogleCloudPlatform · shubha-rajan · Apr 15, 2020 · Mar 24, 2020 · Mar 24, 2020 · Mar 24, 2020
diff --git a/cloud-sql/sql-server/sqlalchemy/.gcloudignore b/cloud-sql/sql-server/sqlalchemy/.gcloudignore
@@ -0,0 +1,19 @@
+# This file specifies files that are *not* uploaded to Google Cloud Platform
+# using gcloud. It follows the same syntax as .gitignore, with the addition of
+# "#!include" directives (which insert the entries of the given .gitignore-style
+# file at that point).
+#
+# For more information, run:
+#   $ gcloud topic gcloudignore
+#
+.gcloudignore
+# If you would like to upload your .git directory, .gitignore file or files
+# from your .gitignore file, remove the corresponding line
+# below:
+.git
+.gitignore
+
+# Python pycache:
+__pycache__/
+# Ignored by the build system
+/setup.cfg
diff --git a/cloud-sql/sql-server/sqlalchemy/.gitignore b/cloud-sql/sql-server/sqlalchemy/.gitignore
@@ -0,0 +1,2 @@
+__pycache__
+.pytest_cache
diff --git a/cloud-sql/sql-server/sqlalchemy/Dockerfile b/cloud-sql/sql-server/sqlalchemy/Dockerfile
@@ -0,0 +1,42 @@
+# Copyright 2020 Google, LLC.
+#
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+#    http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+
+# Use the official Python image.
+# https://hub.docker.com/_/python
+FROM python:3.8-buster
+
+#Download the desired package(s) for Microsoft ODBC 17 Driver
+RUN (curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -) && \
+        curl https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list && \
+        apt-get update && ACCEPT_EULA=Y apt-get install -y msodbcsql17  unixodbc-dev
+
+# Copy application dependency manifests to the container image.
+# Copying this separately prevents re-running pip install on every code change.
+COPY requirements.txt ./
+
+# Install production dependencies.
+RUN pip install -r requirements.txt && \
+         pip install gunicorn
+
+# Copy local code to the container image.
+ENV APP_HOME /app
+WORKDIR $APP_HOME
+COPY . ./
+
+
+# Run the web service on container startup. Here we use the gunicorn
+# webserver, with one worker process and 8 threads.
+# For environments with multiple CPU cores, increase the number of workers
+# to be equal to the cores available.
+CMD exec gunicorn --bind :$PORT --workers 1 --threads 8 main:app
diff --git a/cloud-sql/sql-server/sqlalchemy/README.md b/cloud-sql/sql-server/sqlalchemy/README.md
@@ -0,0 +1,83 @@
+# Connecting to Cloud SQL - SQL Server
+
+## Before you begin
+
+1. If you haven't already, set up a Python Development Environment by following the [python setup guide](https://cloud.google.com/python/setup) and 
+[create a project](https://cloud.google.com/resource-manager/docs/creating-managing-projects#creating_a_project).
+
+1. [Create a Google Cloud SQL "SQL Server" instance](
+    https://console.cloud.google.com/sql/choose-instance-engine).
+
+6.  Under the instance's "USERS" tab, create a new user. Note the "User name" and "Password".
+
+7.  Create a new database in your Google Cloud SQL instance.
+
+    1.  List your database instances in [Cloud Cloud Console](
+        https://console.cloud.google.com/sql/instances/).
+
+    2.  Click your Instance Id to see Instance details.
+
+    3.  Click DATABASES.
+
+    4.  Click **Create database**.
+
+    2.  For **Database name**, enter `votes`.
+
+    3.  Click **CREATE**.
+
+1. Install the version of [Microsoft ODBC 17 Driver for SQL Server](https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15_) for your operating system.
+
+1. Create a service account with the 'Cloud SQL Client' permissions by following these 
+[instructions](https://cloud.google.com/sql/docs/postgres/connect-external-app#4_if_required_by_your_authentication_method_create_a_service_account).
+Download a JSON key to use to authenticate your connection. 
+
+1. Use the information noted in the previous steps:
+```bash
+export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service/account/key.json
+export CLOUD_SQL_CONNECTION_NAME='<MY-PROJECT>:<INSTANCE-REGION>:<INSTANCE-NAME>'
+export DB_USER='my-db-user'
+export DB_PASS='my-db-pass'
+export DB_NAME='my_db'
+```
+Note: Saving credentials in environment variables is convenient, but not secure - consider a more
+secure solution such as [Cloud KMS](https://cloud.google.com/kms/) to help keep secrets safe.
+
+## Running locally
+
+To run this application locally, download and install the `cloud_sql_proxy` by
+following the instructions [here](https://cloud.google.com/sql/docs/mysql/sql-proxy#install). 
+
+Then, use the following command to start the proxy in the
+background using TCP:
+```bash
+./cloud_sql_proxy -instances=${CLOUD_SQL_CONNECTION_NAME}=tcp:1433 sqlserver -u ${DB_USER} --host 127.0.0.1
+```
+
+Next, setup install the requirements into a virtual enviroment:
+```bash
+virtualenv --python python3 env
+source env/bin/activate
+pip install -r requirements.txt
+```
+
+Finally, start the application:
+```bash
+python main.py
+```
+
+Navigate towards `http://127.0.0.1:8080` to verify your application is running correctly.
+
+## Deploy to App Engine Flexible
+
+App Engine Flexible supports connecting to your SQL Server instance through TCP
+
+First, update `app.yaml` with the correct values to pass the environment 
+variables and instance name into the runtime.
+
+Then, make sure that the service account `service-{PROJECT_NUMBER}>@gae-api-prod.google.com.iam.gserviceaccount.com` has the IAM role `Cloud SQL Client`.
+
+Next, the following command will deploy the application to your Google Cloud project:
+```bash
+gcloud beta app deploy
+```
+
diff --git a/cloud-sql/sql-server/sqlalchemy/app.yaml b/cloud-sql/sql-server/sqlalchemy/app.yaml
@@ -0,0 +1,28 @@
+# Copyright 2020 Google LLC
+#
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+#     http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+
+runtime: custom
+env: flex
+
+# Remember - storing secrets in plaintext is potentially unsafe. Consider using
+# something like https://cloud.google.com/secret-manager/docs to help keep secrets secret.
+env_variables:
+  DB_USER: <your-username>
+  DB_PASS: <your-password>
+  DB_NAME: <your-db-name>
+  # Whether the app is deployed or running locally
+  DEPLOYED: true
+
+beta_settings:
+  cloud_sql_instances: <project-name>:<region-name>:<instance-name>=tcp:1433
diff --git a/cloud-sql/sql-server/sqlalchemy/main.py b/cloud-sql/sql-server/sqlalchemy/main.py
@@ -0,0 +1,169 @@
+# Copyright 2020 Google LLC
+#
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+#     http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+
+import datetime
+import logging
+import os
+
+from flask import Flask, render_template, request, Response
+import sqlalchemy
+from sqlalchemy import Table
+from sqlalchemy import Column
+from sqlalchemy import DateTime, Integer, String
+
+app = Flask(__name__)
+
+logger = logging.getLogger()
+
+# [START cloud_sql_server_sqlalchemy_create]
+# Remember - storing secrets in plaintext is potentially unsafe. Consider using
+# something like https://cloud.google.com/kms/ to help keep secrets secret.
+db_user = os.environ.get("DB_USER")
+db_pass = os.environ.get("DB_PASS")
+db_name = os.environ.get("DB_NAME")
+
+# When deployed to GAE Flex for TCP, use "172.17.0.1" to connect
+host = "172.17.0.1" if os.environ.get("DEPLOYED") else "127.0.0.1"
+
+# The SQLAlchemy engine will help manage interactions, including automatically
+# managing a pool of connections to your database
+db = sqlalchemy.create_engine(
+    # Equivalent URL:
+    # mssql+pyodbc://<db_user>:<db_pass>@/<host>:<port>/<db_name>?driver=ODBC+Driver+17+for+SQL+Server
+    sqlalchemy.engine.url.URL(
+        "mssql+pyodbc",
+        username=db_user,
+        password=db_pass,
+        database=db_name,
+        host=host,
+        port=1433,
+        query={"driver": "ODBC Driver 17 for SQL Server"},
+    ),
+    # ... Specify additional properties here.
+    # [START_EXCLUDE]
+    # [START cloud_sql_server_sqlalchemy_limit]
+    # Pool size is the maximum number of permanent connections to keep.
+    pool_size=5,
+    # Temporarily exceeds the set pool_size if no connections are available.
+    max_overflow=2,
+    # The total number of concurrent connections for your application will be
+    # a total of pool_size and max_overflow.
+    # [END cloud_sql_server_sqlalchemy_limit]
+    # [START cloud_sql_server_sqlalchemy_backoff]
+    # SQLAlchemy automatically uses delays between failed connection attempts,
+    # but provides no arguments for configuration.
+    # [END cloud_sql_server_sqlalchemy_backoff]
+    # [START cloud_sql_server_sqlalchemy_timeout]
+    # 'pool_timeout' is the maximum number of seconds to wait when retrieving a
+    # new connection from the pool. After the specified amount of time, an
+    # exception will be thrown.
+    pool_timeout=30,  # 30 seconds
+    # [END cloud_sql_server_sqlalchemy_limit]
+    # [START cloud_sql_server_sqlalchemy_lifetime]
+    # 'pool_recycle' is the maximum number of seconds a connection can persist.
+    # Connections that live longer than the specified amount of time will be
+    # reestablished
+    pool_recycle=1800,  # 30 minutes
+    # [END cloud_sql_server_sqlalchemy_lifetime]
+    echo=True  # debug
+    # [END_EXCLUDE]
+)
+# [END cloud_sql_server_sqlalchemy_create]
+
+
+@app.before_first_request
+def create_tables():
+    # Create tables (if they don't already exist)
+    if not db.has_table("votes"):
+        metadata = sqlalchemy.MetaData(db)
+        Table(
+            "votes",
+            metadata,
+            Column("vote_id", Integer, primary_key=True, nullable=False),
+            Column("time_cast", DateTime, nullable=False),
+            Column("candidate", String(6), nullable=False),
+        )
+        metadata.create_all()
+
+
+@app.route("/", methods=["GET"])
+def index():
+    votes = []
+    with db.connect() as conn:
+        # Execute the query and fetch all results
+        recent_votes = conn.execute(
+            "SELECT TOP(5) candidate, time_cast FROM votes "
+            "ORDER BY time_cast DESC"
+        ).fetchall()
+        # Convert the results into a list of dicts representing votes
+        for row in recent_votes:
+            votes.append({"candidate": row[0], "time_cast": row[1]})
+
+        stmt = sqlalchemy.text(
+            "SELECT COUNT(vote_id) FROM votes WHERE candidate=:candidate"
+        )
+        # Count number of votes for tabs
+        tab_result = conn.execute(stmt, candidate="TABS").fetchone()
+        tab_count = tab_result[0]
+        # Count number of votes for spaces
+        space_result = conn.execute(stmt, candidate="SPACES").fetchone()
+        space_count = space_result[0]
+
+    return render_template("index.html", recent_votes=votes,
+                           tab_count=tab_count, space_count=space_count)
+
+
+@app.route("/", methods=["POST"])
+def save_vote():
+    # Get the team and time the vote was cast.
+    team = request.form["team"]
+    time_cast = datetime.datetime.utcnow()
+    # Verify that the team is one of the allowed options
+    if team != "TABS" and team != "SPACES":
+        logger.warning(team)
+        return Response(response="Invalid team specified.", status=400)
+
+    # [START cloud_sql_server_sqlalchemy_connection]
+    # Preparing a statement before hand can help protect against injections.
+    stmt = sqlalchemy.text(
+        "INSERT INTO votes (time_cast, candidate)"
+        " VALUES (:time_cast, :candidate)"
+    )
+    try:
+        # Using a with statement ensures that the connection is always released
+        # back into the pool at the end of statement (even if an error occurs)
+        with db.connect() as conn:
+            conn.execute(stmt, time_cast=time_cast, candidate=team)
+    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.
+        # [START_EXCLUDE]
+        logger.exception(e)
+        return Response(
+            status=500,
+            response="Unable to successfully cast vote! Please check the "
+            "application logs for more details.",
+        )
+        # [END_EXCLUDE]
+    # [END cloud_sql_server_sqlalchemy_connection]
+
+    return Response(
+        status=200,
+        response="Vote successfully cast for '{}' at time {}!".format(
+                    team, time_cast),
+    )
+
+
+if __name__ == "__main__":
+    app.run(host="127.0.0.1", port=8080, debug=True)
diff --git a/cloud-sql/sql-server/sqlalchemy/requirements-test.txt b/cloud-sql/sql-server/sqlalchemy/requirements-test.txt
@@ -0,0 +1 @@
+pytest==5.3.2
diff --git a/cloud-sql/sql-server/sqlalchemy/requirements.txt b/cloud-sql/sql-server/sqlalchemy/requirements.txt
@@ -0,0 +1,4 @@
+Flask==1.1.1
+SQLAlchemy==1.3.13
+pyodbc==4.0.30
+