MySQL Bugs: #89112: Cannot use lists or tuples as arguments for WHERE/IN clauses

Bug #89112	Cannot use lists or tuples as arguments for WHERE/IN clauses
Submitted:	4 Jan 2018 22:53	Modified:	11 Oct 2018 10:43
Reporter:	Chris Foresman	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / Python	Severity:	S4 (Feature request)
Version:	8.0.5b1, 8.0.12	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A common SQL query uses an WHERE/IN clause to select several rows matching multiple conditions.
E.g.,
```sql
SELECT * FROM table WHERE id IN (1, 2, 3);
```

Other Python database drivers accept a tuple or list to dynamically construct the proper query like so:
```python
query = """SELECT * FROM table WHERE id IN (%s);"""
cursor.execute(query, [(1, 2, 3)])
```
 
This should result in the properly-interpolated query of:
```sql
SELECT * FROM table WHERE id IN (1, 2, 3);
```

Likewise with strings:
```python
query = """SELECT * FROM table WHERE type IN (%s);"""
cursor.execute(query, [('new', 'unprocessed')])
```

should result in:
```sql
SELECT * FROM table WHERE type IN ('new', 'unprocessed');
```

However, Connector/Python raises an error in this scenario:
TypeError: Python 'tuple' cannot be converted to a MySQL type
or
TypeError: Python 'list' cannot be converted to a MySQL type

How to repeat:
Assuming a `database_connection` function that makes a successful connection using Connector/Python:

```python
>>> conn = database_connection()
>>> cur = conn.cursor()
>>> cur.execute("SELECT * FROM lab_order WHERE id IN (%s);", [(1, 2, 3)])
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/conversion.py", line 179, in to_mysql
    return getattr(self, "_{0}_to_mysql".format(type_name))(value)
AttributeError: 'MySQLConverter' object has no attribute '_tuple_to_mysql'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/cursor.py", line 427, in _process_params
    res = [to_mysql(i) for i in res]
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/cursor.py", line 427, in <listcomp>
    res = [to_mysql(i) for i in res]
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/conversion.py", line 182, in to_mysql
    "MySQL type".format(type_name))
TypeError: Python 'tuple' cannot be converted to a MySQL type

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/cursor.py", line 549, in execute
    psub = _ParamSubstitutor(self._process_params(params))
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/cursor.py", line 432, in _process_params
    "Failed processing format-parameters; %s" % err)
mysql.connector.errors.ProgrammingError: Failed processing format-parameters; Python 'tuple' cannot be converted to a MySQL type
```

Suggested fix:
Ideally, Connector/Python could correctly interpolate these types (or better yet, any iterable of valid types, like ints, strings, etc) into a suitable array for inserting into the query.

Hello Chris Foresman,

Thank you for the feature request!

regards,
Umesh

This is another pattern that is supported by both pymysql and mysqlclient that would also be nice to include.

```python
query = """SELECT * FROM table WHERE (thing1, thing2) IN (%s);"""
cursor.execute(query, [('red', 'blue'), ('old', 'new')])
```

Changing method "_process_params_dict" in "cursor.py" to following solved the problem for me:

def _process_params_dict(self, params):
        """Process query parameters given as dictionary"""
        try:
            to_mysql = self._connection.converter.to_mysql
            escape = self._connection.converter.escape
            quote = self._connection.converter.quote
            res = {}
            for key, value in list(params.items()): ### Begin additions
                if type(value) is tuple:
                    res[key.encode()] = b''
                    for subvalue in value:
                        conv = subvalue
                        conv = to_mysql(conv)
                        conv = escape(conv)
                        conv = quote(conv)
                        res[key.encode()] = res[key.encode()] + b',' + conv if len(res[key.encode()]) else conv
                else: ### End additions
                    conv = value
                    conv = to_mysql(conv)
                    conv = escape(conv)
                    conv = quote(conv)
                    res[key.encode()] = conv
        except Exception as err:
            raise errors.ProgrammingError(
                "Failed processing pyformat-parameters; %s" % err)
        else:
            print('result: ', res)
            return res