Feature Request: Google Cloud SQL Support [ Code Attached ]

[Former user]

(original reporter: richieforeman) Hello,

I've been doing some tinkering with Google Cloud SQL + SQLAlchemy. Cloud SQL is really just MySQL over JDBC under the covers. I've included a mysql dialect that I've been using with great success. I'm open to any comments, thoughts, or changes on this.

I was having some issues with connections 'dying' -- so I set the pool_recycle to 1, which cleared that issue up (I tried various numbers between 30 and 60 as well, with limited success -- sometimes GAE just 'closes' the connection for the hell of it -- which makes sense, I suppose). If there's a better way to handle that, I'd love to change my code.

I've attached my dialect code at the following URL:

/sqlalchemy/dialects/mysql/gaerdbms.py: http://pastebin.com/FLU88Mv2

[Mike Bayer]

looks interesting, am travelling this week but will try to review more closely soon.

[Mike Bayer]

pasting here so we aren't dependent on pastebin.

from sqlalchemy.dialects.mysql.mysqldb import MySQLDialect_mysqldb

__author__ = "Richie Foreman <[e-mail]>"

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

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

Connect string format::

    mysql+gaerdbms:///<dbname>


  # Example:
  create_engine('mysql+gaerdbms:///mydb',
                 connect_args={"instance":"instancename",
                 pool_recycle=1)
"""

class MySQLDialect_gaerdbms(MySQLDialect_mysqldb):

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

    def create_connect_args(self, url):
        return [[]([),{'database':url.database}]

dialect = MySQLDialect_gaerdbms

[Mike Bayer]

what I think we should do as far as connections "dying" is to disable pooling. We do that like this:

    @classmethod
    def get_pool_class(cls, url):
        return pool.NullPool

Also have you had any issues with create_all() ? I'm told there's problems there, GAE is not raising the exception we expect.

[Former user]

(original author: richieforeman) Okay, radical. Now that my gross pool recycle hack is gone, my instance start up time for my app plummeted from 13000ms down to about 4000ms (acceptable, imo).

Random Thought: If I'm using a scoped_session -- that would still make NullPool threadsafe, right?

However, I am having an issue with create_all(). Here's my stacktrace:

(DatabaseError) 1146: Table 'tpm.promotion_sku' doesn't exist 'DESCRIBE `promotion_sku`' ()
Traceback (most recent call last):
  File "/base/python27_runtime/python27_lib/versions/third_party/webapp2-2.3/webapp2.py", line 1511, in __call__
    rv = self.handle_exception(request, response, e)
  File "/base/python27_runtime/python27_lib/versions/third_party/webapp2-2.3/webapp2.py", line 1505, in __call__
    rv = self.router.dispatch(request, response)
  File "/base/data/home/apps/s~instancenameremoved/1.359020357412427922/app.py", line 24, in custom_dispatcher
    rv = router.default_dispatcher(request, response)
  File "/base/python27_runtime/python27_lib/versions/third_party/webapp2-2.3/webapp2.py", line 1253, in default_dispatcher
    return route.handler_adapter(request, response)
  File "/base/python27_runtime/python27_lib/versions/third_party/webapp2-2.3/webapp2.py", line 1077, in __call__
    return handler.dispatch()
  File "/base/data/home/apps/s~instancenameremoved/1.359020357412427922/app.py", line 52, in dispatch
    _webapp.RequestHandler.dispatch(self)
  File "/base/python27_runtime/python27_lib/versions/third_party/webapp2-2.3/webapp2.py", line 547, in dispatch
    return self.handle_exception(e, self.app.debug)
  File "/base/python27_runtime/python27_lib/versions/third_party/webapp2-2.3/webapp2.py", line 545, in dispatch
    return method(*args, **kwargs)
  File "/base/data/home/apps/s~instancenameremoved/1.359020357412427922/main.py", line 335, in get
    TPMDataHelper.create_db_tables()
  File "/base/data/home/apps/s~instancenameremoved/1.359020357412427922/models.py", line 365, in create_db_tables
    Base.metadata.create_all(db)
  File "/base/data/home/apps/s~instancenameremoved/1.359020357412427922/sqlalchemy/schema.py", line 2553, in create_all
    tables=tables)
  File "/base/data/home/apps/s~instancenameremoved/1.359020357412427922/sqlalchemy/engine/base.py", line 2293, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/base/data/home/apps/s~instancenameremoved/1.359020357412427922/sqlalchemy/engine/base.py", line 1963, in _run_visitor
    **kwargs).traverse_single(element)
  File "/base/data/home/apps/s~instancenameremoved/1.359020357412427922/sqlalchemy/sql/visitors.py", line 106, in traverse_single
    return meth(obj, **kw)
  File "/base/data/home/apps/s~instancenameremoved/1.359020357412427922/sqlalchemy/engine/ddl.py", line 54, in visit_metadata
    if self._can_create_table(t)]
  File "/base/data/home/apps/s~instancenameremoved/1.359020357412427922/sqlalchemy/engine/ddl.py", line 32, in _can_create_table
    table.name, schema=table.schema)
  File "/base/data/home/apps/s~instancenameremoved/1.359020357412427922/sqlalchemy/dialects/mysql/base.py", line 1964, in has_table
    rs = connection.execute(st)
  File "/base/data/home/apps/s~instancenameremoved/1.359020357412427922/sqlalchemy/engine/base.py", line 1450, in execute
    params)
  File "/base/data/home/apps/s~instancenameremoved/1.359020357412427922/sqlalchemy/engine/base.py", line 1627, in _execute_text
    statement, parameters
  File "/base/data/home/apps/s~instancenameremoved/1.359020357412427922/sqlalchemy/engine/base.py", line 1697, in _execute_context
    context)
  File "/base/data/home/apps/s~instancenameremoved/1.359020357412427922/sqlalchemy/engine/base.py", line 1690, in _execute_context
    context)
  File "/base/data/home/apps/s~instancenameremoved/1.359020357412427922/sqlalchemy/engine/default.py", line 335, in do_execute
    cursor.execute(statement, parameters)
  File "/base/python27_runtime/python27_lib/versions/1/google/storage/speckle/python/api/rdbms.py", line 424, in execute
    self._DoExec(request)
  File "/base/python27_runtime/python27_lib/versions/1/google/storage/speckle/python/api/rdbms.py", line 355, in _DoExec
    response = self._conn.MakeRequest('Exec', request)
  File "/base/python27_runtime/python27_lib/versions/1/google/storage/speckle/python/api/rdbms.py", line 704, in MakeRequest
    response = self._MakeRetriableRequest(stub_method, request)
  File "/base/python27_runtime/python27_lib/versions/1/google/storage/speckle/python/api/rdbms.py", line 735, in _MakeRetriableRequest
    sql_exception.message))
DatabaseError: (DatabaseError) 1146: Table 'tpm.promotion_sku' doesn't exist 'DESCRIBE `promotion_sku`' ()

[Mike Bayer]

yeah that error needs to be caught and checked. Two things need to be in place in googles DBAPI. 1. The DatabaseError they are throwing must be a subclass of rdbms.DBAPIError. They are probably doing this. Next, the dialect needs to implement _extract_error_code() correctly for google's API here, so that we get the number "1146" out of it.

[Former user]

(original author: richieforeman) Alright! Sweet, I've implemented _extract_error_code in my code, and things are looking pretty good. I'm able to run create_all()!

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>


  # Example:
  create_engine('mysql+gaerdbms:///mydb',
                 connect_args={"instance":"instancename"})
"""

class MySQLDialect_gaerdbms(MySQLDialect_mysqldb):

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

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

    def create_connect_args(self, url):
        return [[]([),{'database':url.database}]

    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

[Mike Bayer]

6b810a9141dcc65cd462066e741937f32fbc745a 1e0ecce9ed0b319c476f6dba5f159c6b9153b4a7

good luck !

[Mike Bayer]

Removing milestone: 0.7.8 (automated comment)