Using Google Cloud SQL with App Engine

Python | Java | PHP | Go

This document describes how to use Google Cloud SQL instances with the App Engine Python SDK.

To learn more about Google Cloud SQL, see the Google Cloud SQL documentation.

If you haven't already created a Google Cloud SQL instance, the first thing you need to do is create one.

Creating a Cloud SQL Instance

A Cloud SQL instance is equivalent to a server. A single Cloud SQL instance can contain multiple databases. Follow these steps to create a Google Cloud SQL instance:

Sign into the Google Developers Console .
Create a new project, or open an existing project.
From within a project, select Cloud SQL to open the Cloud SQL control panel for that project.
Click New Instance to create a new Cloud SQL instance in your project, and configure your size, billing and replication options.
- More information on Cloud SQL billing options and instance sizes
- More information on Cloud SQL replication options
You'll notice that the App Engine application associated with your current project is already authorized to access this new instance. For more information on app authorization see the Access Control topic in the Cloud SQL docs.

That's it! You can now connect to your Google Cloud SQL instance from within your app, or any of these other methods .

MySQL case sensitivity

When you are creating or using databases and tables, keep in mind that all identifiers in Google Cloud SQL are case-sensitive. This means that all tables and databases are stored with the same name and case that was specified at creation time. When you try to access your databases and tables, make sure that you are using the exact database or table name.

For example, if you create a database named PersonsDatabase, you will not be able to reference the database using any other variation of that name, such as personsDatabase or personsdatabase. For more information about identifier case sensitivity, see the MySQL documentation.

Build a starter application and database

The easiest way to build an App Engine application that accesses Google Cloud SQL is to create a starter application then modify it. This section leads you through the steps of building an application that displays a web form that lets users read and write entries to a guestbook database. The sample application demonstrates how to read and write to a Google Cloud SQL instance.

Step 1: Create your App Engine sample application

Follow the instructions for the Hello World! chapter of the Python Getting Started guide to create a simple App Engine application.

Step 2: Grant your App Engine application access to the Google Cloud SQL instance

You can grant individual Google App Engine applications access to a Google Cloud SQL instance. One application can be granted access to multiple instances, and multiple applications can be granted access to a particular instance. To grant access to a Google App Engine application, you need its application ID which can be found at the Google App Engine administration console under the Applications column.

Note : An App Engine application must be in the same region (either EU or US) as a Google Cloud SQL instance to be authorized to access that Google Cloud SQL instance.

To grant an App Engine application access to a Google Cloud SQL instance:

Go to the Google Developers Console .
Select a project by clicking the project name.
In the left sidebar of your project, click Cloud SQL .
Click the name of the instance to which you want to grant access.
In the instance dashboard, click Edit .
In the Instance settings window, enter your Google App Engine application ID in the Authorized App Engine applications section. You can grant access to multiple applications, by entering them one at a time.

Note : In order to improve performance, the Google Cloud SQL instance will be kept as close as possible to the first App Engine application on the list, so this should be the application whose performance is most important.
Click Confirm to apply your changes.

After you have added authorized applications to your Google Cloud SQL instance, you can view a list of these applications in the instance dashboard, in the section titled Applications .

Step 3: Create your database and table

For example, you can use MySQL Client to run the following commands:

Create a new database called guestbook using the following SQL statement:
```
CREATE DATABASE guestbook;
```
Inside the guestbook database create a table called entries with columns for the guest name, the message content, and a random ID, using the following statement:
```
CREATE TABLE guestbook.entries (
  entryID INT NOT NULL AUTO_INCREMENT,
  guestName VARCHAR(255),
  content VARCHAR(255),
  PRIMARY KEY(entryID)
);
```

After you have set up a bare-bones application, you can modify it and deploy it.

Connect to your database

Import the MySQLdb module

Google Cloud SQL supports connections using the MySQLdb module, which is the de facto way to connect to MySQL in Python. MySQLdb implements PEP 249 (the same as implemented by the custom Google driver, google.appengine.api.rdbms ) and also provides access to the _mysql module which implements the MySQL C API. For more information, see the MySQLdb User's Guide . We recommend that you use the MySQLdb module whenever possible.

Before you can write any Python applications with Google Cloud SQL, you need to import the MySQLdb module by adding import MySQLdb to your source code. Copy and paste the following code into your helloworld.py file.

import cgi
import webapp2
from google.appengine.ext.webapp.util import run_wsgi_app

import MySQLdb
import os
import jinja2

The webapp2 module provides an application framework to simplify development, the run_wsgi_app and cgi modules provide Common Gateway Interface (CGI) support, the jinja2 module provides HTML templating, and the os module provides access to environment variables.

Connect, post, and get from your database

