PyMySQL Incorrectly Identifying Variables as Tuple

Question

I am trying to do an insert with PyMySQL in Python3, but I keep getting the following:

TypeError: unsupported operand type(s) for %: 'tuple' and 'tuple'

My code is as follows:

query = ("INSERT INTO table_name ",
                 "(type,sub) ",
                 "VALUES ",
                 "(%s,%s)")
#Execute the query
print("Category: %s" % category)
print("Category is string:",isinstance(category,str))
print("Category_group: %s" % category_group)
print("Category_group is string:",isinstance(category,str))
self.DB['master']['cursor'].execute(query,(category,category_group,))
self.DB['master']['con'].commit()

Which when running outputs:

Category: Amusement Park
Category is string: True
Category_group: Parks
Category_group is string: True
Traceback (most recent call last):
  File "main.py", line 87, in <module>
    m()
  File "main.py", line 82, in __call__
    self.deal.processdeal(deal=item,store=store)
  File "file.py", line 85, in processdeal
    btype = self.obtainBusinessType(deal['category'],deal['categoryGroup'])
  File "file.py", line 59, in obtainBusinessType
    self.DB['master']['cursor'].execute(query,(category,category_group,))
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/cursors.py", line 130, in execute
    query = query % self._escape_args(args, conn)
TypeError: unsupported operand type(s) for %: 'tuple' and 'tuple'

The output statements indicate that I am feeding strings as parameters, so why is it complaining about tuples?

Answer 1

Your problem is how you are building your query variable.

query = ("INSERT INTO table_name ",
             "(type,sub) ",
             "VALUES ",
             "(%s,%s)")
print query 
>>>('INSERT INTO table_name ', '(type,sub) ', 'VALUES ', '(%s,%s)')

query = query % ('hello', 'world')

TypeError: unsupported operand type(s) for %: 'tuple' and 'tuple'

Notice how your query is being outputted as a tuple, which causing the error.

You can use a str:

query = "INSERT INTO table_name (type,sub) VALUES (%s,%s)"

Or you remove the , from your query

query = ("INSERT INTO table_name "
              "(type,sub) "
              "VALUES "
              "(%s,%s)")

Alternatively, as Kindall stated, you can also use triple-quote for multi-line strings

"""INSERT INTO table_name 
           (type,sub) 
           VALUES
           (%s,%s)"""