PYTHON: Update MULTIPLE COLUMNS with python variables

Question

I'm trying to write a valid mysql statement that would allow me to update multiple columns in one record with values provided as python variables.

My statement would look like this:

db = MySQLdb.connect(host="localhost", user="user", passwd="password", db="dbname")
cursor = db.cursor()
sql_update = "UPDATE table_name SET field1=%s, field2=%s, field3=%s, field4=%s, field5=%s, field6=%s, field7=%s, field8=%s, field9=%s, field10=%s WHERE id=%s" % (var1, var2, var3, var4, var5, var6, var7, var8, var9, var10, id)
cursor.execute(sql_update)
cursor.close ()
db.commit()
db.close()

While trying to execute the query, I keep receiving information that there is an error in my SQL syntax. I can't locate it though. Could someone point out my mistake or show me how it should be written?

score 22 · Accepted Answer · edited May 06 '21 at 20:25

22

You are using string formatting, while what you SHOULD be doing is using a parametrized query. Do it like this:

cursor.execute("UPDATE table_name SET field1=%s, ..., field10=%s WHERE id=%s", (var1,... var10, id))

edited May 06 '21 at 20:25

Kalimantan

702
1
9
28

answered Jul 04 '09 at 07:53

shylent

10,076
6
38
55

1

if anyone is still having problem, i followed this format but it didn't work. used comma between multiple fields, then it worked. like => SET field1=%s, field2=%s, field3=%s WHERE criteria1=%s – syd Nov 04 '20 at 12:27

score 3 · Answer 2 · answered Jun 27 '21 at 19:11

Please, have these notes into consideration:

you should make sure the user and password are correct
The number of place holder must match the number of values
the type of values you are using matches the column's type in your table

please try the following code:

db = MySQLdb.connect(host="localhost", user="user", passwd="password", db="dbname")
cursor = db.cursor()
sql_update = "UPDATE table_name SET field1=%s, field2=%s, field3=%s, field4=%s, field5=%s, field6=%s, field7=%s, field8=%s, field9=%s, field10=%s WHERE id=%s"
val =  (var1, var2, var3, var4, var5, var6, var7, var8, var9, var10, id)
cursor.execute(sql_update, val)
db.commit()
cursor.close ()
db.close()

score 1 · Answer 3 · edited Mar 12 '16 at 21:57

I did it as:

    def bscaSoporte(self, denom_sop):
        soporte = (denom_sop,) #Para que tome la variable, lista
        sql= 'SELECT * FROM SOPORTE_ID WHERE denom_sop LIKE ?'
        self.cursor1.execute(sql, soporte)
        return self.cursor1.fetchall()

    def updSoporte(self, lstNuevosVal):
        #busca soporte, SOLO HAY UNO que cumpla
        denom_sop = lstNuevosVal.pop(0)
        print(lstNuevosVal)
        encontrado = self.bscaSoporte(denom_sop)
        soporte = (denom_sop,) #Para que tome la variable, lista
        if encontrado != None:
            sqlupdate =('UPDATE SOPORTE_ID SET  dst_pln_eje = ?, geom_Z_eje = ?, \
            geom_Y_0_grd = ?, dst_hta_eje = ?,  geom_Z_0_grd = ?, \
            descrip = ? WHERE denom_sop  LIKE ?')
        #añado denom_soporte al final de la lista
        lstNuevosVal.append(denom_sop)
        self.cursor1.execute(sqlupdate, (lstNuevosVal))
        self.conexion.commit()

lstNuevosVal has all parameters to replace in sql command. The value of lstNuevosVal is

['Soporte 1', '6.35', '7.36', '8.37', '9.38', '10.39', 'Soporte 306048\nCabeza 3072589\nMáquina: Deco20\n\n']

score 0 · Answer 4 · answered Jan 27 '22 at 07:15

def UpdateData(lVideoList, no_of_gate):
    connection = sqlite3.connect('db.sqlite',
                                 detect_types=sqlite3.PARSE_DECLTYPES |
                                              sqlite3.PARSE_COLNAMES)
    cursor = connection.cursor()
    success = 200
    unsuccess = 500
    default_value = 0
    for i in range(no_of_gate):
        gate_id = i+1
        for videofilename in lVideoList:
##            print("videofilename: ", videofilename)
            ldate = videofilename[0:10]
            sql_select_Query = "select * from dailyfootfall where csv_name LIKE '"+ldate+"%'"
            cursor.execute(sql_select_Query)

            result  = cursor.fetchall()
            print(result)

            if len(result)>0 :
                try:
                    
                    print ('Entry Not found...!!!')
                    insert_query = ("INSERT or IGNORE INTO dailyfootfall(csv_name, video_download, processed, footfall, send_status, "
                                    "male_footfall, send_status_male, female_footfall, send_status_female, gate_id,outsiders, send_status_outsiders) "
                                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)")

                    cursor.execute(insert_query,[videofilename, unsuccess, unsuccess, default_value, unsuccess, default_value,
                                                 unsuccess, default_value, unsuccess, gate_id, default_value, unsuccess])

                    print("Data_Inserted..!!")
                    print("="*20)
                except Exception as e:
                    
                    exc_type, exc_obj, exc_tb = sys.exc_info()
                    fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
                    print("entry Not found"exc_type, fname, exc_tb.tb_lineno)
                    
            else:
                try:
                    
                    print ('Entry found...!!!')

                    cursor.execute("UPDATE dailyfootfall SET video_download=?, processed=?, footfall=?, send_status=? ,male_footfall=?, send_status_male=?, "
                                   "female_footfall =?,send_status_female=?, outsiders=?, send_status_outsiders=? "
                                   "WHERE csv_name=? AND gate_id=?", [unsuccess,unsuccess,default_value,unsuccess,default_value,unsuccess,default_value,
                                                                      unsuccess,default_value,unsuccess,videofilename,gate_id])
                    print("Data_Updated..!!!")

                    
                except Exception as e:
                    exc_type, exc_obj, exc_tb = sys.exc_info()
                    fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
                    print("Entry found: "exc_type, fname, exc_tb.tb_lineno)

       
    print("Data Inserted Successfully !")
    connection.commit()
    cursor.close()
    connection.close()

Hi and thanks for the answer. Your answer would be much more valuable to us if you added an explanation about what your code does and why it helps the OP — Simas Joneliunas, Feb 01 '22 at 05:56

score -2 · Answer 5 · answered Jul 04 '09 at 08:23

Maybe it's about apostrophes around string/VARCHAR values:

sql_update = "UPDATE table_name SET field1='%s', field2='%s', field3='%s', field4='%s', field5='%s', field6='%s', field7='%s', field8='%s', field9='%s', field10='%s' WHERE id='%s'" % (var1, var2, var3, var4, var5, var6, var7, var8, var9, var10, id)

PYTHON: Update MULTIPLE COLUMNS with python variables

5 Answers5

Linked