Query formatting using pymssql

Question

I am trying to use a variable with pymssql to retrieve a database query in 2.7 Python. I run into a syntax error when trying to run the script. The script is below:

school = 'Some School'
sql_statement = '''
            SELECT TOP 10
              a.Item_Name,
              sum(a.Sales) 'Total_Spend'
            FROM some_DB a
            WHERE
              a.School_Name = {} 
            GROUP BY a.Health_Rating, a.Item_Name
            ORDER BY 2 DESC;
        '''.format(school)

There is no problem with the connection, so I have excluded that code.

It only errors when I try to add the variable using the .format() method. Any help into this would be greatly appreciated.

Cheers.

score 1 · Accepted Answer · answered Jun 16 '17 at 01:51

I found this solution, feel free to correct me if necessary. After some research, I found that the formatting operator can be subject to SQL injection and does not allow escaping. Information gained from a previous question variables with python and sql

school = 'Some School'
sql_statement = '''
            SELECT TOP 10
              a.Item_Name,
              sum(a.Sales) 'Total_Spend'
            FROM some_DB a
            WHERE
              a.School_Name = (%s) 
            GROUP BY a.Health_Rating, a.Item_Name
            ORDER BY 2 DESC;
        '''
cursor.execute(sql_statement, school)

Query formatting using pymssql

1 Answers1