CODEWITHSUNDEEP
pythonmysql
Mike C.
Mike C.

Reputation: 1921

How do I update a MySQL table using executemany in Python

I am trying to update multiple items into a MySQL table in Python using executemany. I am not sure where I am going wrong.

Here's the function:

def update_bid_items(itemlines):
    c = create_connection()
    db = c[1]
    cur = db.cursor() 
    sql = """UPDATE bid_items            
             SET item_id = %s,
             description = %s,
             quantity = %s,
             base_price = %s,
             tier_price = %s,
             amount = %s
             WHERE bid_head_id = %s AND 
             row_num = %s
          """
    vals = (itemlines,)
    cur.executemany(sql, vals)        
    db.commit()
    db.close()

Here is the data that I am passing into the function:

[(4, '110V wall plug', '1', '5.00', '5.00', '5.00', '16', 0), (2, 'carpet nails box of 100', '1', '19.95', '19.95', '19.95', '16', 2)]

I am getting the following error:

_mysql_connector.MySQLInterfaceError: Python type tuple cannot be converted

I also tried it with a list instead of tuples with this error:

_mysql_connector.MySQLInterfaceError: Python type list cannot be converted

Upvotes: 0

Views: 1782

Answers (1)

Barmar
Barmar

Reputation: 780974

itemlines is already a list of tuples, you don't need to put it inside another tuple.

So get rid of vals and use

cursor.executemany(sql, itemlines)

Upvotes: 3

Related Questions