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

[4 Jan 2018 22:53] Chris Foresman
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.
[11 Oct 2018 10:43] MySQL Verification Team
Hello Chris Foresman,

Thank you for the feature request!

regards,
Umesh
[14 Apr 2021 21:27] Jeff Stein
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')])
```
[6 Jun 2021 6:43] PHILIP COLEMAN
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