- changed milestone to 0.8.xx
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)
-
repo owner -
reporter The error I was getting (which is the same one that was occurring in the original Cloud SQL ticket 2484):
sqlalchemy.exc.DatabaseError: (DatabaseError) 1146: Table 'test.users' doesn't exist 'DESCRIBE `users`' ()
but with the code above, I'm able to run create_all without issue.
Regarding
apiproxy_stub_map.apiproxy.GetStub('rdbms')
, I saw that's what Google was doing in their Django db driver to load eitherapiproxy
orgoogleapi
(http://goo.gl/4ScgJ).Looking at the source for
apiproxy
, I think it's attempting to load thegoogle.appengine.runtime
module and it exists, it loads the runtime (production) apiproxies for all the various servivces (ex. https://developers.google.com/appengine/docs/python/apis). I can't say I'm following this code that great, but the short of it is, I saw Google doing this in their Django driver and figured it was how it should be done here :) -
repo owner oh its the
DatabaseError
, that seems like something is wrong with extract_error_code() when you passed in the alternate DBAPI module. but your implementation above seems to do the same thing as the built in dialect for GAE. -
reporter I was able to push my schema to Cloud SQL using
create_all()
from my development box and run queries from a production Google App Engine instance (which should have tested both the DBAPI and the condition to use the right one). Let me know if there is anything else I can help with on this ticket. -
repo owner - changed status to resolved
this will be in 0.8.0, thanks a6697a83e57ef58780e3d6da43f907f88a60e3a8
-
repo owner - changed milestone to 0.8.0final
-
repo owner opening up for more options in
#2715. -
repo owner - removed milestone
Removing milestone: 0.8.0final (automated comment)
- Log in to comment
if it says "table doesn't exist", that means you're trying to access a table that isn't present in the schema to which you're accessing. perhaps the connect() method is not specified correctly in his answer.
can you give me background on what
apiproxy_stub_map.apiproxy.GetStub('rdbms'):
means and how we know this can be trusted in 100% of cases ?