14 
#!/usr/bin/python

# -*- coding: utf-8 -*-
import MySQLdb as mdb

con = mdb.connect('localhost', 'root', 'root', 'kuis')

with con:

    cur = con.cursor()
    cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s ",
        ("new_value" , "3"))
    print "Number of rows updated:",  cur.rowcount


With above code the third row's value of the table Writers in the database kuis gets updated with new_value and the output will be Number od rows updated: 1
How am I supposed to update multiple rows at the same time?

Improve this question
5
  • 1
    Change your WHERE statement to match multiple criteria? Commented Jan 2, 2015 at 14:13
  • perhaps cursor.execute('update sql ...', multi=True)? Commented Jan 2, 2015 at 14:13
  • @algosig, do you want to update multiple rows with "new_values" = 3, or multiple sql updates with different "new_value"? Commented Jan 2, 2015 at 14:14
  • 1
    @Anzel I want to update like "tom" in the third row i.e. in '3' and "sam" in the fourth i.e. '4', is it possible with the single cur.execute(...) query or should I use the same command multiple times? Commented Jan 2, 2015 at 14:17
  • 1
    both my comment and @PeterMmm's answer will work for you Commented Jan 2, 2015 at 14:18

3 Answers 3

Reset to default
36

Probably you are looking for cursor.executemany.

cur.executemany("UPDATE Writers SET Name = %s WHERE Id = %s ",
        [("new_value" , "3"),("new_value" , "6")])
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I got the result but why Number of rows updated is still the same i.e. 1 which should be 2 when I updated two fields?
@algosig, that's because on the execute level there is no database commit until the point you do conn.commit(), that's the whole point of you asking for executemany isn't it?
@PeterMmm, Thanks for this. Can you help me with in query. Qry - "Update mytable SET status = 'new status' where id in ()"
@John cur.executemany("Update mytable SET status = %s where id = %s",[("new status" , "3"),("new status" , "6")])
FYI for interested parties - try playing around with you're quote characters (or even removing them) if you're getting syntax errors but can't figure out why. I was using single quotes for string values, but switching to double or even no quotes seemed to start working (not sure its worth a new question)
|
11

I don't think mysqldb has a way of handling multiple UPDATE queries at one time.

But you can use an INSERT query with ON DUPLICATE KEY UPDATE condition at the end.

I written the following example for ease of use and readability. 

import MySQLdb

def update_many(data_list=None, mysql_table=None):
    """
    Updates a mysql table with the data provided. If the key is not unique, the
    data will be inserted into the table.

    The dictionaries must have all the same keys due to how the query is built.

    Param:
        data_list (List):
            A list of dictionaries where the keys are the mysql table
            column names, and the values are the update values
        mysql_table (String):
            The mysql table to be updated.
    """

    # Connection and Cursor
    conn = MySQLdb.connect('localhost', 'jeff', 'atwood', 'stackoverflow')
    cur = conn.cursor()

    query = ""
    values = []

    for data_dict in data_list:

        if not query:
            columns = ', '.join('`{0}`'.format(k) for k in data_dict)
            duplicates = ', '.join('{0}=VALUES({0})'.format(k) for k in data_dict)
            place_holders = ', '.join('%s'.format(k) for k in data_dict)
            query = "INSERT INTO {0} ({1}) VALUES ({2})".format(mysql_table, columns, place_holders)
            query = "{0} ON DUPLICATE KEY UPDATE {1}".format(query, duplicates)

        v = data_dict.values()
        values.append(v)

    try:
        cur.executemany(query, values)
    except MySQLdb.Error, e:
        try:
            print"MySQL Error [%d]: %s" % (e.args[0], e.args[1])
        except IndexError:
            print "MySQL Error: %s" % str(e)

        conn.rollback()
        return False

    conn.commit()
    cur.close()
    conn.close()

Explanation of one liners

columns = ', '.join('`{}`'.format(k) for k in data_dict)

is the same as

column_list = []
for k in data_dict:
    column_list.append(k)
columns = ", ".join(columns)

Here's an example of usage

test_data_list = []
test_data_list.append( {'id' : 1, 'name' : 'Tech', 'articles' : 1 } )
test_data_list.append( {'id' : 2, 'name' : 'Jhola', 'articles' : 8 } )
test_data_list.append( {'id' : 3, 'name' : 'Wes', 'articles' : 0 } )

update_many(data_list=test_data_list, mysql_table='writers')

Query output

INSERT INTO writers (`articles`, `id`, `name`) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE articles=VALUES(articles), id=VALUES(id), name=VALUES(name)

Values output

[[1, 1, 'Tech'], [8, 2, 'Jhola'], [0, 3, 'Wes']]
Improve this answer

3 Comments

Actually there is one and it is explained just above your answer. Hope you also benefit from it ;)
This answer saved me SO much time. I was updating a massive table using the first answer and it was taking hours. Using the INSERT ON DUPLICATE KEY only takes about a minute with the same query. The reason why is explained on the mysql page: "In most cases, the executemany() method iterates through the sequence of parameters, each time passing the current parameters to the the execute() method."
This answer worked for me. I adjusted it to work with mysql.connector. For Python 3.8, I also had to change ` data_dict.values()` to list( data_dict.values()). I also needed to include all of the columns (not sure if this was required because of database constraints?). I did about 50K data transformations and updates on a remote server in under a minute. Now for some big tables.
4

The simple one I have to write for my use is.

sql='''INSERT INTO <Tabel Name> (column 1, column 2, ... , column N)
 VALUES (%s, %s, ..., %s) 
ON DUPLICATE KEY UPDATE column1=VALUES(column 1), column3=VALUES(column N)'''

mycursor.executemany(sql, data)

data = ['value of column 1', 'value of column 2' , ... ,'value of column N']

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.