Using Cloud SQL with Python

This page of the Bookshelf app tutorial shows how the sample app stores its persistent data, metadata for books, in Cloud SQL.

This page is part of a multipage tutorial. To start from the beginning and read the setup instructions, go to Python Bookshelf app.

Creating a Cloud SQL instance and database

When deployed, your app uses the Cloud SQL Proxy that is built in to the App Engine environment to communicate with your Cloud SQL instance. However, to test your app locally, you must install and use a local copy of the proxy in your development environment.

Learn more about the Cloud SQL Proxy.

To perform basic admin tasks on your Cloud SQL instance, you can use the MySQL client.

Enable the Cloud SQL Admin API

Before using Cloud SQL, you must enable the Cloud SQL Admin API:

gcloud services enable sqladmin

Installing the Cloud SQL Proxy

Download and install the Cloud SQL Proxy. The Cloud SQL Proxy connects to your Cloud SQL instance when running locally.

Linux 64-bit

  1. Download the proxy:
    wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
    
  2. Make the proxy executable:
    chmod +x cloud_sql_proxy
    

Linux 32-bit

  1. Download the proxy:
    wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.386 -O cloud_sql_proxy
    
  2. Make the proxy executable:
    chmod +x cloud_sql_proxy
    

macOS 64-bit

  1. Download the proxy:
    curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.amd64
    
  2. Make the proxy executable:
    chmod +x cloud_sql_proxy
    

macOS 32-bit

  1. Download the proxy:
    curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.386
    
  2. Make the proxy executable:
    chmod +x cloud_sql_proxy
    

Windows 64-bit

Right-click https://dl.google.com/cloudsql/cloud_sql_proxy_x64.exe and select Save Link As to download the proxy. Rename the file to cloud_sql_proxy.exe.

Windows 32-bit

Right-click https://dl.google.com/cloudsql/cloud_sql_proxy_x86.exe and select Save Link As to download the proxy. Rename the file to cloud_sql_proxy.exe.
If your operating system isn't included here, you can also compile the proxy from source.

Creating a Cloud SQL instance

  1. Create a Cloud SQL for MySQL Second Generation instance.

    Name the instance library or similar. It can take a few minutes for the instance to be ready. When the instance is ready, it's visible in the instances list.

  2. Use the Cloud SDK to run the following command where [YOUR_INSTANCE_NAME] represents the name of your Cloud SQL instance:
    gcloud sql instances describe [YOUR_INSTANCE_NAME]

    In the output, note the value shown for [CONNECTION_NAME].

    The [CONNECTION_NAME] value is in the format [PROJECT_NAME]:[REGION_NAME]:[INSTANCE_NAME].

Initializing your Cloud SQL instance

  1. Start the Cloud SQL Proxy by using the [CONNECTION_NAME] value from the previous step:

    Linux/macOS

    ./cloud_sql_proxy -instances="[YOUR_INSTANCE_CONNECTION_NAME]"=tcp:3306

    Windows

    cloud_sql_proxy.exe -instances="[YOUR_INSTANCE_CONNECTION_NAME]"=tcp:3306

    Replace [YOUR_INSTANCE_CONNECTION_NAME] with the [CONNECTION_NAME] value that you recorded in the previous step.

    This step establishes a connection from your local computer to your Cloud SQL instance for local testing purposes. Keep the Cloud SQL Proxy running the entire time you test your app locally.

  2. Create a Cloud SQL user and database:

    GCP Console

    1. Create a new database by using the GCP Console for your Cloud SQL instance library. For example, you can use the name bookshelf.
    2. Create a new user by using the GCP Console for your Cloud SQL instance library.

    MySQL client

    1. In a separate command-line tab, use the MySQL client or similar program to connect to your instance. When prompted, use the root password you configured.
      mysql --host 127.0.0.1 --user root --password
    2. Create the required databases, users, and access permissions in your Cloud SQL database using the following commands. Replace [MYSQL_USER] and [MYSQL_PASSWORD] with the username and password you want to use.
      CREATE DATABASE bookshelf;
      CREATE USER '[MYSQL_USER]' IDENTIFIED BY '[MYSQL_PASSWORD]';
      GRANT ALL ON . TO '[MYSQL_USER]';

Configuring settings

This section uses code in the 2-structured-data directory. Edit the files and run commands in this directory.

  1. Open the config.py file for editing and replace the following values:
    • Set the value of [PROJECT_ID] to your project ID, which is visible in the GCP Console.
    • Set the value of [DATA_BACKEND] to cloudsql.
    • Set the value of [CLOUDSQL_USER], [CLOUDSQL_PASSWORD], and [CLOUDSQL_DATABASE] to the values you used when configuring the Cloud SQL instance. You can use the root user and root password configured while creating your instance.
    • Set the value of [CLOUDSQL_CONNECTION_NAME] to the connection name for your Cloud SQL instance. This connection name is visible in the Google Cloud Platform Console in the details for your Cloud SQL instance.

      The connection name value is in the format project:region:cloudsql-instance.

  2. Save and close the config.py file.

You need to also configure a setting in the app.yaml file before deploying:

  1. Open the app.yaml file for editing.
  2. Set the value of cloudsql-instance to the same value used for [CLOUDSQL_CONNECTION_NAME] in the config.py file. Use the format project:region:cloudsql-instance. Uncomment this entire line.
  3. Save and close the app.yaml file.

Installing dependencies

To create a virtual environment and install dependencies, use the following commands:

Linux/macOS

virtualenv -p python3 env
source env/bin/activate
pip install -r requirements.txt

