TypeError when trying to connect to mysql using pyodbc

Issue #4223 wontfix
Dmitriy Shashkin
created an issue

python 3.6, 64 bit, fedora 27, sqlalchemy 1.2.5, mysql 5.7

When trying to connect to mysql using pyodbc I get the following error: TypeError: The first argument to execute must be a string or unicode query.

This problem does not exist in 1.1.18, it was introduced in 1.2.0

Code to reproduce error (requires and existing mysql database):

import sqlalchemy
sqlalchemy.create_engine('mysql+pyodbc://dsn').connect()

The text of error message:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-6-2a6312fe45dd> in <module>()
----> 1 sqlalchemy.create_engine('mysql+pyodbc://replica_lan_mob_api_stats').connect()

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in connect(self, **kwargs)
   2100         """
   2101 
-> 2102         return self._connection_cls(self, **kwargs)
   2103 
   2104     def contextual_connect(self, close_with_result=False, **kwargs):

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in __init__(self, engine, connection, close_with_result, _branch_from, _execution_options, _dispatch, _has_events)
     88         else:
     89             self.__connection = connection \
---> 90                 if connection is not None else engine.raw_connection()
     91             self.__transaction = None
     92             self.__savepoint_seq = 0

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in raw_connection(self, _connection)
   2186         """
   2187         return self._wrap_pool_connect(
-> 2188             self.pool.unique_connection, _connection)
   2189 
   2190 

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _wrap_pool_connect(self, fn, connection)
   2156         dialect = self.dialect
   2157         try:
-> 2158             return fn()
   2159         except dialect.dbapi.Error as e:
   2160             if connection is None:

~/.local/lib/python3.6/site-packages/sqlalchemy/pool.py in unique_connection(self)
    343 
    344         """
--> 345         return _ConnectionFairy._checkout(self)
    346 
    347     def _create_connection(self):

~/.local/lib/python3.6/site-packages/sqlalchemy/pool.py in _checkout(cls, pool, threadconns, fairy)
    782     def _checkout(cls, pool, threadconns=None, fairy=None):
    783         if not fairy:
--> 784             fairy = _ConnectionRecord.checkout(pool)
    785 
    786             fairy._pool = pool

~/.local/lib/python3.6/site-packages/sqlalchemy/pool.py in checkout(cls, pool)
    530     @classmethod
    531     def checkout(cls, pool):
--> 532         rec = pool._do_get()
    533         try:
    534             dbapi_connection = rec.get_connection()

~/.local/lib/python3.6/site-packages/sqlalchemy/pool.py in _do_get(self)
   1187             except:
   1188                 with util.safe_reraise():
-> 1189                     self._dec_overflow()
   1190         else:
   1191             return self._do_get()

~/.local/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback)
     64             self._exc_info = None   # remove potential circular references
     65             if not self.warn_only:
---> 66                 compat.reraise(exc_type, exc_value, exc_tb)
     67         else:
     68             if not compat.py3k and self._exc_info and self._exc_info[1]:

~/.local/lib/python3.6/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    185         if value.__traceback__ is not tb:
    186             raise value.with_traceback(tb)
--> 187         raise value
    188 
    189 else:

~/.local/lib/python3.6/site-packages/sqlalchemy/pool.py in _do_get(self)
   1184         if self._inc_overflow():
   1185             try:
-> 1186                 return self._create_connection()
   1187             except:
   1188                 with util.safe_reraise():

~/.local/lib/python3.6/site-packages/sqlalchemy/pool.py in _create_connection(self)
    348         """Called by subclasses to create a new ConnectionRecord."""
    349 
--> 350         return _ConnectionRecord(self)
    351 
    352     def _invalidate(self, connection, exception=None, _checkin=True):

~/.local/lib/python3.6/site-packages/sqlalchemy/pool.py in __init__(self, pool, connect)
    475         self.__pool = pool
    476         if connect:
--> 477             self.__connect(first_connect_check=True)
    478         self.finalize_callback = deque()
    479 

~/.local/lib/python3.6/site-packages/sqlalchemy/pool.py in __connect(self, first_connect_check)
    675                 pool.dispatch.first_connect.\
    676                     for_modify(pool.dispatch).\
--> 677                     exec_once(self.connection, self)
    678             if pool.dispatch.connect:
    679                 pool.dispatch.connect(self.connection, self)

~/.local/lib/python3.6/site-packages/sqlalchemy/event/attr.py in exec_once(self, *args, **kw)
    272                 if not self._exec_once:
    273                     try:
--> 274                         self(*args, **kw)
    275                     finally:
    276                         self._exec_once = True

~/.local/lib/python3.6/site-packages/sqlalchemy/event/attr.py in __call__(self, *args, **kw)
    282             fn(*args, **kw)
    283         for fn in self.listeners:
--> 284             fn(*args, **kw)
    285 
    286     def __len__(self):

~/.local/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py in go(*arg, **kw)
   1332         if once:
   1333             once_fn = once.pop()
-> 1334             return once_fn(*arg, **kw)
   1335 
   1336     return go

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py in first_connect(dbapi_connection, connection_record)
    181                                     _has_events=False)
    182                 c._execution_options = util.immutabledict()
--> 183                 dialect.initialize(c)
    184             event.listen(pool, 'first_connect', first_connect, once=True)
    185 

~/.local/lib/python3.6/site-packages/sqlalchemy/dialects/mysql/base.py in initialize(self, connection)
   1902                     "CHECK constraints, which impact handling of NULL values "
   1903                     "with SQLAlchemy's boolean datatype (MDEV-13596). An "
-> 1904                     "additional issue prevents proper migrations of columns "
   1905                     "with CHECK constraints (MDEV-11114).  Please upgrade to "
   1906                     "MariaDB 10.2.9 or greater, or use the MariaDB 10.1 "

~/.local/lib/python3.6/site-packages/sqlalchemy/dialects/mysql/pyodbc.py in _detect_charset(self, connection)
     59         # If it's decided that issuing that sort of SQL leaves you SOL, then
     60         # this can prefer the driver value.
---> 61         rs = connection.execute("SHOW VARIABLES LIKE 'character_set%%'")
     62         opts = {row[0]: row[1] for row in self._compat_fetchall(rs)}
     63         for key in ('character_set_connection', 'character_set'):

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in execute(self, object, *multiparams, **params)
    940         """
    941         if isinstance(object, util.string_types[0]):
