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.

user4600699 · Accepted Answer · 2015-02-24 13:01:49Z

2

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

edited Feb 24, 2015 at 13:01

answered Feb 24, 2015 at 12:45

user4600699

1,48511 silver badges10 bronze badges

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

Comments

Martijn Arts · Accepted Answer · 2015-02-24 12:45:02Z

0

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.)

answered Feb 24, 2015 at 12:45

Martijn Arts

7437 silver badges22 bronze badges

1 Comment

Matthew Myers Over a year ago

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.

Selcuk · Accepted Answer · 2015-02-24 12:43:16Z

0

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

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

answered Feb 24, 2015 at 12:43

Selcuk

60.1k12 gold badges114 silver badges119 bronze badges

Collectives™ on Stack Overflow

Python Passing string Parameters via for loop into sql execute

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related