Passing python variable to sql

Question

I have a python script that I want to extract data to a list from a DB, and then print it out.

Here is the code:

sql = "SELECT * FROM example WHERE column1 == 'string_value' AND column2 = float_value AND column3 == 'string_value'"
 # Assemble list.
query = []
for row in c.execute(sql):
    query.append(row)

This runs fine. When I debug i see sql is a string and the loop runs fine and assembles the list. When I however try to use variables for the sql query columns values using string formatting like this:

sql = "SELECT * FROM example WHERE column1 = %s AND column2 = %s  AND column3 = %s ",(var1_str, var2_float, var3_str)

I get a:

 for row in c.execute(sql):
ValueError: operation parameter must be str

and I cannot run the loop to assemble the list. Debugging this I see 'sql' is now a tuple and not a string.Why is that? Is there a better way to do this than what I am using. This is my first venture into SQL and Python so I am new to this.

Thanks you for any suggestions.

alecxe · Accepted Answer · 2016-01-08 19:13:00Z

2

ValueError: operation parameter must be str

What is happening is that sql variable is a tuple, but execute() expects to see a string as the first positional parameter. What you meant was to parameterize the query by passing the query parameters to execute() separately:

sql = "SELECT * FROM example WHERE column1 = %s AND column2 = %s  AND column3 = %s "
c.execute(sql, (var1_str, var2_float, var3_str))

for row in c.fetchall():
    query.append(row)

answered Jan 8, 2016 at 19:13

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

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

1 Comment

Oscar Dulzaides Over a year ago

Thanks, This worked perfect... much cleaner as well.

Kevin · Accepted Answer · 2016-01-08 18:56:01Z

1

"AND column3 = %s ",(var1_str, var2_float, var3_str)

String formatting requires a percent sign.

"AND column3 = %s " % (var1_str, var2_float, var3_str)

Not that I condone using string formatting when forming a sql query. If any of those values come from an untrusted source, you're opening yourself up to SQL injection attacks. Check the documentation of your DB interface to see if it has a safer way of including parameters.

answered Jan 8, 2016 at 18:56

Kevin

76.5k13 gold badges141 silver badges168 bronze badges

1 Comment

Oscar Dulzaides Over a year ago

Correct indeed. I had the syntax wrong. I did however still get an error due to the rest of my code and the solution above did provide the solution by using the 'fetchall()' method. Thanks for the help.

Collectives™ on Stack Overflow

Passing python variable to sql

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related