CODEWITHSUNDEEP
pythonpython-3.xsqlite
Oscar Dulzaides
Oscar Dulzaides

Reputation: 169

Passing python variable to sql

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.

Upvotes: 1

Views: 1655

Answers (2)

alecxe
alecxe

Reputation: 473813

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)

Upvotes: 2

Kevin
Kevin

Reputation: 76194

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

Upvotes: 1

Related Questions