[Sqlalchemy-tickets] Issue #4233: params={} is being passed to pymysql but it expects None (zzzeek/sqlalchemy)

[Abdeali K. <iss...@bi...>]

New issue 4233: params={} is being passed to pymysql but it expects None
https://bitbucket.org/zzzeek/sqlalchemy/issues/4233/params-is-being-passed-to-pymysql-but-it

Abdeali Kothari:

I was using pandas+sqlalchemy_pymysql to query from MySQL. It looks like when I try to read sql or even use sqlalchemy engine to query it gives me an error when I use a % in my query.

Using Python 3.6, sqlalchemy 1.2.1


```
#!python

qry = """
SELECT DATE_FORMAT(fetchDate, '%Y-%m') as frequency
FROM portfolionotesdailyperformance
LIMIT 10;
"""
import sqlalchemy
import settings
engine = sqlalchemy.create_engine(settings.mariadb_connection)
engine.execute(qry)
```

It looks like the `params` being passed to the cursor is {} but pymysql expects it to be a `None` for it to NOT try using python string formatting.

Reference:
https://github.com/PyMySQL/PyMySQL/blob/9c1b0d80995587cab39b56ea3b48cc25cb63289d/pymysql/cursors.py#L144

If anything other than None is passed, it does a % formatting in python.
This causes issues here as the %Y is getting converted to Y and then it gives an error:




```
#!python

ValueError                                Traceback (most recent call last)
<ipython-input-2-52648731c651> in <module>()
      7 import settings
      8 engine = sqlalchemy.create_engine(settings.mariadb_connection)
----> 9 engine.execute(qry)

~/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py in execute(self, statement, *multiparams, **params)
   2073
   2074         connection = self.contextual_connect(close_with_result=True)
-> 2075         return connection.execute(statement, *multiparams, **params)
   2076
   2077     def scalar(self, statement, *multiparams, **params):

~/venv/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

~/venv/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:

~/venv/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:

~/venv/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:

~/venv/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:

~/venv/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(

~/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    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):

~/venv/lib/python3.6/site-packages/pymysql/cursors.py in execute(self, query, args)
    162             pass
--> 163         query = self.mogrify(query, args)
    164
    165         result = self._query(query)

~/venv/lib/python3.6/site-packages/pymysql/cursors.py in mogrify(self, query, args)
    141
    142         if args is not None:
--> 143             query = query % self._escape_args(args, conn)
    144
    145         return query

ValueError: unsupported format character 'Y' (0x59) at index 33
```