Windows

virtualenv -p python3 env
env\scripts\activate
pip install -r requirements.txt

Creating the database tables

The app needs to create the database tables used to store the bookshelf data. Use the following command to connect to the Cloud SQL instance and create the necessary tables:

Linux/macOS

python bookshelf/model_cloudsql.py

Windows

python bookshelf\model_cloudsql.py

Running the app on your local machine

  1. Start a local web server:

    python main.py
    
  2. In your browser, enter the following address:

    http://localhost:8080
    
Now you can browse the app's web pages to add, edit, and delete books.

Press Control+C to exit the worker and then the local web server.

Deploying the app to the App Engine flexible environment

  1. Deploy the sample app:

    gcloud app deploy
    
  2. In your browser, enter the following address. Replace [YOUR_PROJECT_ID] with your GCP project ID:

    https://[YOUR_PROJECT_ID].appspot.com
    

If you update your app, you deploy the updated version by entering the same command that you used to deploy the app. The deployment creates a new version of your app and promotes it to the default version. The earlier versions of your app remain, as do their associated virtual machine (VM) instances. All of these app versions and VM instances are billable resources. To reduce costs, delete the non-default versions of your app.

To delete an app version:

  1. In the GCP Console, go to the Versions page for App Engine.

    Go to the Versions page

  2. Select the checkbox for the non-default app version you want to delete.
  3. Click Delete to delete the app version.

For more information about cleaning up billable resources, see the Cleaning up section in the final step of this tutorial.

App structure

Bookshelf app deployment process and structure

The app stores all persistent data in Cloud SQL.

Understanding the code

This section walks you through the app's code and explains how it works.

Handling user submissions with forms

The add/edit HTML form lets users add and edit book submissions in the app.

Image of add/edit form

The HTML form is created using Flask's default template engine, Jinja2, which is a Python template engine. The following Jinja2 template specifies that the form include text input fields for Title, Author, Date Published, and Description:

{% extends "base.html" %}

{% block content %}
<h3>{{action}} book</h3>

<form method="POST" enctype="multipart/form-data">

  <div class="form-group">
    <label for="title">Title</label>
    <input type="text" name="title" id="title" value="{{book.title}}" class="form-control"/>
  </div>

  <div class="form-group">
    <label for="author">Author</label>
    <input type="text" name="author" id="author" value="{{book.author}}" class="form-control"/>
  </div>

  <div class="form-group">
    <label for="publishedDate">Date Published</label>
    <input type="text" name="publishedDate" id="publishedDate" value="{{book.publishedDate}}" class="form-control"/>
  </div>

  <div class="form-group">
    <label for="description">Description</label>
    <textarea name="description" id="description" class="form-control">{{book.description}}</textarea>
  </div>

  <button type="submit" class="btn btn-success">Save</button>
</form>

{% endblock %}

Handling form submissions

When a user clicks Add Book, the crud.add view displays the form. When the user fills out the Add book form and then clicks Save, the same view handles the form's HTTP POST action. This action initiates the process of sending the submitted data to the Cloud SQL database by passing the data to the model.create function.

@crud.route('/add', methods=['GET', 'POST'])
def add():
    if request.method == 'POST':
        data = request.form.to_dict(flat=True)

        book = get_model().create(data)

        return redirect(url_for('.view', id=book['id']))

    return render_template("form.html", action="Add", book={})

The bookshelf/model_cloudsql.py file contains the code that performs CRUD functions for data stored in the Cloud SQL database. The SQL is created by using an Object Relational Mapper (ORM) called SQLAlchemy. Object relational mappers make it possible to interact with your data models as Python objects and have the equivalent SQL generated for you. Another popular ORM you might have worked with is the Django ORM.

To simplify integration with Flask, use the Flask extension Flask-SQLAlchemy.

For example, the create function in model_cloudsql.py updates the data model by converting attributes, which are passed in as a Python dictionary file, into keyword arguments for the Book constructor. The function then adds the model to the database session and commits the session. SQLAlchemy converts this commit to a SQL INSERT operation and creates a book entry in the database.

def create(data):
    book = Book(**data)
    db.session.add(book)
    db.session.commit()
    return from_sql(book)

When a user edits and submits a book's information, the update function in model_cloudsql.py queries for the existing entry by its id field. Then it iterates through the updated fields and edits them in the existing model. Finally, the function commits the session, and, in this case, SQLAlchemy converts the edit to a model as a SQL UPDATE operation.

def update(data, id):
    book = Book.query.get(id)
    for k, v in data.items():
        setattr(book, k, v)
    db.session.commit()
    return from_sql(book)

After users have added books, clicking the Books link navigates to the /books page, which lists all of the books currently stored in the Cloud SQL database. The list function lists all of the books using data retrieved from the Cloud SQL database.

def list(limit=10, cursor=None):
    cursor = int(cursor) if cursor else 0
    query = (Book.query
             .order_by(Book.title)
             .limit(limit)
             .offset(cursor))
    books = builtin_list(map(from_sql, query.all()))
    next_page = cursor + limit if len(books) == limit else None
    return (books, next_page)

Here you can see how to convert Python object methods to the appropriate SQL. In the preceding code, the limit method specifies the number of records to return from a database, which in turn adds a LIMIT clause to the SELECT SQL statement generated by the query. The offset method adds an OFFSET clause to the SELECT statement, which lets you specify a starting point for handling pagination and lets you request additional pages based on the value of cursor.

Was this page helpful? Let us know how we did:

Send feedback about...