将文件夹位置作为python中的SQL参数传递会导致错误 [英] Passing a folder location as an SQL parameter in python causes an error
问题描述
我对python还是很陌生,我知道的唯一SQL是来自该项目的,因此请原谅缺乏技术知识:
I am fairly new to python and the only SQL I know is from this project so forgive the lack of technical knowledge:
def importFolder(self):
user = getuser()
filename = askopenfilename(title = "Choose an image from the folder to import", initialdir='C:/Users/%s' % user)
for i in range (0,len(filename) - 1):
if filename[-i] == "/":
folderLocation = filename[:len(filename) - i]
break
cnxn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Public\dbsDetectorBookingSystem.accdb')
cursor = cnxn.cursor()
cursor.execute("SELECT * FROM tblRuns")
cursor.execute("insert into tblRuns(RunID,RunFilePath,TotalAlphaCount,TotalBetaCount,TotalGammaCount) values (%s,%s,0,0,0)" %(str(self.runsCount + 1), folderLocation))
cnxn.commit()
self.runsCount = cursor.rowcount
rowString = str(self.runsCount) + " " + folderLocation + " " + str(0) + " " + str(0) + " " + str(0) + " " + str(0)
self.runsTreeView.insert("","end", text = "", values = (rowString))
这是我当前程序中的一个例程,旨在创建一条新记录,该记录除了索引和文件位置外几乎都是空的.该位置需要另存为字符串,但是当将其作为参数传递给SQL字符串时,会发生以下错误:
cursor.execute("insert into tblRuns(RunID,RunFilePath,TotalAlphaCount,TotalBetaCount,TotalGammaCount) values (%s,%s,0,0,0)" %(str(self.runsCount + 1), folderLocation))
ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'C:/Users/Jacob/Documents/USB backup'. (-3100) (SQLExecDirectW)")
我认为这是因为SQL识别文件路径并想要为其使用.有人知道如何解决此问题吗?
That is one routine from my current program meant to create a new record which is mostly empty apart from an index and a file location. This location needs to be saved as a string however when it is passed as a paramenter to the SQL string the following error occurs:
cursor.execute("insert into tblRuns(RunID,RunFilePath,TotalAlphaCount,TotalBetaCount,TotalGammaCount) values (%s,%s,0,0,0)" %(str(self.runsCount + 1), folderLocation))
ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'C:/Users/Jacob/Documents/USB backup'. (-3100) (SQLExecDirectW)")
I assume this is because the SQL recognises a file path and wantsto user it. Does anybody know how to fix this?
推荐答案
您没有正确使用db-api.而不是使用字符串格式来传递您的查询参数-这很容易出错(正如您刚刚注意到的那样),并且出现了安全性问题,您想将它们作为参数传递给cursor.execute()
,即:
You're not using the db-api correctly. Instead of using string formatting to pass your query params - which is error-prone (as you just noticed) AND a security issue, you want to pass them as arguments to cursor.execute()
, ie:
sql = "insert into tblRuns(RunID, RunFilePath, TotalAlphaCount, TotalBetaCount, TotalGammaCount) values (%s, %s, 0, 0, 0)"
cursor.execute(sql, (self.runsCount + 1, folderLocation))
请注意,我们在此不要使用字符串格式(在sql
和参数之间没有%")
Note that we DONT use string formatting here (no "%" between sql
and the params)
NB:请注意,参数化查询的占位符取决于您的数据库连接器. python-MySQLdb使用%
,但是您的用户可能使用?
或其他任何东西.
NB : note that the placeholder for parameterized queries depends on your db connector. python-MySQLdb uses %
but your one may use a ?
or anything else.
wrt/您的确切问题:由于您没有在占位符两边加上引号,因此您发送的sql查询类似于:
wrt/ your exact problem: since you didn't put quotes around your placeholders, the sql query you send looks something like:
"insert into tblRuns(
RunID, RunFilePath,
TotalAlphaCount, TotalBetaCount, TotalGammaCount
)
values (1,/path/to/folder,0,0,0)"
这显然不能工作(它需要在/path/to/folder
周围加上引号才能有效的SQL).
Which cannot work, obviously (it needs quotes around /path/to/folder
to be valid SQL).
通过正确地传递查询参数,您的数据库连接器将处理所有引用和转义.
By passing query parameters the right way, your db connector will take care of all the quoting and escaping.
这篇关于将文件夹位置作为python中的SQL参数传递会导致错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!