无法使用SQLAlchemy从开发服务器连接到Google Cloud SQL [英] Unable to connect to Google Cloud SQL from development server using SQLAlchemy

问题描述

我在使用SQLAlchemy 0.7.9从我的开发工作站连接到Google Cloud SQL时失败（希望使用create_all（）生成模式）。我无法通过以下错误：

pre $ sqlalchemy.exc.DBAPIError：（AssertionError）找不到服务的api代理 rdbms无无


我能够使用 google_sql.py instancename ，它最初打开一个浏览器来授权连接（现在似乎已经缓存了授权，尽管我没有〜/ Library / Preferences / com.google.cloud.plist 文件为 https：/ /developers.google.com/cloud-sql/docs/commandline 表示我应该）

以下是我用来测试连接的简单应用程序：

  from sqlalchemy import create_engine 
 
 engine = create_engine（'mysql + gaerdbms：/// myapp '，connect_args = {instance：test}）
 connection = engine.connect（）
  

他可以在这里找到完整的stacktrace - https://gist.github.com/4486641

解决方案

事实证明，SQLAlchemy中的 mysql + gaerdbms：/// 驱动程序仅设置使用 rdbms_apiproxy DBAPI，它只能在从Google App Engine实例访问Google Cloud SQL时使用。我向SQLAlchemy提交了票证，以更新驱动程序以使用基于OAuth的 rdbms_googleapi 不在Google App Engine上时，就像App Engine SDK中提供的 Django驱动程序一样。 rdbms_googleapi也是 google_sql.py 使用（远程SQL控制台）的DBAPI。

更新的方言有望是0.7.10和0.8.0版本的一部分，但在可用之前，您可以执行以下操作：

---

1 - 将门票中更新的方言复制到一个文件（例如gaerdbms_dialect.py）

  from sqlalchemy.dialects.mysql.mysqldb从sqlalchemy.pool导入MySQLDialect_mysqldb 
 import NullPool 
 import re 
 
在Google App Engine上支持Google Cloud SQL 
 
连接
 ----------- 
 
连接字符串格式： ：
 
 mysql + gaerdbms：///< dbname>？instance =< project：instance> 
 
 
＃例子：
 create_engine 'mysql + gaerdbms：/// mydb？instance = myproject：instance1'）

 
 
 class MySQLDialect_gaerdbms（MySQLDialect_mysqldb）：
 
 @classmethod 
 de f 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）：
＃云SQL连接随时死亡
 return NullPool 
 
 def create_connect_args（self，url）：
 opts = url.translate_connect_args（）
 opts ['dsn'] =''＃未使用但需要传递给rdbms.connect（）
 opts ['instance'] = url.query ['instance'] 
 return []，opts 
 
 def _extract_error_code（self，exception）：
 match = re.compile（r^（\ d +）：）。match（str例外））
 code = match.group（1）
代码：
 return int（code）
 
 dialect = MySQLDialect_gaerdbms 
  

2 - 注册自定义方言（您可以覆盖现有方案）

  from sqlalchemy.dialects import registry 
 registry.register（mysql.gaerdbms，application.database.gaerdbms_dialect，MySQLDialect_gaerdbms）
   0.8允许在当前进程中注册一个方言（如上所示）。如果您运行的是旧版本的SQLAlchemy，我建议升级到0.8以上，否则您需要为方言创建单独的安装，如 here 。
 
 
 
 
 
 
 3  - 更新您的 create_engine（'...'） url，因为项目和实例现在作为URL的查询字符串的一部分提供
 
 
  mysql + gaerdbms：///< dbname>？instance =< project：instance> 
  
例如： 
 
 
  create_engine（'mysql + gaerdbms：/// mydb？instance = myproject：instance1'）
  
 
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
解决方案
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')


                        
这篇关于无法使用SQLAlchemy从开发服务器连接到Google Cloud SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！