Support Google Cloud SQL using rdbms_googleapi along with rdbms_apiproxy

The current dialect for Google Cloud SQL (mysql+gaerdbms:///) added in 0.7.8 (http://www.sqlalchemy.org/trac/ticket/2484) only supports the rdbms_apiproxy API, which from my understanding, is only used when connecting from a production Google App Engine instance to Google Cloud SQL (i.e. NOT when connecting from your development workstation to Cloud SQL). To support connecting to Cloud SQL from a development workstation, the rdbms_googleapi API needs to be used (which forms a connection using OAuth).

Here is my stab at it:

   from sqlalchemy.dialects.mysql.mysqldb import MySQLDialect_mysqldb
from sqlalchemy.pool import NullPool
import re

"""Support for Google Cloud SQL on Google App Engine

Connecting
-----------

Connect string format::

    mysql+gaerdbms:///<dbname>?instance=<project:instance>


  # Example:
  create_engine('mysql+gaerdbms:///mydb?instance=myproject:instance1')
"""


class MySQLDialect_gaerdbms(MySQLDialect_mysqldb):

    @classmethod
    def dbapi(cls):
        from google.appengine.api import apiproxy_stub_map

        if apiproxy_stub_map.apiproxy.GetStub('rdbms'):
            from google.storage.speckle.python.api import rdbms_apiproxy
            return rdbms_apiproxy
        else:
            from google.storage.speckle.python.api import rdbms_googleapi
            return rdbms_googleapi

    @classmethod
    def get_pool_class(cls, url):
        # Cloud SQL connections die at any moment
        return NullPool

    def create_connect_args(self, url):
        opts = url.translate_connect_args()
        opts['dsn']('dsn') = ''  # unused but required to pass to rdbms.connect()
        opts['instance']('instance') = url.query['instance']('instance')
        return [], opts

    def _extract_error_code(self, exception):
        match = re.compile(r"^(\d+):").match(str(exception))
        code = match.group(1)
        if code:
            return int(code)

dialect = MySQLDialect_gaerdbms

From this I was able to connect and successful run Base.metadata.create_all(engine). I have not attempted to deploy the app to a GAE instance and make sure thee apiproxy side is detected and working (although I should be in the next few days).

Btw, the text for this ticket was much longer, but I slowly worked on an implementation and finally got it working as I typed it. I still have the original text with some supports docs/links if more work needs to be done on this.

Something of interest, this is based on an answer a StackOverflow question I asked (http://goo.gl/G77bB), although I wasn't able to successful run create_all(). Any idea why it works as a dialect and not the create_engine way (maybe the _extra_error_code method, which I stole from the original impl).

Comments (8)