Using SQLAlchemy on App Engine development server

I have seen some questions about using SQLAlchemy on App Engine to connect to Google Cloud SQL. But I'm not sure if it is possible to develop using a local MySQL database and the existing SQLAlchemy dialect. On my first attempt, I added SQLAlchemy 0.8.0 to the app and defined a schema:

from sqlalchemy import create_engine, Column, Integer, Table
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()    

foo_table = Table('foo', Base.metadata,
  Column('id', Integer, primary_key=True, autoincrement=True),
)

And when I tried to create the tables on the development server using:

url = 'mysql+gaerdbms:///%s?instance=%s' % ('database_name', 'instance_name')
engine = create_engine(url)
Base.metadata.create_all(engine)

...I got an error DBAPIError: (ImportError) No module named pwd None None, which means that SQLAlchemy is importing a module that is blacklisted by the development server.

Am I doing something wrong? Or, if not, what should I do to use SQLAlchemy on the development server? Or maybe the first question is: Can I use the SQLAlchemy's gaerdbms dialect to develop in a local MySql database using the dev server?

Edit: this error doesn't happen only when trying to create tables. I created the tables manually and tried to query them, and the same error occurs.

The full traceback is:

Traceback (most recent call last):
  File "[...]/google_appengine/lib/webapp2-2.5.2/webapp2.py", line 1535, in __call__
    rv = self.handle_exception(request, response, e)
  File "[...]/google_appengine/lib/webapp2-2.5.2/webapp2.py", line 1529, in __call__
    rv = self.router.dispatch(request, response)
  File "[...]/google_appengine/lib/webapp2-2.5.2/webapp2.py", line 1278, in default_dispatcher
    return route.handler_adapter(request, response)
  File "[...]/google_appengine/lib/webapp2-2.5.2/webapp2.py", line 1102, in __call__
    return handler.dispatch()
  File "[...]/google_appengine/lib/webapp2-2.5.2/webapp2.py", line 572, in dispatch
    return self.handle_exception(e, self.app.debug)
  File "[...]/google_appengine/lib/webapp2-2.5.2/webapp2.py", line 570, in dispatch
    return method(*args, **kwargs)
  File "[...]/webapp/admin.py", line 12, in get
    db.Base.metadata.create_all(engine)
  File "[...]/webapp/sqlalchemy/schema.py", line 2784, in create_all
    tables=tables)
  File "[...]/webapp/sqlalchemy/engine/base.py", line 1486, in _run_visitor
    with self._optional_conn_ctx_manager(connection) as conn:
  File "/usr/lib/python2.7/contextlib.py", line 17, in __enter__
    return self.gen.next()
  File "[...]/webapp/sqlalchemy/engine/base.py", line 1479, in _optional_conn_ctx_manager
    with self.contextual_connect() as conn:
  File "[...]/webapp/sqlalchemy/engine/base.py", line 1669, in contextual_connect
    self.pool.connect(),
  File "[...]/webapp/sqlalchemy/pool.py", line 272, in connect
    return _ConnectionFairy(self).checkout()
  File "[...]/webapp/sqlalchemy/pool.py", line 425, in __init__
    rec = self._connection_record = pool._do_get()
  File "[...]/webapp/sqlalchemy/pool.py", line 855, in _do_get
    return self._create_connection()
  File "[...]/webapp/sqlalchemy/pool.py", line 225, in _create_connection
    return _ConnectionRecord(self)
  File "[...]/webapp/sqlalchemy/pool.py", line 318, in __init__
    self.connection = self.__connect()
  File "[...]/webapp/sqlalchemy/pool.py", line 368, in __connect
    connection = self.__pool._creator()
  File "[...]/webapp/sqlalchemy/engine/strategies.py", line 80, in connect
    return dialect.connect(*cargs, **cparams)
  File "[...]/webapp/sqlalchemy/engine/default.py", line 279, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "[...]/google_appengine/google/storage/speckle/python/api/rdbms_googleapi.py", line 183, in __init__
    super(GoogleApiConnection, self).__init__(*args, **kwargs)
  File "[...]/google_appengine/google/storage/speckle/python/api/rdbms.py", line 810, in __init__
    self.OpenConnection()
  File "[...]/google_appengine/google/storage/speckle/python/api/rdbms.py", line 832, in OpenConnection
    self.SetupClient()
  File "[...]/google_appengine/google/storage/speckle/python/api/rdbms_googleapi.py", line 193, in SetupClient
    self._client = RdbmsGoogleApiClient(**kwargs)
  File "[...]/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 259, in expanduser
    import pwd
  File "[...]/google_appengine/google/appengine/tools/devappserver2/python/sandbox.py", line 822, in load_module
    raise ImportError('No module named %s' % fullname)
