Passing a folder location as an SQL parameter in p

问题:

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))

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?

回答1:

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))

Note that we DONT use string formatting here (no "%" between sql and the params)

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/ 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)"

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.