How to use pyodbc, cursor, execute to extract data from SQL when there is no parameters for query.format?

Usually, I use the following way to extract data from SQL using python:

myConnect=pyodbc.connect('DSN=B1P HANA;UID=****;PWD=****')
myCursor=myConnect.cursor()

Start1=20150101
End=20200101
query = """
        SELECT "Operator",
               "Position"
        FROM ******
        """
myRow = myCursor.execute(query.format(Start1=Start1,
                                      End=End)
Result = myRow.fetchall()
OperatorTMs = pd.DataFrame(columns=["Operator", "Position"])
for i in Result:
    OperatorTMs=OperatorTMs.append({"Operator":i.Operator,"Position":i.Position},ignore_index=True)

But now, I do not need any parameters in query.format(). And I tried the way in https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html. And it does not work

So could anyone please tell mw how to do this without any parameters in query.format?

1 answer

  • answered 2019-05-15 12:03 Gord Thompson

    You really shouldn't be using .format to insert column values into your SQL command. Search for "SQL Injection" or "Little Bobby Tables" for more information.

    Instead, you should be using ? parameter placeholders and supplying the column values as additional arguments to .execute as in

    query = "SELECT col1, col2 FROM tablename WHERE col1 BETWEEN ? AND ?"
    myRow = myCursor.execute(query, Start1, End)
    

    For queries with no parameter values you simply pass the query string by itself

    query = "SELECT col1, col2 FROM tablename"
    myRow = myCursor.execute(query)