Python / MySQL组合的最佳转义字符策略是什么？ [英] What is the best escape character strategy for Python/MySQL combo?

问题描述

这是我的查询。

  cursor2.execute（update myTable set`+ str（row [1]） += \'+ str（row [3]）+'\其中ID ='+ str（row [0]）+'）
  

当行值具有双引号一些值时，它失败。

解决方案

这是一个例子：

  import MySQLdb 
 column = str（MySQLdb.escape_string（row [1]））
 query =update myTable set％（column）s = %% s id = %% s％dict（column = column）
 cursor2.execute（query，[row [3]，row [0]]）
  pre> 
 
 
 更新 
 
 
 
这是一个简短的评论：
  column = str（MySQLdb.escape_string（row [1]））
  
总是一个好主意，以逃避任何进入查询的东西。在这种情况下，我们会动态地添加列名称，因此在执行查询之前必须进行转义。
  query =更新myTable集％（列）s = %% s其中ID = %% s％dict（column = column）
  
我正在形成查询。我试图实现两件事情：（1）使用在上一行中声明的列变量填充列名的查询（2）添加将被填入的占位符通过查询执行期间的实际参数。 
 
 
 
片段 dict（column = column）实际上是创建字典的另一种方式 {'column'：column} 。这可以使用 dict 构造函数实现。我不想要
填写其他地方持有人，所以我使用两个百分号（ %% ）逃脱他们。
  cursor2.execute（query，[row [3]，row [0]]）
  
最后执行查询。如果在执行之前打印查询，您将看到字符串更新myTable set column_name =％s其中ID =％s 。 
 
This is my query.
cursor2.execute("update myTable set `"+ str(row[1]) +"` = \"'" + str(row[3]) +"'\" where ID = '"+str(row[0])+"'")
It is failing when row values have double quotes "some value". How do I escape all special characters?
解决方案
Here is an example:
import MySQLdb
column = str(MySQLdb.escape_string(row[1]))
query = "update myTable set %(column)s = %%s where ID = %%s" % dict(column = column) 
cursor2.execute(query, [row[3], row[0]])
Update 

Here is a brief commentary:
column = str(MySQLdb.escape_string(row[1]))
Always a good idea to escape anything that goes into a query. In this case we are dynamically adding a column name and hence it has to be escaped before the query is executed.
query = "update myTable set %(column)s = %%s where ID = %%s" % dict(column = column) 
I am forming the query here. I am trying to achieve two things: (1) form a query with column name populated using the column variable declared in the previous line (2) add placeholders that will be filled in by actual parameters during query execution. 

The snippet dict(column = column) is actually another way of creating the dictionary {'column': column}. This is made possible using the dict constructor. I don't want to 
 fill in the other place holders just yet so I escape them using two percentage signs (%%).
cursor2.execute(query, [row[3], row[0]])
Finally execute the query. If you print query before executing you'll see the string update myTable set column_name = %s where ID = %s. 

                        
这篇关于Python / MySQL组合的最佳转义字符策略是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！