python3 sqlalchemy pymysql connect string
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:[email protected]: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?
Answer
Try this very simple example:
import sqlalchemy
from sqlalchemy.sql import select
from sqlalchemy import Table, MetaData
def init():
try:
server = 'xx'
db = 'xx'
login = 'xx'
passwd = 'xx'
engine_str = 'mysql+mysqlconnector://{}:{}@{}/{}'.format(login, passwd, server, db)
engine = sqlalchemy.create_engine(engine_str, echo=False, encoding='utf-8')
connection = engine.connect()
metadata = MetaData()
t_servers = Table('your_table_here', metadata, autoload=True, autoload_with=engine)
s = select([t_servers])
result = connection.execute(s)
for row in result:
print(row['the_field'])
except Exception:
raise
finally:
connection.close()
if __name__ == '__main__':
init()
Mysql connector for Python 3 : download here