Sqlalchemy can't connect to a mysql server

0
votes

My legacy mysql 4.0.20 server is on a windows machine. I'm developing a new system (python based) on a linux that needs to connect to the legacy server and make queries etc. I have successfully connected using both plain MySQLdb and django. I'm having trouble connecting using sqlalchemy. Here is the code:

conn_str = "mysql://user:[email protected]/dbd"
engine = create_engine(conn_str, echo=True)
metadata = MetaData(engine)
connection = engine.connect()

and the error stack I'm getting:

2011-03-01 08:35:04,613 INFO sqlalchemy.engine.base.Engine.0x...b42c SELECT DATABASE() 2011-03-01 08:35:04,613 INFO sqlalchemy.engine.base.Engine.0x...b42c () Traceback (most recent call last):

connection = engine.connect() File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", line 1811, in connect return self.Connection(self, **kwargs) File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", line 832, in init self.connection = connection or engine.raw_connection() File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", line 1874, in raw_connection return self.pool.unique_connection() File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/pool.py", line 142, in unique_connection return _ConnectionFairy(self).checkout() File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/pool.py", line 369, in __init rec = self._connection_record = pool.get() File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/pool.py", line 213, in get return self.do_get() File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/pool.py", line 732, in do_get con = self.create_connection() File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/pool.py", line 147, in create_connection return _ConnectionRecord(self) File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/pool.py", line 258, in init l.first_connect(self.connection, self) File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/strategies.py", line 151, in first_connect dialect.initialize(c) File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/dialects/mysql/base.py", line 1753, in initialize default.DefaultDialect.initialize(self, connection) File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/default.py", line 159, in initialize self.returns_unicode_strings = self._check_unicode_returns(connection) File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/default.py", line 205, in _check_unicode_returns unicode_for_varchar = check_unicode(sqltypes.VARCHAR(60)) File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/default.py", line 195, in check_unicode ]).compile(dialect=self) File "/usr/local/lib/python2.6/dist-packages/MySQL_python-1.2.3c1-py2.6-linux-i686.egg/MySQLdb/cursors.py", line 173, in execute self.errorhandler(self, exc, value) File "/usr/local/lib/python2.6/dist-packages/MySQL_python-1.2.3c1-py2.6-linux-i686.egg/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue ProgrammingError: (1064, "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(60)) AS anon_1' at line 1")

1
mysqlsqlalchemy

1 Answers

1
votes

Looks like MySQL is reporting a syntax error in one of your SQL statements:

<snip>

1064, "You have an error in your SQL syntax.
Check the manual that corresponds to your MySQL server
version for the right syntax to use near
'(60)) AS anon_1' at line 1

</snip>

It looks like MySQL is objecting to a statement that includes the string:

(60)) AS anon_1

NOTE: I am not familiar with Sqlalchemy, but it looks to me like the exception (the SQL error from the statement) is getting propagated up to the handler specified in the connection.

FOLLOWUP: It's likely that a query that Sqlalchemy is executing is not compatible with the older version of MySQL. Looks like it's in the "_check_unicode_returns" function.

Have you tried disabling unicode support?

conn_str = "mysql://user:[email protected]/dbd?use_unicode=0"