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

Question

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, how can I do this without any parameters in query.format?

score 1 · Accepted Answer · answered May 15 '19 at 12:03

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)

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

1 Answers1