Install MySQL's server and Python client libraries to your local system. If you have a recent MacOSX system, MacPorts is an easy way to install. Here is an example command line for installing and setting up MySQL server 5.5 with MacPorts as of June 5, 2012.

$ sudo port install mysql55 mysql55-server

Edit the configuration file according to your needs and start the server. (Typically, you want to set the character set to utf8.)

$ sudo vi /opt/local/etc/mysql55/my.cnf
$ sudo /opt/local/share/mysql55/support-files/mysql.server start --timezone=UTC

Cloud SQL always runs with UTC, so add the '--timezone=UTC' option for consistency between the local dev environment and Cloud SQL environment.

It is always a good idea to change the password of ‘root’ user as follows:

$ /opt/local/lib/mysql55/bin/mysqladmin -u root password
New password:
Confirm new password:

Create a normal user for our application. (Don’t forget to replace ' username ' and ' password ' with your own username and password):

$ /opt/local/bin/mysql -u root mysql -p
…
mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON guestbook.* TO 'username'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

In this example, I only give a full permission on the database ‘guestbook’ to the user ' username '.

Type 'exit' to exit from the mysql command prompt once, enter the command prompt again as a new user, and create a database and a table for the guestbook application.

$ /opt/local/bin/mysql -u username -p
...
mysql> create database guestbook charset utf8;
Query OK, 1 row affected (0.00 sec)
mysql> use guestbook;
Database changed
mysql> CREATE TABLE entries
  (id int not null auto_increment primary key,
   guest_name varchar(255),
   content varchar(255),
   created_at timestamp)
  ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

Please replace variables like path names, username and password in this example with real values according to your MySQL installation and your preference.

To build a guestbook application, you need to create several files. Create app.yaml in your project directory with the following content, replacing your_application_id with a real application id:

application: your_application_id
version: 1
api_version: 1
runtime: python27
threadsafe: true

handlers:
- url: /.*
  script: main.application

libraries:
- name: jinja2
  version: latest

Then create the application file itself(main.py) as follows, with real username and password:

import os
import logging

from google.appengine.api import rdbms
from google.appengine.ext import webapp

import jinja2

template_path = os.path.join(os.path.dirname(__file__))

jinja2_env = jinja2.Environment(
    loader=jinja2.FileSystemLoader(template_path)
)

CLOUDSQL_INSTANCE = ''
DATABASE_NAME = 'guestbook'
USER_NAME = 'username'
PASSWORD = 'password'


def get_connection():
    return rdbms.connect(instance=CLOUDSQL_INSTANCE, database=DATABASE_NAME,
                         user=USER_NAME, password=PASSWORD, charset='utf8')


class MainHandler(webapp.RequestHandler):
    def get(self):
        # Viewing guestbook
        conn = get_connection()
        cursor = conn.cursor()
        cursor.execute('SELECT guest_name, content, created_at FROM entries '
                       'ORDER BY created_at DESC limit 20')
        rows = cursor.fetchall()
        conn.close()
        template_values = {"rows": rows}
        template = jinja2_env.get_template('index.html')
        self.response.out.write(template.render(template_values))


class GuestBook(webapp.RequestHandler):
    def post(self):
        # Posting a new guestbook entry
        conn = get_connection()
        cursor = conn.cursor()
        cursor.execute('INSERT INTO entries (guest_name, content) '
                       'VALUES (%s, %s)',
                       (self.request.get('guest_name'),
                        self.request.get("content")))
        conn.commit()
        conn.close()
        self.redirect("/")


application = webapp.WSGIApplication(
    [
        ("/", MainHandler),
        ("/sign", GuestBook),
    ],
    debug=True
)

The 'google.appengine.api.rdbms' module provides the 'connect' function, which returns a connection object which complies to the Python DB-API 2.0 standard .

Create a jinja2 template file named 'index.html' as follows:

<!DOCTYPE html>
<html>
<head>
<title>CloudSQL Tutorial</title>
</head>
<body>
<h2>Guestbook</h2>
<form action="sign" method="POST">
  Name: <input name="guest_name"><br>
  <textarea name="content" rows="3" cols="60"></textarea><br>
  <input type="submit" value="Sign Guestbook">
</form>
<hr>
{% for row in rows %}
  <p>{{ row[0]|e }} wrote:
    <blockquote>
      {{ row[1]|e }}
    </blockquote>
    at {{ row[2]|e }}
  </p>
  <hr>
{% endfor %}

The |e filter escapes HTML entities and protects the app from some XSS attacks. For more details about the jinja2 template engine, see the jinja2 documentation .

To run this application, you need to pass mysql related parameters to the dev_appserver command like this:

$ app_engine_sdk_path/dev_appserver.py --mysql_socket=mysql_socket_path .

There are several other options related to the local MySQL environment, so it is a good idea to look through all the options for dev_appserver.py by running it with '--help' option.

Play around with this application and make sure the posted data is stored in the local MySQL table correctly.

Before going to the next lesson, look into the bolded lines in the above code closely again, and make sure you understand how to:

1. connect to the database
2. run a query, and put data into the database
3. run the dev_appserver with mysql related options