Python Passing string Parameters via for loop into sql execute

Question

Trying to figure out how to pass each string from my loop into SQL to loop through each country and create an excel file specific to each country (5 excel files each having there own specific country data). I found code that does a .join and IN() statement that worked, but that put all 5 countries in all 5 files.

Code:

import CX_Oracle
Import xlwt
country_list = ['PERU','GUATEMALA','BAHAMAS','BARBADOS']
for country in country_list:
 SQL = "SELECT * from TABLE WHERE COUNTRY = %s" % country
 cursor = con.cursor()
 cursor.execute(SQL)
 book = xlwt.Workbook()
 sheet1 = book.add_sheet('Summary')
 for i, row in enumerate(cursor):
 for j, col in enumerate(row):
 sheet1.write(i+1,j,col) #Starts pasting the data at row 2
 book.save('Output_' + country + '.xls')
Error "PERU" Invalid character. 

Answer 1

Even better than including quotes is avoiding explicit string formatting. Instead pass parameters as second argument in execute. Psycopg2 will handle it for you. This will save you from potential code injection and from mistakes like missed quotes as well

cursor.execute("SELECT * from TABLE WHERE COUNTRY = %s" , ('USA',))

http://initd.org/psycopg/docs/cursor.html#cursor.execute

Answer 2

In SQL, strings need to be surrounded by quotes '. In your query, the %s won't be surrounded by those after substitution.

Right now, your query would look like SELECT * from TABLE WHERE COUNTRY = PERU, while you're going for SELECT * from TABLE WHERE COUNTRY = 'PERU'. Simply surround %s with quotes.

(If your queries are giving you errors, often it's useful to print the final query after substitutions and such, to see if it would be valid SQL.)

• Matthew Myers: Thanks very much! I was working on this for hours and tried so many ways of putting the array in different string formats and it still couldn't get this to work.

Answer 3

You should include the single quotes for string literals, ie:

SQL = "SELECT * from TABLE WHERE COUNTRY = '%s'" % country