Using Google Cloud SQL with App Engine Python SDK

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

Creating a Cloud SQL instance
Build a starter application and database
Connect to your database
Using a local MySQL instance during development
Size and access limits

For information about using Django with App Engine and Cloud SQL, see Django Support.

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 CloudSQL instance is equivalent to a server. A single CloudSQL instance can contain multiple databases. Follow these steps to create a Google Cloud SQL instance:

Sign into the Google Cloud Console.
Create a new Cloud Console project, or open an existing project.
From within a Cloud Console project, select Cloud SQL to open the CloudSQL control panel for that project.
Click New Instance to create a new CloudSQL instance in your project, and configure your size, billing and replication options.
- More information on CloudSQL billing options and instance sizes
- More information on CloudSQL 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 AppEngine 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:

From the Google Cloud SQL pane of the APIs Console, find the instance that you want to grant access to and click the name of your instance.
Click Google Cloud SQL in the left hand navigation to display the Google Cloud SQL overview, then click the name of the relevant instance.
Click the Instance settings button on the instance's dashboard.
In the Instance settings window that appears, enter your Google App Engine application ID under the Authorized 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 AppEngine application on the list, so this should be the application whose performance is most important.
Click Update instance 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's Dashboard, in the section titled Authorized Applications.

Step 3: Create your database and table

Using either the SQL prompt or the command line tool:

Create a new database called guestbook using the following SQL statement:
```
CREATE DATABASE guestbook;
```
Select your new database:
- If you're using the command line tool, select the new database using the following statement:
```
USE guestbook;
```
- If you're using the SQL prompt, select the new database from the drop down menu.
Create a table called entries with columns for the guest name, the message content, and a random ID, using the following statement:
```
CREATE TABLE entries (guestName VARCHAR(255), content VARCHAR(255), entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID));
```

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

Connect to your database

To connect your application to a Google Cloud SQL database:

Import the RDBMS module
Connect, post, and get from your database
Update your configuration file

Import the RDBMS module

Before you can write any Python applications with Google Cloud SQL, you need to import the RDBMS module. To do so, you need to add from google.appengine.api import rdbms to your source code. For this sample, copy and paste the following to your helloworld.py file. Note the bolded line where the RDBMS module is being imported:

import cgi
import webapp2
from google.appengine.api import rdbms

Connect, post, and get from your database

Google Cloud SQL is accessed using DB-API 2.0, Python's standard interface for relational databases.

Paste the following code sample in your helloworld.py file after your import statements. Replace YOUR_INSTANCE_NAME in the following code snippet with your Google Cloud SQL instance name.

This code is performing several actions:

The MainPage class is:
- connecting to the guestbook database and querying it for all rows in the entries table
- printing all the rows in an HTML table
- providing a web form for users to POST to the guestbook
The Guestbook class is:
- grabbing the values of the form fields from MainPage
- connecting to the guestbook database and inserting the form values
- redirecting the user to back to the MainPage

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

_INSTANCE_NAME = YOUR_INSTANCE_NAME

class MainPage(webapp2.RequestHandler):
    def get(self):
        conn = rdbms.connect(instance=_INSTANCE_NAME, database='guestbook')
        cursor = conn.cursor()
        cursor.execute('SELECT guestName, content, entryID FROM entries')
        self.response.out.write("""
          <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
          <html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
            <head>
               <title>My Guestbook!</title>
            </head>
            <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 row in cursor.fetchall():
          self.response.out.write('<tr><td>')
          self.response.out.write(cgi.escape(row[0]))
          self.response.out.write('</td><td>')
          self.response.out.write(cgi.escape(row[1]))
          self.response.out.write('</td><td>')
          self.response.out.write(row[2])
          self.response.out.write('</td></tr>')

        self.response.out.write("""
          </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>""")
        conn.close()

class Guestbook(webapp2.RequestHandler):
    def post(self):
        fname = self.request.get('fname')
        content = self.request.get('content')
        conn = rdbms.connect(instance=_INSTANCE_NAME, database='guestbook')
        cursor = conn.cursor()
        # Note that the only format string supported is %s
        cursor.execute('INSERT INTO entries (guestName, content) VALUES (%s, %s)', (fname, content))
        conn.commit()
        conn.close()

        self.redirect("/")

app = 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 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:

conn = rdbms.connect(instance='instance_name', database='database', user='user', password='password')

database: [Optional] The database to connect to.
user: [Optional] The database user to connect as.
password: [Optional] The user's password.

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

Update your configuration file

Open up your app.yaml file, and change the value of the application field to the application ID of your App Engine application.

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

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, passing --mysql_user and optionally --mysql_password. For example:

dev_appserver.py --mysql_user=username [--mysql_password=password] 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 size and QPS limits apply to Google Cloud SQL:

Resource	Limit
Maximum Request Size	16 MB
Maximum Response Size	16 MB

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.

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

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 August 8, 2013.

Google App Engine

Feedback on this document

Using Google Cloud SQL with App Engine Python SDK

Creating a Cloud SQL Instance

MySQL Case Sensitivity

Build a starter application and database

Step 1: Create your App Engine sample application

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

Step 3: Create your database and table

Connect to your database

Import the RDBMS module

Connect, post, and get from your database

Update your configuration file

Using a local MySQL instance during development

Install MySQL

Run your application on the development server

Size and access limits

Authentication required

Signing you in...

Google App Engine Feedback on this document

Using Google Cloud SQL with App Engine Python SDK

Creating a Cloud SQL Instance

MySQL Case Sensitivity

Build a starter application and database

Step 1: Create your App Engine sample application

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

Step 3: Create your database and table

Connect to your database

Import the RDBMS module

Connect, post, and get from your database

Update your configuration file

Using a local MySQL instance during development

Install MySQL

Run your application on the development server

Size and access limits

Authentication required

Signing you in...

Google App Engine

Feedback on this document