Python - MySQL queries with parametres and comas

Question

I'm learning programming with python and trying to implement the safest possible MySQL queries starting with the simple SELECT ones. The problem is whenever I use coma in a query I got the following error:

cursor.execute(query)
  File "C:\Users\username\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\cursor.py", line 536, in execute
    stmt = operation.encode(self._connection.python_charset)
AttributeError: 'tuple' object has no attribute 'encode'

I am aware of the fact that coma itself isn't a source of a problem but I tried many different MySQL syntax and everytime I use a come I got this "AttributeError: 'tuple' object has no attribute 'encode'" error.

I also tried to change MySQL database encoding - nothing changes. The code is below.

import mysql.connector

conn = mysql.connector.connect(
    charset='utf8',
    # init_command='SET NAMES UTF8',
    host="10.0.0.234",
    user="x",
    passwd="x>",
    database="x",
)

print(conn.is_connected())

param = "test"

cursor = conn.cursor()

# =========== query below does work ========

# query = ("SELECT * from list WHERE username LIKE '%test%'")



# ============ query below does work =======

# query = ("SELECT * from list HAVING username = '%s'" % param)



# ============ query below doesn't work =====

# query = ("SELECT * from list HAVING username = %s", (param,))



# ============= query below doesn't work =====

query = "SELECT * from list WHERE username = :name", {'name': param}





cursor.execute(query)
result = cursor.fetchall()
for x in result:
    print(x)

conn.close()

Any ideas what am I doing wrong?

Answer 1

The answer is a little bit tricky, but it is in essence because of what the actual value of the 'query' variable is...

For example:

# 1.
query = ("SELECT * from list WHERE username LIKE '%test%'")
# when you do this, query is a string variable,
# NB: the parentheses are not necessary here

# so when you call
cursor.execute(query)
# the value passed into the execute call is the string "SELECT * from list WHERE username LIKE '%test%'"

# 2.
query = ("SELECT * from list HAVING username = '%s'" % param)
# when you do this, query is the result of a string formatting operation
# This is a Python 2 form of string formatting
# The discussion here probably makes it more clear: 
# https://stackoverflow.com/questions/13945749/string-formatting-in-python-3

# it is almost the same as doing this:
query = "SELECT * from list HAVING username = 'test'"

# so when you call
cursor.execute(query)
# the value passed into the execute call is the string "SELECT * from list HAVING username = 'test'"

# 3. 
query = ("SELECT * from list HAVING username = %s", (param,))
# This operation is assigning a 2-value tuple into the query variable
# The first value in the tuple is the string "SELECT * from list HAVING username = %s"
# The second value in the tuple is a 1-value, with 'test' as its first value

# 4.
query = "SELECT * from list WHERE username = :name", {'name': param}
# This is similar to #3, but the values in the tuple are instead
# query[0] == "SELECT * from list WHERE username = :name"
# query[1] is a dictionary: {'name': param}

Both 3 and 4 above are not calling the MySQL execute with the parameters you are expecting (see API here). You probably need to do one of:

1. unpack the query tuple into separate variables, and call the function with them

   operation, params = query # unpack the first elem into operation, and second into params
   cursor.execute(operation, params)
   

2. just index into the query tuple

   cursor.execute(query[0], query[1])
   # NB: you could also use the 'named parameters' feature in Python
   cursor.execute(query[0], params=query[1])
   

3. Use the 'unpacking arguments list' (SPLAT operator)

   cursor.execute(*query)