Formatting SQL query

Question

I am trying to make a query for my PostgreSQL database.

I think the format of my query is wrong and I can't seem to get it to work, I have posted my code below:

 query = cur.execute('SELECT "KINASE_NAME" FROM public."Phosphosite_table"
    WHERE "GENE_NAME" LIKE %(genename)s AND "RESIDUE" LIKE %(location)s')

The aim is to take the kinase name is the gene name and location match.

my error message appears as the following:

    ProgrammingError                          Traceback (most recent call last)
<ipython-input-33-9eae43b913d6> in <module>()
     35 cur = connection.cursor()
     36 
---> 37 query = cur.execute('SELECT "KINASE_NAME" FROM public."Phosphosite_table" WHERE "GENE_NAME" LIKE%(genename)s AND "RESIDUE" LIKE %(location)s')

Thanks!

Connor

Sorry, just to add, the variable is a list of strings that contain numbers within the strings if that helps. — Connor Knight
– Connor Knight, Commented Feb 12, 2019 at 14:31
Please tag your request with the API you are using. According to Lutz Horn it is psycopg (or psycopg2)? — Thorsten Kettner
– Thorsten Kettner, Commented Feb 12, 2019 at 14:58

score 1 · Accepted Answer · 2019-02-12 14:30:31Z

1

Don't use string operations to build SQL queries. Use the proper %s syntax.

genname = "foo"
location = "bar"
cur.execute("SELECT ... LIKE %s and ... LIKE %s", (genname, location))

No quotes around the values must be used. The quoting will be done by the DB API library.

edited Feb 12, 2019 at 14:30

answered Feb 12, 2019 at 14:29

user11044402

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

2 Comments

Connor Knight Over a year ago

Thank you, Lutz, for your response. I tried the code that you posted: cur.execute('SELECT "KINASE_NAME" FROM public."Phosphosite_table" LIKE %s and LIKE %s', (genename, location)). and got an error: ProgrammingError Traceback (most recent call last) <ipython-input-38-d21250cd0d10> in <module>() ProgrammingError: syntax error at or near "LIKE" LINE 1: ...ECT "KINASE_NAME" FROM public."Phosphosite_table" LIKE '[''1...

user11044402 Over a year ago

... FROM public."Phosphosite_table" WHERE "GENE_NAME" LIKE ...

Collectives™ on Stack Overflow

Formatting 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