Search code examples
pythonmysqlupdatesexecutemany

How solve Operand should contain 1 column(s) for update multiple rows in python?


I want to update multiple rows with a single query in python. I use executemany based on the following example but I got this error:

Operand should contain 1 column(s)

This is my code which shows how I create list of values of rows need to be update.I update rows whose postings is just string NULL.

def UpdatePostingList (self, data):

    queryUpdate='Update KeywordIndex2 set postings=%s where keyValue= %s'

    querySelect='SELECT postings FROM KeywordIndex2 where  keyValue= %s'
    list=[]
    try:

        for i in range(0,len(data)):
            keyValue=data[i][0]
            k=(keyValue,)
            self.cursor.execute(querySelect,k)
            postings= self.cursor.fetchall()
            pst=[x[0] for x in postings]
            if pst[0]=='NULL':
                p=data[i][1]
                list.insert(i, (p,keyValue))
        print list
        self.cursor.executemany(queryUpdate,list)
        self.connection.commit()
    except MySQLdb.Error, e:
        try:
            print "MySQL Error [%d]: %s" % (e.args[0], e.args[1])
        except IndexError:
            print "MySQL IndexError: %s" % str(e)
        self.connection.rollback()

I got this output:

[([(3, 0.4698370099067688), (12, 0.38598471879959106), (2, 0.33203423023223877), (1, 0.3257867097854614), (8, 0.3251670002937317)], 'york'), ([(21, 0.5803983509540558), (18, 0.5671890079975128), (24, 0.5287801623344421), (9, 0.5264906287193298), (15, 0.47776609659194946)], 'yang'), ([(12, 0.6617408990859985), (3, 0.6475195586681366), (15, 0.4491569995880127), (24, 0.4268345832824707), (21, 0.40550071001052856)], 'world')] MySQL Error [1241]: Operand should contain 1 column(s)

To be clear for the last member of list the keyValue is world and the postinglist is the first part of tuple.


Solution

  • The problem of my code is that I send p as list of tuples for column postings. I changed p to str(p) in the following line of my code and the problem was solved.

    list.insert(i, (str(p),keyValue))