PYTHON：使用 python 变量更新 MULTIPLE COLUMNS
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1081750/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PYTHON: Update MULTIPLE COLUMNS with python variables
提问by elfuego1
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.
我正在尝试编写一个有效的 mysql 语句，它允许我使用作为 python 变量提供的值更新一个记录中的多个列。
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?
在尝试执行查询时，我不断收到有关 SQL 语法有错误的信息。虽然我找不到它。有人可以指出我的错误或告诉我应该如何写吗？
回答by shylent
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))
Did you really need to post it with 10 variables? It was so frustrating to format, I gave up.
你真的需要用 10 个变量来发布它吗？格式化太令人沮丧了，我放弃了。
回答by Ricardo mp
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()
lstNuevosValhas all parameters to replace in sqlcommand. The value of lstNuevosValis
lstNuevosVal具有要在sql命令中替换的所有参数。的价值lstNuevosVal是
['Soporte 1', '6.35', '7.36', '8.37', '9.38', '10.39', 'Soporte 306048\nCabeza 3072589\nMáquina: Deco20\n\n']
回答by Grzegorz Oledzki
Maybe it's about apostrophes around string/VARCHAR values:
也许是关于字符串/VARCHAR 值的撇号：
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)
