how to pass asc desc as params in python psycopg2 raw sql execution?-CodePudding

i have a sample query template to accept dynamic parameters to execute:

ps_conn = psycopg2.connect(...)
ps_cursor = ps_conn.cursor()
ps_cursor.execute('''
    SELECT *
    FROM "posts"
    ) ORDER BY "posts"."rate" %s, "posts"."date_created" ASC LIMIT 1
    ''', ["DESC"])

as you can see i wanna pass DESC dynamically to orderby the results , but the above code keep throwing the error InvalidSchemaName

schema "posts" does not exist LINE 11:) ORDER BY "posts"."rate" 'DESC',

it passes the DESC as 'DESC' to the sql .

any opinion how to perform this functionality so i can pass ordering type dynamically?

CodePudding user response：

Update your query like this and try:

ps_cursor.execute('''
SELECT *
FROM "posts"
) ORDER BY "posts"."rate" {0}, "posts"."date_created" ASC LIMIT 1
'''.format("DESC"))

CodePudding user response：

I would instead create a separate variable and code as follows:

ps_conn = psycopg2.connect(...)
ps_cursor = ps_conn.cursor()

# you may add a condition to use DESC or not
dynamic_sort = 'DESC'
ps_cursor.execute('''SELECT *
                     FROM posts
                     ORDER BY rate %s, date_created ASC 
                     LIMIT 1''',
                  (dynamic_sort))