Unable to connect to Google Cloud SQL from development server using SQLAlchemy

Question

I've been unsuccessful at connecting to Google Cloud SQL using SQLAlchemy 0.7.9 from my development workstation (in hopes to generate the schema using create_all()). I can't get passed the following error:

sqlalchemy.exc.DBAPIError: (AssertionError) No api proxy found for service "rdbms" None None

I was able to successfully connect to the database instance using the google_sql.py instancename, which initially opened up a browser to authorize the connection (which now appears to have cached the authorization, although I don't have the ~/Library/Preferences/com.google.cloud.plist file as https://developers.google.com/cloud-sql/docs/commandline indicates I should)

Here is the simple application I'm using to test the connection:

from sqlalchemy import create_engine

engine = create_engine('mysql+gaerdbms:///myapp', connect_args={"instance":"test"})
connection = engine.connect()

The full stacktrace is available here - https://gist.github.com/4486641

Mohun

Answer 1

It turns out the mysql+gaerdbms:/// driver in SQLAlchemy was only setup to use the rdbms_apiproxy DBAPI, which can only be used when accessing Google Cloud SQL from a Google App Engine instance. I submitted a ticket to SQLAlchemy to update the driver to use the OAuth-based rdbms_googleapi when not on Google App Engine, just like the Django driver provided in the App Engine SDK. rdbms_googleapi is also the DBAPI that google_sql.py uses (remote sql console).

The updated dialect is expected to be part of 0.7.10 and 0.8.0 releases, but until they are available, you can do the following:

---

1 - Copy the updated dialect in the ticket to a file (ex. gaerdbms_dialect.py)

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'] = ''  # unused but required to pass to rdbms.connect()
        opts['instance'] = url.query['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

---

2 - Register the custom dialect (you can override the existing schema)

from sqlalchemy.dialects import registry
registry.register("mysql.gaerdbms", "application.database.gaerdbms_dialect", "MySQLDialect_gaerdbms")

Note: 0.8 allows a dialect to be registered within the current process (as shown above). If you are running an older version of SQLAlchemy, I recommend upgrading to 0.8+ or you'll need to create a separate install for the dialect as outlined here.

---

3 - Update your create_engine('...') url as the project and instance are now provided as part of the query string of the URL

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

for example:

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

Mohun

Do you have it working with latest SDK? I tried both the dialect that ships with SQLAlchemy 0.8.0 and the one you posted above, and still get the same error described here: #16193479 – Marthena

I found a workaround, and updated your dialect: https://mcmap.net/q/429917/-using-sqlalchemy-on-app-engine-development-server – Marthena

Sorry, SO is terrible about notifying of comments and just saw this. When I do development with a local mysql instance, I was a different connection string / driver (just the default mysql:// (mysql-python) driver) and only using mysql+gaerdbms when using my Production or ProductionDeploy config within my Flask/Flask-Script project. I was also typically not using the dev_appserver and just a standard WSGI server. Looks like a good change worth pushing upstream to SQLAlchemy. – Mohun

I opened a ticket to propose the improved dialect. I understood it wrong thinking that you were using it with the dev server. Well, now it can also be used this way. Thanks for the help. :) – Marthena

Answer 2

I think I have a working sample script as follows. Can you try a similar thing and let me know how it goes?

However(and you might be already aware of it), if your goal is to create the schema on Google Cloud SQL instance, maybe you can create the schema against a local mysql server, dump it with mysqldump, and then import the schema to Google Cloud SQL. Using local mysql server is very convenient for development anyway.

from sqlalchemy import create_engine
from google.storage.speckle.python.api import rdbms as dbi_driver
from google.storage.speckle.python.api import rdbms_googleapi

INSTANCE = 'YOURPROJECT:YOURINSTANCE'
DATABASE = 'YOURDATABASE'

def get_connection():       
    return rdbms_googleapi.connect('', instance=INSTANCE, database=DATABASE)

def main():
    engine = create_engine(
        'mysql:///YOURPROJECT:YOURINSTANCE/YOURDATABASE',
        module=dbi_driver,
        creator=get_connection,
    )
    print engine.execute('SELECT 1').scalar()

if __name__ == '__main__':
    main()

Federalism

This worked and I was able to the SELECT 1 to run. It went through the OAuth flow like the google_sql.py did as well. – Mohun

I looked a lot last night at the existing mysql+gaerdbms:/// dialect (goo.gl/WxpCV) and it uses from google.appengine.api import rdbms which in turn uses google.storage.speckle.python.api.rdbms_apiproxy and not smartly google.storage.speckle.python.api.rdbms_googleapi when not on production / GAE instance like the Django driver does (goo.gl/4ScgJ). – Mohun

I'm trying to find a way to update the mysql+gaerdbms:/// dialect to switch which API to use based on environment like the Django driver does (if apiproxy_stub_map.apiproxy.GetStub('rdbms'): "use rdbms_apiproxy" else "use rdbms_googleapi") – Mohun

Oh, and I'm not able to generate my schema from my mappings, even using your method above. I get: sqlalchemy.exc.DatabaseError: (DatabaseError) 1146: Table 'test.users' doesn't exist 'DESCRIBE users' (). – Mohun

I think I've come up with a Dialect enhancement mysql+gaerdbms:/// that works with apiproxy and googleapi like the Django driver. I've submitted a ticket for review in SQLALchemy's Trak system - sqlalchemy.org/trac/ticket/2649. Btw, the dialect works to generate the schema using create_all() even though I couldn't get it to work with your method. – Mohun

Oh, I'm sorry, my example wasn't complete. I should have tested more SQLs and scenarios. However, it is great to see that you came up with a practical idea. – Federalism

Thanks for your help. Your example tipped me off for how to use rdbms_googleapi (I was originally trying to create my own OAuth flow which wasn't right). – Mohun