Saturday, January 12, 2013

Accessing Google Cloud SQL externally from Python

I started playing with Google Cloud SQL recently, hoping to use it to tie together a data collection application I run on a home server with an Google AppEngine interface for querying the data.  The problem came when I realized that the only external interface into Cloud SQL is via a JDBC driver, and my program was written entirely in Python.

After some toying around, I successfully got the Google Cloud SQL JDBC driver to work from within Python 2.7 under the Python JayDeBeApi module and jpype.

I'm documenting the final steps here in case I need them later.  YMMV

  • Set up a Google Cloud SQL instance and database.

    • This will also require you to register a project in the developer console, and enable billing if you haven't already.  I'm using the temporarily available free trial of Cloud SQL, but you have to have billing associated with the account in case you exceed the free trial quota.

On the system you want to connect to Cloud SQL from:

  • Make sure you have a working JVM environment. (I'm using openjdk-1.6-amd64 on Ubuntu 12.04 64-bit Linux)

  • Download and install JPype

  • Download and install JayDeBeApi

  • Download and unpack the Google Cloud SQL command line tool.  (NOTE: This will also be the .jar file used by python, so make a note of where you put it.)

  • Follow the command line tool instructions to launch the command line tool and connect to your Google Cloud SQL database once, running as the same local user your python script will run as.   This will take you through the OAuth2 flow and store OAuth tokens locally which will be used by jaydebeapi later.

In your python program:

  • Add the path to the google_sql.jar file you unpacked above to the java.class.path in jpype.StartJVM()

  • Connect to your Google Cloud SQL instance & database using driver and URI described on the Google Cloud SQL Connecting from External Applications page.

    • Note:  Pass empty strings ('') for the user and password fields to jaydebeapi.connect() .  What's happening here is that google_sql.jar is using the previously created and stored OAuth2 token on the local filesystem to authenticate.

A q&d python example that works in my environment looks as follows:  (You'll likely have to change the classpath, jvm_path, and insert your database details.)

import jpype
import jaydebeapi

classpath = ('/usr/lib/jvm/java-6-openjdk/jre/lib/'
jvm_path = '/usr/lib/jvm/java-6-openjdk-amd64/jre/lib/amd64/server/'

jpype.startJVM(jvm_path, '-Djava.class.path=%s' % classpath)"hello world")

conn = jaydebeapi.connect('', 'jdbc:google:rdbms://instance/database', '', '')

cur = conn.cursor()

cur.execute('SELECT * from my_table_name')
print cur.fetchall()


The interface exposed by jaydebeapi appears to be the same as the other python DB-API interfaces. Have fun!