在python mysql查询中动态传递列名和值

Question

这是下面的代码

pythonlist = ['Name','Mno']
datalist = ["qwerty",'234']
sql = "SELECT " + ",".join(pythonlist) + " FROM data WHERE name = '"+ "','".join(datalist) + "' INTO OUTFILE filename"
print(sql)

输出：

SELECT Name,Mno FROM data WHERE Name= 'qwerty','234' 

所需输出：

SELECT Name,Mno FROM data WHERE Name = 'qwerty' and Mno = 234 

注意删除“mno”中的引号

我之所以这样做是因为列名以及与之对应的值将频繁更改

Answer 1

试试这个：

data = {'Name': 'qwerty' , 'Mno' : '234'}

sql = "SELECT " + ", ".join(data.keys()) + " FROM data WHERE " + str(list(data.keys())[0]) + "  = '" + \
      str(data[list(data.keys())[0]]) + "' and " +\
      str(list(data.keys())[1]) + " = " + str(data[list(data.keys())[1]])

print(sql)

Answer 2

代码：

queryparams = {'Name': 'qwerty', 'Mno': '234'}

and_clause = []
[and_clause.append(' %s = %s ') for k,v in queryparams.items()]
and_clause_str = ' and '.join(and_clause)

sql = 'SELECT %s FROM data WHERE ' + and_clause_str

params = [','.join(queryparams.keys())]
for k,v in queryparams.items():
  params.append(str(k))
  params.append(str(v))
print(sql)
print(params)
cursor.execute(sql, params=tuple(params))

如果您向字典中再添加10/20个条目，则此操作有效。 以及防止SQL-injection：使用params来传递值，而不是string-concatenation