python3 sqlalchemy pymysql 连接字符串 [英] python3 sqlalchemy pymysql connect string

问题描述

使用python3，我可以使用pymysql连接到mysql.一切都按预期工作.封闭的代码有效.

导入pymysqlconn = pymysql.connect(host='127.0.0.1', unix_socket='/home/jhgong/mysql/tmp/mysql.sock', user='root', passwd='my_pass', db='my_db', port='3333')cur = conn.cursor()cur.execute('从 auth_users 中选择用户')因为我在cur:打印(一)

试图让 sqlalchemy 与 pymysql 连接，默认示例字符串似乎不起作用.除非我同时声明端口号和 unix_socket，否则上面的示例不起作用.

下面是我一直用来尝试让 sqlalchemy 连接的内容.我假设套接字和端口号都是需要的.我使用 connect_args 输入带有额外 unix_socket 位置的哈希值.没有快乐.

附上我一直在使用的导致错误的代码段.

conarg = {'unix_socket':'/home/jhgong/mysql/tmp/mysql.sock','db':'冰'}engine = create_engine('mysql+pymysql://root:my_pass@127.0.0.1:3333/my_db', connect_args = conarg, echo=True)连接 = engine.connect()

在 conarg 散列中有或没有数据库时，我收到以下错误:

<预><代码>>>>连接 = engine.connect()2013-01-17 13:04:20,819 信息 sqlalchemy.engine.base.Engine b'SELECT DATABASE()'2013-01-17 13:04:20,819 信息 sqlalchemy.engine.base.Engine ()回溯(最近一次调用最后一次):文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/pool.py"，第724行，在_do_get返回 self._pool.get(wait, self._timeout)文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/util/queue.py"，第163行，在get提高空sqlalchemy.util.queue.Empty在处理上述异常的过程中，又发生了一个异常:回溯(最近一次调用最后一次):文件<stdin>"，第 1 行，在 <module> 中文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/engine/base.py"，第1574行，连接返回 self._connection_cls(self, **kwargs)文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/engine/base.py"，第58行，在__init__self.__connection = connection 或 engine.raw_connection()文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/engine/base.py"，第1637行，raw_connection返回 self.pool.unique_connection()文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/pool.py"，第182行，unique_connection返回 _ConnectionFairy(self).checkout()文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/pool.py"，第398行，在__init__rec = self._connection_record = pool._do_get()文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/pool.py"，第744行，在_do_getcon = self._create_connection()文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/pool.py"，第187行，在_create_connection返回 _ConnectionRecord(self)文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/pool.py"，第284行，在__init__exec_once(self.connection, self)文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/event.py"，第362行，在exec_onceself(*args, **kw)文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/event.py"，第379行，在__call__fn(*args, **kw)文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/engine/strategies.py"，第168行，在first_connect方言初始化(c)文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/dialects/mysql/base.py"，第2005行，在初始化default.DefaultDialect.initialize(self, connection)文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/engine/default.py"，第183行，初始化self._get_default_schema_name(连接)文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/dialects/mysql/base.py"，第1970行，在 _get_default_schema_name 中返回 connection.execute('SELECT DATABASE()').scalar()文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/engine/base.py"，第645行，在执行参数)文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/engine/base.py"，第791行，​​在_execute_text语句、参数文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/engine/base.py"，第854行，在_execute_context语境)文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/engine/default.py"，第342行，在do_executecursor.execute(语句，参数)文件/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/PyMySQL3-0.5-py3.2.egg/pymysql/cursors.py"，第105行，执行查询 = 查询 % escaped_args类型错误:% 不支持的操作数类型:字节"和元组"

它似乎在空池队列上引发错误.设置池队列的大小或类型无效.

关于如何解决这个问题有什么建议吗?

解决方案

我知道这已经晚了但是 PyMySQL要求提到 CPython >= 2.6 或 >= 3.3，看起来您使用的是 CPython 3.2(使用的默认 Python 版本)，这可能是您的问题.

