Replacing a String with a python variable in sql query

Question

so I know that similar questions have been asked before, but the solutions posted do not seem to work for me, unless perhaps I am doing it wrong.

I want to change this:query1= ("SELECT anthologyID, title FROM newschema.item WHERE title LIKE 'asperges'" ) where the output is 51962, Asperges

into something like this:

asperges= "asperges" cursor.execute(query1,asperges) query1= ("SELECT anthologyID, title FROM newschema.item WHERE title LIKE '%s'")

problem: output is all wrong (I'm printing in a for loop on the cursor just for reference), and instead of getting just one match, I'm getting a match on all titles in that table.

what's up?

(because someone will probably tell me to do something like this: query1= ("SELECT anthologyID, title FROM newschema.item WHERE title LIKE '%s'" , asperges)

That query is giving me AttributeError: 'tuple' object has no attribute 'encode'

Thanks!

mviana · Accepted Answer · 2017-12-10 22:58:13Z

2

For cursor.execute you can give two arguments: first the string with the values to replace ("%s") and the second one is a tuple, so either you can throw this to your cursor (best option!):

cursor.execute("SELECT anthologyID, title FROM newschema.item WHERE title LIKE %s",('aspergers',))

DO NOT FORGET the comma after 'aspergers'! It converts your data to a tuple.

Or you can set a query and give it to the cursor:

query1= "SELECT anthologyID, title FROM newschema.item WHERE title LIKE %s"  % (asperges,)
cursor.execute(query1)

CHECK THE COMMA again.

answered Dec 10, 2017 at 22:58

mviana

764 bronze badges

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

2 Comments

Ore Arowobusoye Over a year ago

thank you! What I ended up doing was this: cursor.execute("SELECT anthologyID, title FROM newschema.item WHERE title LIKE %s" ,(asperges,)) what does replacing the percent with the comma do, also, a bit of a related question: how would I be able re-create something like %asperges% where I want to match other characters after the last s in 'asperges' in the LIKE thank you

mviana Over a year ago

If you want to write the '%' character, just double it '%%'. e.g.: "(...) WHERE title like '%%%s%%'"

Collectives™ on Stack Overflow

Replacing a String with a python variable in sql query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related