CODEWITHSUNDEEP
pythonmysqlmysql-connector
Devesh Datwani
Devesh Datwani

Reputation: 54

How do I update a column in mysql with a python list?

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

Answers (2)

Luiz
Luiz

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

David542
David542

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

Related Questions