In this section, we show you how to continue to modify your helloworld.py file to connect, post, and get data from your Cloud SQL database. In the code, replace your-instance-name with your Google Cloud SQL instance name and `your-project-id' with the literal project ID.

This code performs the following actions:

The MainPage class:
- connects to the guestbook database and querying it for all rows in the entries table
- prints all the rows in an HTML table
- provides a web form for users to POST to the guestbook
The Guestbook class:
- grabs the values of the form fields from MainPage
- connects to the guestbook database and inserting the form values
- redirects the user to back to the MainPage

You access a Cloud SQL instance by using a Unix socket with the prefix /cloudsql/ . The code below can be used to run in both production and on dev_appserver (using a local MySQL server).

import cgi
import webapp2
from google.appengine.ext.webapp.util import run_wsgi_app

import MySQLdb
import os
import jinja2

# Configure the Jinja2 environment.
JINJA_ENVIRONMENT = jinja2.Environment(
  loader=jinja2.FileSystemLoader(os.path.dirname(__file__)),
  autoescape=True,
  extensions=['jinja2.ext.autoescape'])

# Define your production Cloud SQL instance information.
_INSTANCE_NAME = 'your-project-id:your-instance-name'

class MainPage(webapp2.RequestHandler):
    def get(self):
        # Display existing guestbook entries and a form to add new entries.
        if (os.getenv('SERVER_SOFTWARE') and
            os.getenv('SERVER_SOFTWARE').startswith('Google App Engine/')):
            db = MySQLdb.connect(unix_socket='/cloudsql/' + _INSTANCE_NAME, db='guestbook', user='root')
        else:
            db = MySQLdb.connect(host='127.0.0.1', port=3306, db='guestbook', user='root')
            # Alternatively, connect to a Google Cloud SQL instance using:
            # db = MySQLdb.connect(host='ip-address-of-google-cloud-sql-instance', port=3306, user='root')

        cursor = db.cursor()
        cursor.execute('SELECT guestName, content, entryID FROM entries')

        # Create a list of guestbook entries to render with the HTML.
        guestlist = [];
        for row in cursor.fetchall():
          guestlist.append(dict([('name',cgi.escape(row[0])),
                                 ('message',cgi.escape(row[1])),
                                 ('ID',row[2])
                                 ]))

        variables = {'guestlist': guestlist}
        template = JINJA_ENVIRONMENT.get_template('main.html')
        self.response.write(template.render(variables))
        db.close()

class Guestbook(webapp2.RequestHandler):
    def post(self):
        # Handle the post to create a new guestbook entry.
        fname = self.request.get('fname')
        content = self.request.get('content')

        if (os.getenv('SERVER_SOFTWARE') and
            os.getenv('SERVER_SOFTWARE').startswith('Google App Engine/')):
            db = MySQLdb.connect(unix_socket='/cloudsql/' + _INSTANCE_NAME, db='guestbook', user='root')
        else:
            db = MySQLdb.connect(host='127.0.0.1', port=3306, db='guestbook', user='root')
            # Alternatively, connect to a Google Cloud SQL instance using:
            # db = MySQLdb.connect(host='ip-address-of-google-cloud-sql-instance', port=3306, db='guestbook', user='root')

        cursor = db.cursor()
        # Note that the only format string supported is %s
        cursor.execute('INSERT INTO entries (guestName, content) VALUES (%s, %s)', (fname, content))
        db.commit()
        db.close()

        self.redirect("/")

application = webapp2.WSGIApplication([('/', MainPage),
                               ('/sign', Guestbook)],
                              debug=True)

def main():
    application = webapp2.WSGIApplication([('/', MainPage),
                                           ('/sign', Guestbook)],
                                          debug=True)
    run_wsgi_app(application)

if __name__ == "__main__":
    main()

The following HTML file ( main.html ) is referenced in the MainPage class. It uses the JinJa2 templating system to keep the HTML and code separate.

<!DOCTYPE html>
<html>
  <head>
    <title>My Guestbook!</title>
  </head>
  <body>
    <body>
      <table style="border: 1px solid black">
        <tbody>
          <tr>
            <th width="35%" style="background-color: #CCFFCC; margin: 5px">Name</th>
            <th style="background-color: #CCFFCC; margin: 5px">Message</th>
            <th style="background-color: #CCFFCC; margin: 5px">ID</th>
          </tr>
          {% for guest in guestlist %}
             <tr>
               <td>{{ guest['name'] }}</td>
               <td>{{ guest['message'] }}</td>
               <td>{{ guest['ID'] }}</td>
             </tr>
          {% endfor %}
        </tbody>
      </table>
      <br /> No more messages!
      <br /><strong>Sign the guestbook!</strong>
      <form action="/sign" method="post">
        <div>First Name: <input type="text" name="fname" style="border: 1px solid black"></div>
        <div>Message: <br /><textarea name="content" rows="3" cols="60"></textarea></div>
        <div><input type="submit" value="Sign Guestbook"></div>
      </form>
  </body>
</html>

The example above connects to the Google Cloud SQL instance as the root user but you can connect to the instance as a specific database user with the following parameters:

db = MySQLdb.connect(unix_socket='/cloudsql/' + _INSTANCE_NAME, db='database', user='user', passwd='password')

For information about creating MySQL users, see Adding Users in the MySQL documentation.

Managing connections

An App Engine application is made up of one or more modules . Each module consists of source code and configuration files. An instance instantiates the code which is included in an App Engine module, and a particular version of module will have one or more instances running. The number of instances running depends on the number of incoming requests. You can configure App Engine to scale the number of instances automatically in response to processing volume (see Instance scaling and class ).

When App Engine instances talk to Google Cloud SQL, each App Engine instance cannot have more than 12 concurrent connections to a Cloud SQL instance. Always close any established connections before finishing processing a request. Not closing a connection will cause it to leak and may eventually cause new connections to fail. You can exit this state by shutting down the affected App Engine instance.

You should also keep in mind that there is also a maximum number of concurrent connections and queries for each Cloud SQL instance, depending on the tier (see Cloud SQL pricing ). For guidance on managing connections, see How should I manage connections? in the "Google Cloud SQL FAQ" document.

Update your configuration file

In your app.yaml file, you need to make a few changes. First, change the value of the application field to the application ID of your App Engine application. Second, enable the MySQLdb library for your application. App Engine already contains the MySQLdb library, you only need to make the app.yaml addition to use it. Finally, make sure you to load the jinja2 module.

libraries:
- name: MySQLdb
  version: "latest"

- name: jinja2
  version: "latest"

That's it! Now you can deploy your application and try it out!

The Google App Engine SDK for Python doesn’t contain the MySQLdb library, so you need to install it manually if you want to use it. On Ubuntu/Debian, you can install it by running the following command:

sudo apt-get install python-mysqldb

A complete example is shown in the Complete MySQLdb Python example in the appendix and also at https://github.com/GoogleCloudPlatform/appengine-cloudsql-native-mysql-demo-python . The example also shows how to use the low-level MySQLdb._mysql .

Using a local MySQL instance during development

The Python Development Server in the Google App Engine SDK can use a locally-installed MySQL server instance to closely mirror the Google Cloud SQL environment during development.

Install MySQL

Visit MySQL.com to download the MySQL Community Server. Linux users with apt-get can run:

sudo apt-get install mysql-server

You must also install the MySQLdb library . Linux users on a distribution with apt-get can run:

sudo apt-get install python-mysqldb

Run your application on the development server

Before you can connect, create a user and a database in your local instance if these weren't created during installation. See the MySQL Reference Manual for more information.

When you're ready to run your application on the Python Development Server, use the dev_appserver.py command. For example:

dev_appserver.py myapp

If you want to run your local MySQL server on a different host, pass the --mysql_host and --mysql_port flags, and, optionally, the --mysql_socket flag.

Size and access limits

The following limits apply to Google Cloud SQL:

Google App Engine Limits

Google App Engine applications are also subject to additional Google App Engine quotas and limits. Requests from Google App Engine applications to Google Cloud SQL are subject to the following time limits:

All database requests must finish within the HTTP request timer , around 60 seconds .
Offline requests like cron tasks have a time limit of 10 minutes.
Backend requests to Google Cloud SQL have a time limit of 10 minutes.
Each App Engine instance cannot have more than 12 concurrent connections to a Cloud SQL instance.

App Engine-specific quotas and access limits are discussed on the Quotas page.

Complete MySQLdb Python example

This example includes three files:

app.py - connects to a Cloud SQL instance and runs the SHOW VARIABLES command.
app_mysql.py - does the same thing as app.py but uses the lower level _mysql interface.
app.yaml - defines the App Engine Python application.

app.py

import MySQLdb
import os
import webapp2

class IndexPage(webapp2.RequestHandler):
  def get(self):
    self.response.headers['Content-Type'] = 'text/plain'

    if (os.getenv('SERVER_SOFTWARE') and
      os.getenv('SERVER_SOFTWARE').startswith('Google App Engine/')):
      db = MySQLdb.connect(unix_socket='/cloudsql/your-project-id:your-instance-name', user='root')
    else:
      db = MySQLdb.connect(host='localhost', user='root')

    cursor = db.cursor()
    cursor.execute('SHOW VARIABLES')
    for r in cursor.fetchall():
      self.response.write('%s\n' % str(r))

    db.close()

app = webapp2.WSGIApplication([
    ('/', IndexPage),
    ])

app_mysql.py

import MySQLdb
import _mysql
import os
import webapp2

class Page(webapp2.RequestHandler):
  def get(self):
    self.response.headers['Content-Type'] = 'text/plain'

    if (os.getenv('SERVER_SOFTWARE') and
      os.getenv('SERVER_SOFTWARE').startswith('Google App Engine/')):
      db = MySQLdb.connect(unix_socket='/cloudsql/your-project-id:your-instance-name', user='root')
    else:
      db = MySQLdb.connect(host='localhost', user='root')

    db.query('SHOW VARIABLES')
    result = db.store_result()
    while True:
      row = result.fetch_row()
      if row:
        self.response.write('%s\n' % str(row[0]))
      else:
        break

    db.close()

app = webapp2.WSGIApplication([
    ('/mysql', Page),
    ])

app.yaml

application: your-app-id
version: 1
runtime: python27
api_version: 1
threadsafe: yes

handlers:
- url: /
  script:app.app

- url: /mysql
  script: app_mysql.app

libraries:
- name: MySQLdb
  version: "latest"

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License , and code samples are licensed under the Apache 2.0 License . For details, see our Site Policies .

Last updated July 11, 2014.

Python