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

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?

pythonsqldatabasepostgresqlpsycopg2


Solution 1:[1]

ps_conn = psycopg2.connect(...)
ps_cursor = ps_conn.cursor()
ps_cursor.execute('''
    SELECT *
    FROM "posts"
    ORDER BY "posts"."rate" {order}, "posts"."date_created" ASC LIMIT %s
    '''.format(order='desc'),(1,))

Solution 2:[2]

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"))

Solution 3:[3]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ashkan Goleh Pour
Solution 2 Ramprakash
Solution 3 PhuriChal

Related Questions