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.
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.
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?
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)