--> 942             return self._execute_text(object, multiparams, params)
    943         try:
    944             meth = object._execute_on_connection

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_text(self, statement, multiparams, params)
   1102             statement,
   1103             parameters,
-> 1104             statement, parameters
   1105         )
   1106         if self._has_events or self.engine._has_events:

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1198                 parameters,
   1199                 cursor,
-> 1200                 context)
   1201 
   1202         if self._has_events or self.engine._has_events:

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1414                 )
   1415             else:
-> 1416                 util.reraise(*exc_info)
   1417 
   1418         finally:

~/.local/lib/python3.6/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    185         if value.__traceback__ is not tb:
    186             raise value.with_traceback(tb)
--> 187         raise value
    188 
    189 else:

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1191                         statement,
   1192                         parameters,
-> 1193                         context)
   1194         except BaseException as e:
   1195             self._handle_dbapi_exception(

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    505 
    506     def do_execute(self, cursor, statement, parameters, context=None):
--> 507         cursor.execute(statement, parameters)
    508 
    509     def do_execute_no_params(self, cursor, statement, context=None):

TypeError: The first argument to execute must be a string or unicode query.

Comments (6)

  1. Michael Bayer repo owner

    the MySQL Pyodbc dialect is not really supported in any case. can you please use a more common dialect like PyMySQL? runs in pure python and you will have no issues.

  2. Michael Bayer repo owner

    additionally i dont understand the error message, that is a string being passed. feel free to share exactly what ODBC driver you are using, OS in use, and how this driver is configured.

  3. Dmitriy Shashkin reporter

    Yes, I guess switching to other dialect is an option, though the ability to easily share settings between applications through odbc.ini is really convenient.

    Was able to reproduce the problem with publically available DB, here is the excerpt from odbc.ini:

    [ensemble_test]
    Driver = MySQL
    Server = ensembldb.ensembl.org
    Port = 3306
    User = anonymous
    

    The code looks like this:

    import sqlalchemy
    sqlalchemy.create_engine('mysql+pyodbc://ensemble_test').connect()
    

    odbcinst.ini looks like the following:

    [MySQL]
    Description=ODBC for MySQL
    Driver=/usr/lib/libmyodbc5.so
    Setup=/usr/lib/libodbcmyS.so
    Driver64=/usr/lib64/libmyodbc5w.so
    Setup64=/usr/lib64/libodbcmyS.so
    FileUsage=1
    

    My pyodbc version is 4.0.22

    mysql odbc driver comes from version 5.3.10 of mysql-connector-odbc package from mysql-connectors-community repository

    For the reference, I had the same problem on Centos 7.4

  4. Log in to comment