Reputation: 54
So I have been looking around a lot. I wouldn't ask the question had any of the answers on SO helped me.
Basically, I have a python list that I want to update my mysql column with. I can't seem to find the right query for it. I am using mysql.connector library to connect to a database.
fourth_query = 'UPDATE trendsincities SET trends = (%s) cursor.executemany(fourth_query, tuple(trends))
Where trends is a list that contains strings seperated by commas.
To this, I get an error saying - Not all parameters were used in the SQL statement
Need help on this. Linking this to a solved question would also be appreciated. Thanks
Upvotes: 0
Views: 2487
Reputation: 1
What works for me, was:
row = 0
for i in range(0, len(values)):
row +=1
val = values[i]
sql = f"UPDATE teste SET percent = {val} WHERE id = {row}"
cursor.execute(sql)
connection.commit()
Obs: I'm using mysql.connector to establish a connection with MySQL.
Upvotes: 0
Reputation: 110592
Is trendsincities
a VARCHAR
field and you are trying to save a comma-separated-string into it? If so, then you would want something like this:
trends = ["best", "city", "new"]
trends_as_csv_string = ",".join(trends)
sql = "UPDATE trendsincities SET trends=%s"
cursor.execute(sql, trends_as_csv_string)
However I'm a bit suspicious of this. I think you probably want an INSERT
if it's a new item? Or, if it's an UPDATE
, you probably want to use a WHERE
clause such as:
"UPDATE trendsincities SET trends='best,city,new' WHERE city='New York'"
Otherwise, each successive update will overwrite the previous.
But if you want to insert each trend you would use this pattern:
trends = ["best", "city", "new"]
for trend in trends:
cursor.execute('INSERT INTO trendsincities (trend) VALUES (%s)', trend)
And then at the end of your sql statement(s) you'll want to issue a connection.commit()
depending on if you have autocommit
turned on or not.
Hopefully one of the above approaches will help.
Upvotes: 1