Update select row in MySQL with Python-CodePudding

I'm writing a code that essentially queries the MySQL table first, finds a telephone number that doesn't have user assigned and updates it with new user that user specifies. Here is my code:

import mysql.connector

mydb = mysql.connector.connect(
  host="127.0.0.1",
  user="root",
  password="password",
  database="DID_MASTER"
)

mycursor = mydb.cursor()
mycursor.execute("SELECT DID FROM JAX2 WHERE ASSIGNEE = ''")
myresult = mycursor.fetchall()

location = input("Enter users location: :")
name = input('Enter users name: ')

for x in myresult:
    print('Free number to be used: ', x)

    sql = "UPDATE JAX2 SET ASSIGNEE = %s WHERE DID = %s"
    val = (name, x)

    mycursor.executemany(sql, val)  
    mydb.commit()

    break

Query works fine but problem is with updating portion. It returns following error:

Enter users name: Joe Smith
Free number to be used:  (1235136500,)
Traceback (most recent call last):
  File "/Users/user1/Desktop/old mac Desktop/DID Project/DB_Testing.py", line 27, in <module>
    mycursor.executemany(sql, val)  
  File "/Users/user1/Library/Python/3.9/lib/python/site-packages/mysql/connector/cursor_cext.py", line 406, in executemany
    self.execute(operation, params)
  File "/Users/user1/Library/Python/3.9/lib/python/site-packages/mysql/connector/cursor_cext.py", line 266, in execute
    prepared = self._cnx.prepare_for_mysql(params)
  File "/Users/user1/Library/Python/3.9/lib/python/site-packages/mysql/connector/connection_cext.py", line 738, in prepare_for_mysql
    raise ProgrammingError(
mysql.connector.errors.ProgrammingError: Could not process parameters: str(Joe Smith), it must be of type list, tuple or dict

UPDATE ERROR:

raceback (most recent call last):
  File "/Users/nbecirovic/Desktop/old mac Desktop/DID Project/DB_Testing.py", line 27, in <module>
    mycursor.executemany(sql, val)  
  File "/Users/nbecirovic/Library/Python/3.9/lib/python/site-packages/mysql/connector/cursor_cext.py", line 406, in executemany
    self.execute(operation, params)
  File "/Users/nbecirovic/Library/Python/3.9/lib/python/site-packages/mysql/connector/cursor_cext.py", line 266, in execute
    prepared = self._cnx.prepare_for_mysql(params)
  File "/Users/nbecirovic/Library/Python/3.9/lib/python/site-packages/mysql/connector/connection_cext.py", line 726, in prepare_for_mysql
    result = self._cmysql.convert_to_mysql(*params)
_mysql_connector.MySQLInterfaceError: Python type tuple cannot be converted

CodePudding user response：

It seems that you are passing x directly to the update query, But x is a tuple so you must get the first element and then add it to the SQL update query.

As @lucas-fernandes answered you have to use a list with executemany

sql = "UPDATE JAX2 SET ASSIGNEE = %s WHERE DID = %s"
did = x[0]
val = [(name, did)]

mycursor.executemany(sql, val)  
mydb.commit()