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.

matoneski · Accepted Answer · 2017-06-16 01:51:03Z

1

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)

answered Jun 16, 2017 at 1:51

matoneski

9281 gold badge9 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query formatting using pymssql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related