using python3, I can connect to mysql using pymysql. all works as expected. enclosed code works.

import pymysql

conn = pymysql.connect(host='127.0.0.1',  unix_socket='/home/jhgong/mysql/tmp/mysql.sock', user='root', passwd='my_pass', db='my_db', port='3333')
cur = conn.cursor()
cur.execute('select user from auth_users')
for i in cur:
    print(i)

trying to get sqlalchemy to connect with pymysql, the default example strings don't seem to work. the above example does not work unless I declare both the port number and a unix_socket.

below is what I've been using to try and get sqlalchemy to connect. I assume that the socket and port number are both needed. I used connect_args to feed in a hash with the extra unix_socket location. no joy.

enclosed the the snippit I've been using that creates the error.

conarg = {
   'unix_socket':'/home/jhgong/mysql/tmp/mysql.sock',
   'db'         :'ice'
}
engine = create_engine('mysql+pymysql://root:my_pass@127.0.0.1:3333/my_db',   connect_args = conarg, echo=True)
connection = engine.connect()

with or without the db in conarg hash i get the following error:

>>> connection = engine.connect()
2013-01-17 13:04:20,819 INFO sqlalchemy.engine.base.Engine b'SELECT DATABASE()'
2013-01-17 13:04:20,819 INFO sqlalchemy.engine.base.Engine ()
Traceback (most recent call last):
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/pool.py", line 724, in _do_get
    return self._pool.get(wait, self._timeout)
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/util/queue.py", line 163, in get
    raise Empty
sqlalchemy.util.queue.Empty

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/engine/base.py", line 1574, in connect 
    return self._connection_cls(self, **kwargs)
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/engine/base.py", line 58, in __init__
    self.__connection = connection or engine.raw_connection()
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/engine/base.py", line 1637, in raw_connection
    return self.pool.unique_connection()
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/pool.py", line 182, in unique_connection
    return _ConnectionFairy(self).checkout()
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/pool.py", line 398, in __init__
    rec = self._connection_record = pool._do_get()
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/pool.py", line 744, in _do_get
    con = self._create_connection()
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/pool.py", line 187, in _create_connection 
    return _ConnectionRecord(self)
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/pool.py", line 284, in __init__
    exec_once(self.connection, self)
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/event.py", line 362, in exec_once
    self(*args, **kw)
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/event.py", line 379, in __call__
    fn(*args, **kw)
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/engine/strategies.py", line 168, in first_connect
    dialect.initialize(c)
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/dialects/mysql/base.py", line 2005, in initialize
    default.DefaultDialect.initialize(self, connection)
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/engine/default.py", line 183, in initialize
    self._get_default_schema_name(connection)
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/dialects/mysql/base.py", line 1970, in _get_default_schema_name
    return connection.execute('SELECT DATABASE()').scalar()
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/engine/base.py", line 645, in execute
    params)
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/engine/base.py", line 791, in _execute_text
    statement, parameters
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/engine/base.py", line 854, in _execute_context
    context)
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/SQLAlchemy-0.8.0b1dev-py3.2.egg/sqlalchemy/engine/default.py", line 342, in do_execute
    cursor.execute(statement, parameters) 
  File "/nfs/site/home/jhgongx/.local/lib/python3.2/site-packages/PyMySQL3-0.5-py3.2.egg/pymysql/cursors.py", line 105, in execute
    query = query % escaped_args
TypeError: unsupported operand type(s) for %: 'bytes' and 'tuple'

it appears to be raising an error on empty pool queue. setting the size or type of pool queue has no effect.

any suggestions on how to figure this out?

解决方案

I know this is late but the PyMySQL requirements mention CPython >= 2.6 or >= 3.3, it looks like you're using CPython 3.2 (the default version of python used), and that may be your problem.

这篇关于python3 sqlalchemy pymysql 连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！