SQLAlchemy with Google Cloud SQL connect to development server

Question

I am using SQLAlchemy 0.8 with Google App Engine and Cloud SQL. I would like to do a full integration test locally before deploying, but I cannot get SQLAlchemy to connect to my development MySQL server using the mysql+gaerdbms dialect

create_engine(
    'mysql+gaerdbms:///test?instance=homingbox:instance1'
)

I start my app engine from the command line as follows

python dev_appserver.py --mysql_user=username --mysql_password=root ../Source/HomingBox/gae

Whenever I try to hit the database I get this lovely stack trace

ERROR    2013-04-22 21:35:39,987 webapp2.py:1528] (ImportError)  None None
Traceback (most recent call last):
  File "/home/leon/Development/google_appengine/lib/webapp2-2.3/webapp2.py", line 1511, in __call__
    rv = self.handle_exception(request, response, e)
  File "/home/leon/Development/google_appengine/lib/webapp2-2.3/webapp2.py", line 1505, in __call__
    rv = self.router.dispatch(request, response)
  File "/home/leon/Development/google_appengine/lib/webapp2-2.3/webapp2.py", line 1253, in default_dispatcher
    return route.handler_adapter(request, response)
  File "/home/leon/Development/google_appengine/lib/webapp2-2.3/webapp2.py", line 1077, in __call__
    return handler.dispatch()
  File "/home/leon/Development/google_appengine/lib/webapp2-2.3/webapp2.py", line 547, in dispatch
    return self.handle_exception(e, self.app.debug)
  File "/home/leon/Development/google_appengine/lib/webapp2-2.3/webapp2.py", line 545, in dispatch
    return method(*args, **kwargs)
  File "/home/leon/Development/Source/HomingBox/gae/rest.py", line 19, in get_list
    self.response.write(json.dumps(application.get_list()))
  File "/home/leon/Development/Source/HomingBox/gae/dal.py", line 98, in get_list
    return query.all()
  File "/home/leon/Development/Source/HomingBox/gae/sqlalchemy/orm/query.py", line 2140, in all
    return list(self)
  File "/home/leon/Development/Source/HomingBox/gae/sqlalchemy/orm/query.py", line 2252, in __iter__
    return self._execute_and_instances(context)
  File "/home/leon/Development/Source/HomingBox/gae/sqlalchemy/orm/query.py", line 2265, in _execute_and_instances
    close_with_result=True)
  File "/home/leon/Development/Source/HomingBox/gae/sqlalchemy/orm/query.py", line 2256, in _connection_from_session
    **kw)
  File "/home/leon/Development/Source/HomingBox/gae/sqlalchemy/orm/session.py", line 797, in connection
    close_with_result=close_with_result)
  File "/home/leon/Development/Source/HomingBox/gae/sqlalchemy/orm/session.py", line 801, in _connection_for_bind
    return self.transaction._connection_for_bind(engine)
  File "/home/leon/Development/Source/HomingBox/gae/sqlalchemy/orm/session.py", line 297, in _connection_for_bind
    conn = bind.contextual_connect()
  File "/home/leon/Development/Source/HomingBox/gae/sqlalchemy/engine/base.py", line 1669, in contextual_connect
    self.pool.connect(),
  File "/home/leon/Development/Source/HomingBox/gae/sqlalchemy/pool.py", line 272, in connect
    return _ConnectionFairy(self).checkout()
  File "/home/leon/Development/Source/HomingBox/gae/sqlalchemy/pool.py", line 425, in __init__
    rec = self._connection_record = pool._do_get()
  File "/home/leon/Development/Source/HomingBox/gae/sqlalchemy/pool.py", line 855, in _do_get
    return self._create_connection()
  File "/home/leon/Development/Source/HomingBox/gae/sqlalchemy/pool.py", line 225, in _create_connection
    return _ConnectionRecord(self)
  File "/home/leon/Development/Source/HomingBox/gae/sqlalchemy/pool.py", line 318, in __init__
    self.connection = self.__connect()
  File "/home/leon/Development/Source/HomingBox/gae/sqlalchemy/pool.py", line 368, in __connect
    connection = self.__pool._creator()
  File "/home/leon/Development/Source/HomingBox/gae/sqlalchemy/engine/strategies.py", line 80, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/leon/Development/Source/HomingBox/gae/sqlalchemy/engine/default.py", line 279, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/leon/Development/google_appengine/google/storage/speckle/python/api/rdbms_googleapi.py", line 183, in __init__
    super(GoogleApiConnection, self).__init__(*args, **kwargs)
  File "/home/leon/Development/google_appengine/google/storage/speckle/python/api/rdbms.py", line 810, in __init__
    self.OpenConnection()
  File "/home/leon/Development/google_appengine/google/storage/speckle/python/api/rdbms.py", line 832, in OpenConnection
    self.SetupClient()
  File "/home/leon/Development/google_appengine/google/storage/speckle/python/api/rdbms_googleapi.py", line 193, in SetupClient
    self._client = RdbmsGoogleApiClient(**kwargs)
  File "/home/leon/Development/google_appengine/google/storage/speckle/python/api/rdbms_googleapi.py", line 106, in __init__
    rdbms.OAUTH_CREDENTIALS_PATH)
  File "/usr/lib/python2.7/posixpath.py", line 268, in expanduser
    import pwd
  File "/home/leon/Development/google_appengine/google/appengine/tools/devappserver2/python/sandbox.py", line 827, in load_module
    raise ImportError

Any help is much appreciated

do you have your HOME directory set? – MichaelJCox Apr 23 '13 at 14:09 — MichaelJCox, Apr 23 '13 at 14:09

score 1 · Accepted Answer · answered Apr 23 '13 at 14:35

1

Here's my hypothesis, based on the stack trace -- when the application can't find your HOME directory, it attempts to import pwd and get the home directory information that way. The application is trying to call this bit of code in posixpath.py:

if 'HOME' not in os.environ:
        import pwd
        userhome = pwd.getpwuid(os.getuid()).pw_dir
    else:
        userhome = os.environ['HOME']

Except, I don't think that GAE will allow pwd to be imported for security reasons. The sandbox environment doesn't look to have it included in its whitelist of allowed modules, and raises an ImportError if the modules aren't in that list.

If I attempt to just do an import pwd inside of a regular handler module, GAE won't allow it.

answered Apr 23 '13 at 14:35

MichaelJCox

756
7
17

So if I understand correctly what I am trying to do is actually not possible due to the why gae "limit" some python functionality? – Leon Apr 23 '13 at 18:08
Is your HOME environment variable set to anything? If it's not, set it and see if that helps (or at least gives a different error). – MichaelJCox Apr 23 '13 at 21:15
Actually, it looks like `os.environ['HOME']` does not exist in the GAE environment. os.environ gets loaded with the GAE environment variables. – MichaelJCox Apr 23 '13 at 21:42
As far as not being possible... I don't know atm. You might want to try to figure out why that code is being called and see if there is a way around that. – MichaelJCox Apr 23 '13 at 21:53
Thanks Michael. To be honest this is now becoming a complete overkill for the tests I want to run. It seems sensible to use the standard MySQL dialect rather than fiddle with the App Engine. I'm still accepting your answer as it really helped me gain more insight into the Google App Engine – Leon Apr 24 '13 at 19:08

SQLAlchemy with Google Cloud SQL connect to development server

1 Answers1