<p>下面是我的问题的有效解决方案的代码片段</p>
<p>此函数非常特定于我的问题,但演示了参数注入技术。它还演示了如何处理SQLite参数注入和Postgres参数注入</p>
<pre><code>def whereInjection(valueList, sqlDict):
# sqlDict starts with just a "paramCount" key set to an initial value (typically 0 but could be any number).
# As this function generates parameter injection strings, it generates a key/value pair for each parameter
# in the form {"p_#": value} where # in the current "paramCount" and value is the value of the associated parameter.
#
# The end result for a valueList containing ["aaa", "bbb", "ccc'ddd", 'eee"fff'] will be:
# injStr = "(:p_0, :p_1, :p_2, :p_3)"
# Note: For Postgres, it has to be "(%(p_0)s, %(p_1)s, etc.)"
# sqlDict = {
# "paramCount": 3,
# "p_0": "aaa",
# "p_1": "bbb",
# "p_2": "ccc'ddd",
# "p_3": 'eee"fff'
# }
localDebugPrintingEnabled = False
# take into account whether the item values are presented as a list, tuple, set, single int, single string, etc.
if isinstance(valueList, list):
vList = valueList
elif isinstance(valueList, tuple):
vList = list(valueList)
elif isinstance(valueList, set):
vList = list(valueList);
elif isinstance(valueList, int) or isinstance(valueList, str):
vList = [valueList]
else:
vList = valueList # unexpected type...
sz = len(vList)
pc = sqlDict["paramCount"]
if (db_type == 'SQLite'):
injectStr = "(" + ",".join((":p_" + str(i + pc)) for i in range(0, sz)) + ")"
else: # assume Postgres
injectStr = "(" + ",".join(("%(p_" + str(i + pc) + ")s") for i in range(0, sz)) + ")"
valueDict = {('p_' + str(i + pc)): vList[i] for i in range(0, sz)}
sqlDict.update(valueDict) # add the valueDict just generated
sqlDict["paramCount"] += sz # update paramCount for all parameters just added
return injectStr
</code></pre>
<p>调用代码如下所示。这假设您知道如何创建到数据库的引擎连接</p>
<pre><code>sqlDict = {"paramCount": 0} # start with empty dictionary and starting count of 0
sql = """SELECT * FROM myTable as mt WHERE mt."aColName" IN {0}""".format(whereInjection(itemList, sqlDict));
my_df = pd.read_sql(sql, engine_connection, params=sqlDict); # does the actual parameter injection
</code></pre>