[FIXED] Python flask_mysqldb passing over 10 does't work

Issue

I'm totally new and learning Python+MySQL, And I have an issue with passing over 10 to %s

@app.route('/usr/<id>', methods=['GET'])
def selected_logs(id):
    response_object = {'status': 'success'}
    cur = mysql.connection.cursor()
    cur.execute('''
    SELECT
            usr.id,
            usr.corp_id,
            date_format (log.date, '%%m/%%d/%%Y') AS date
        FROM 
            (usr
            INNER JOIN corps ON corps.id = usr.corp_id)
        WHERE
            usr.corp_id = %s
        ORDER BY usr.id DESC;
    ''', (id))
    results = cur.fetchall()
    response_object['usr'] = results
    return jsonify(response_object)

In the URL http://localhost:5000/usr/9 works, but start to 10... won't work, please help me.

MySQLdb._exceptions.ProgrammingError: not all arguments converted during bytes formatting

THANK YOU SO MUCH

Solution

Execute() takes an iterable as the second argument. You need to pass an iterable of values to bind to parameters, (id) is not an iterable. It is a common mistake to think that it is a tuple but it is not, (id,) is a tuple which is an iterable.

cur.execute('''
    SELECT
            usr.id,
            usr.corp_id,
            date_format (log.date, '%%m/%%d/%%Y') AS date
        FROM 
            (usr
            INNER JOIN corps ON corps.id = usr.corp_id)
        WHERE
            usr.corp_id = %s
        ORDER BY usr.id DESC;
    ''', (id,))

The error hides this issue because the id value is '10' and strS are iterables in Python.

MySQLdb._exceptions.ProgrammingError: not all arguments converted during bytes formatting

Edit: Response to comment

although I have still don't understand what you mean completely

Execute() takes an iterable of values to bind to parameters in the query, and it must be an iterable in order for execute() to support multiple parameters in the query. The only way (id) can be an iterable is if id itself is an iterable.

id = 9
type((id))
type((id,))

<class 'int'>
<class 'tuple'>

id = '9'
type((id))
type((id,))

<class 'str'>
<class 'tuple'>

The parentheses don't construct a tuple, the comma does. Even if you try to create a tuple of one element using the explicit, tuple() constructor, you'll get an error without the comma.

no_parentheses = 1,
no_comma = (1)
explicit = tuple(1)
type(no_parentheses)
type(no_comma)

Traceback (most recent call last):
  File "", line 1, in 
TypeError: 'int' object is not iterable
<class 'tuple'>
<class 'int'>

So why did (id) work for id='9' but not id='10'? It's because in each case id is a path variable which is a str by default and therefore (id) is a str. Since strS are iterable, execute() unpacked (id) and tried to map it onto the parameters in the query. Since there is only one parameter in the query, the unpacking succeeds for every value of id with length=1. When id='10', it unpacks as '1','0' and execute() tries to map it onto two parameters. But, since there's only one parameter, raises an exception:

MySQLdb._exceptions.ProgrammingError: not all arguments converted during bytes formatting

I'll agree that this message isn't very descriptive of the root cause here. There may have been more information in the complete stacktrace. Regardless, the important part is "not all arguments converted." It's telling you that there are more values than parameters in the query.

Answered By - Michael Ruth