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 2nd Gen Cloud SQL Instance by following these

[instructions](https://cloud.google.com/sql/docs/postgres/create-instance). Note the connection

string, database user, and database password that you create.

1. Create a database for your application by following these

[instructions](https://cloud.google.com/sql/docs/postgres/create-manage-databases). Note the database

name.

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:

export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service/account/key.json

export CLOUD_SQL_CONNECTION_NAME='<MY-PROJECT>:<INSTANCE-REGION>:<MY-DATABASE>'

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.

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).

Once the proxy is ready, use the following command to start the proxy in the

background:

./cloud_sql_proxy -dir=/cloudsql --instances=$CLOUD_SQL_CONNECTION_NAME --credential_file=$GOOGLE_APPLICATION_CREDENTIALS

Note: Make sure to run the command under a user with write access in the

`/cloudsql` directory. This proxy will use this folder to create a unix socket

the application will use to connect to Cloud SQL.

Next, setup install the requirements into a virtual enviroment:

virtualenv --python python3 env

source env/bin/activate

pip install -r requirements.txt

Finally, start the application:

python main.py

Navigate towards `http://127.0.0.1:8080` to verify your application is running correctly.

To run on GAE-Standard, create an App Engine project by following the setup for these

[instructions](https://cloud.google.com/appengine/docs/standard/python3/quickstart#before-you-begin).

First, update `app.yaml` with the correct values to pass the environment

variables into the runtime.

Next, the following command will deploy the application to your Google Cloud project:

gcloud app deploy

runtime: python37

env_variables:

CLOUD_SQL_INSTANCE_NAME: <MY-PROJECT>:<INSTANCE-REGION>:<MY-DATABASE>

DB_USER: my-db-user

DB_PASS: my-db-pass

DB_NAME: my_db

import datetime

import logging

import os

from flask import Flask, render_template, request, Response

import sqlalchemy

db_user = os.environ.get("DB_USER")

db_pass = os.environ.get("DB_PASS")

db_name = os.environ.get("DB_NAME")

cloud_sql_instance_name = os.environ.get("CLOUD_SQL_INSTANCE_NAME")

app = Flask(__name__)

logger = logging.getLogger()

db = sqlalchemy.create_engine(

# Equivalent URL:

# postgres+pg8000://<db_user>:<db_pass>@/<db_name>?unix_socket=/cloudsql/<cloud_sql_instance_name>

sqlalchemy.engine.url.URL(

drivername='postgres+pg8000',

username=db_user,

password=db_pass,

database=db_name,

query={

'unix_sock': '/cloudsql/{}'.format(cloud_sql_instance_name)

}

),

# ... Specify additional properties here.

# [START_EXCLUDE]

# [START cloud_sql_postgres_limit_connections]

# 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_postgres_limit_connections]

# [START cloud_sql_postgres_connection_backoff]

# SQLAlchemy automatically uses delays between failed connection attempts,

# but provides no arguments for configuration.

# [END cloud_sql_postgres_connection_backoff]

# [START cloud_sql_postgres_connection_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_postgres_connection_timeout]

# [START cloud_sql_postgres_connection_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_postgres_connection_lifetime]

# [END_EXCLUDE]

)

def create_tables():

# Create tables (if they don't already exist)

with db.connect() as conn:

conn.execute(

"CREATE TABLE IF NOT EXISTS votes "

"( vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL, "

"candidate CHAR(6) NOT NULL, PRIMARY KEY (vote_id) );"

)

def index():

votes = []

with db.connect() as conn:

# Execute the query and fetch all results

recent_votes = conn.execute(

"SELECT candidate, time_cast FROM votes "

"ORDER BY time_cast DESC LIMIT 5"

).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

)

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_postgres_example_statement]

# 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_postgres_example_statement]

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)

<html lang="en">

<title>Tabs VS Spaces</title>

<link rel="stylesheet"

href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">

<link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">

<script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>

<nav class="red lighten-1">

<div class="nav-wrapper">

<a href="#" class="brand-logo center">Tabs VS Spaces</a>

</div>

<div class="section">

<div class="center">

<h4>

{% if tab_count == space_count %}

TABS and SPACES are evenly matched!

{% elif tab_count > space_count %}

TABS are winning by {{tab_count - space_count}}

{{'votes' if tab_count - space_count > 1 else 'vote'}}!

{% elif space_count > tab_count %}

SPACES are winning by {{space_count - tab_count}}

{{'votes' if space_count - tab_count > 1 else 'vote'}}!

{% endif %}

</h4>

</div>

<div class="row center">

<div class="col s6 m5 offset-m1">

<div class="card-panel {{'green lighten-3' if tab_count > space_count}}">

<i class="material-icons large">keyboard_tab</i>

<h3>{{tab_count}} votes</h3>

<button id="voteTabs" class="btn green">Vote for TABS</button>

</div>

</div>

<div class="col s6 m5">

<div class="card-panel {{'blue lighten-3' if tab_count < space_count}}">

<i class="material-icons large">space_bar</i>

<h3>{{space_count}} votes</h3>

<button id="voteSpaces" class="btn blue">Vote for SPACES</button>

</div>

</div>

</div>

<h4 class="header center">Recent Votes</h4>

<ul class="container collection center">

{% for vote in recent_votes %}

<li class="collection-item avatar">

{% if vote.candidate == "TABS" %}

<i class="material-icons circle green">keyboard_tab</i>

{% elif vote.candidate == "SPACES" %}

<i class="material-icons circle blue">space_bar</i>

{% endif %}

<span class="title">

A vote for <b>{{vote.candidate}}</b>

</span>

<p>was cast at {{vote.time_cast}}</p>

</li>

{% endfor %}

</ul>

function vote(team) {

var xhr = new XMLHttpRequest();

xhr.onreadystatechange = function () {

if (this.readyState == 4) {

if (!window.alert(this.responseText)) {

window.location.reload();

}

}

};

xhr.open("POST", "/", true);

xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");

xhr.send("team=" + team);

}

document.getElementById("voteTabs").addEventListener("click", function () {

vote("TABS");

});

document.getElementById("voteSpaces").addEventListener("click", function () {

vote("SPACES");

});