DBAPIError: (ImportError) No module named pwd None None

I found a workaround. As it is, SQLAlchemy's gaerdbms dialect can't connect to a local database. But with the dialect below it can. Folow the instructions from this answer but use this dialect instead:

# mysql/gaerdbms.py
# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file>
#
# This module is part of SQLAlchemy and is released under
# the MIT License: http://www.opensource.org/licenses/mit-license.php
"""
.. dialect:: mysql+gaerdbms
    :name: Google Cloud SQL
    :dbapi: rdbms
    :connectstring: mysql+gaerdbms:///<dbname>?instance=<instancename>
    :url: https://developers.google.com/appengine/docs/python/cloud-sql/developers-guide

    This dialect is based primarily on the :mod:`.mysql.mysqldb` dialect with minimal
    changes.

    .. versionadded:: 0.7.8


Pooling
-------

Google App Engine connections appear to be randomly recycled,
so the dialect does not pool connections.  The :class:`.NullPool`
implementation is installed within the :class:`.Engine` by
default.

"""

import os
import re

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


class MySQLDialect_gaerdbms(MySQLDialect_mysqldb):

    @classmethod
    def dbapi(cls):
        # from django:
        # http://code.google.com/p/googleappengine/source/
        #     browse/trunk/python/google/storage/speckle/
        #     python/django/backend/base.py#118
        # see also [ticket:2649]
        # see also https://mcmap.net/q/428971/-unable-to-connect-to-google-cloud-sql-from-development-server-using-sqlalchemy/34549
        if is_production():
            # Production mode.
            from google.storage.speckle.python.api import rdbms_apiproxy
            return rdbms_apiproxy
        elif is_remote_mode():
            # Development mode with remote database.
            from google.storage.speckle.python.api import rdbms_googleapi
            return rdbms_googleapi
        else:
            # Development mode with local database.
            from google.appengine.api import rdbms_mysqldb
            return rdbms_mysqldb

    @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()
        if is_production() or is_remote_mode():
            # 'dsn' and 'instance' are because we are skipping
            # the traditional google.api.rdbms wrapper.
            # they are not needed in local mode; 'dns' even causes an error.
            opts['dsn'] = ''
            opts['instance'] = url.query['instance']
        return [], opts

    def _extract_error_code(self, exception):
        match = re.compile(r"^(\d+):|^\((\d+),").match(str(exception))
        # The rdbms api will wrap then re-raise some types of errors
        # making this regex return no matches.
        code = match.group(1) or match.group(2) if match else None
        if code:
            return int(code)

dialect = MySQLDialect_gaerdbms

def is_production():
  return os.getenv('SERVER_SOFTWARE', '').startswith('Google App Engine')

def is_remote_mode():
  return os.getenv('SETTINGS_MODE') == 'prod'

This dialect uses a local database by default when running on the development server. To use remote access to Google Cloud SQL during development, a variable must be set in the environment, following the pattern used by Django:

os.environ['SETTINGS_MODE'] = 'prod'

Recommended topics

Hot tags