Google Developers Academy

Building an application with a local MySQL instance

Learning objectives

  • Set up a local MySQL instance
  • Build a guestbook application with the local MySQL instance

Prerequisites

  • Basic understanding of Python
  • Familiarity with using Google App Engine
  • Basic understanding of SQL

Related

  • MySQL 5.5 documentation
  • Python DB-API 2.0 spec
  • jinja2 documentation

    Takashi Matsuo, Jun 2012
    Google Developer Relations

    Introduction

    This lesson teaches you how to build a guestbook application with App Engine and a local MySQL instance.

    Set up a local MySQL instance

    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.

    Build a guestbook application with the local MySQL instance

    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.

    Lesson wrapup

    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

    Authentication required

    You need to be signed in with Google+ to do that.

    Signing you in...

    Google Developers needs your permission to do that.