I am confusing how can I code in Python.
Following code works from pgsql command line.
select * from consultation_tbl where consultation_status in ('S','C','R');
But in Python, I have no idea how to code.
chat_str = "\'S\',\'C\',\'R\'"
cursor.execute(" \
SELECT * FROM consultation_tbl
WHERE consultation_status IN ( %s )", [chat_str])
Please give me an advice.
asked Oct 12, 2017 at 13:53
Kazzz Studio
4591 gold badge7 silver badges19 bronze badges
-
1String substitution could lead to SQL Injection attacks.Lukasz Szozda– Lukasz Szozda2017年10月12日 13:53:47 +00:00Commented Oct 12, 2017 at 13:53
-
Possible duplicate of SQL query with variables in pythonwwii– wwii2017年10月12日 14:10:49 +00:00Commented Oct 12, 2017 at 14:10
2 Answers 2
Firstly, you can use single quotes within double quotes string, This is a valid Python literal string:
chat_str = "'S', 'C', 'R'"
But I would code like this:
# 1) This joins the tokens with ', ' separator. Very useful
chat_str = ', '.join(["'S'", "'C'", "'R'",])
# 2) We use the python3 format() method for strings.
# The '{}' is replaced. See the official docs
query = "SELECT * FROM consultation_tbl
WHERE consultation_status IN ({})".format(chat_str)
cursor.execute(query)
In both cases, the result string is equivalent.
Sign up to request clarification or add additional context in comments.
9 Comments
Kazzz Studio
Thank you for an advice. But I got following error.
django.db.utils.ProgrammingError: column "s" does not exist LINE 1: ...ation_tbl WHERE consultation_status IN (S, C, R) How can I fix it?madtyn
It seems like you have %s in your code and not the {} placeholder
Kazzz Studio
Thhank you for reply. I use {} in my code. I copy your code and paste it. What is wrong?
madtyn
Have you debugged this for checking which is the result string? I need to see what is getting postgresql.
Kazzz Studio
I may have to use %s if I use postgresql. "{}" doesn't work in my code.
|
Typically you don't want to put data into the query using hand-rolled sting substitution -- the python sql api lets you pass in a tuple which it will sanitize and put in for you to prevent sql injection. That being said since the list of parms in the IN clause can be dynamic in length you may still need string substitution to create a template query. I've usually seen it look something like this:
char_list = ['S', 'C', 'R' ]
qry = "SELECT * FROM consultation_tbl WHERE consultation_status IN ( %s )"
qry %= ",".join("%s" for x in range(len(char_list)))
cursor.execute(qry, chars)
answered Oct 12, 2017 at 14:00
gbtimmon
4,3721 gold badge24 silver badges36 bronze badges
1 Comment
Kazzz Studio
Thank you for an advice. Where chars come from ?
default