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

Question

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?

Ramprakash · Accepted Answer · 2022-05-05 06:58:09Z

1

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

answered May 5, 2022 at 6:58

Ramprakash

1811 silver badge8 bronze badges

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

1 Comment

atabak hooshangi Over a year ago

yeah i realized when we use string formatting the passed string will not get quotation around it .

Phuri Chalermkiatsakul · Accepted Answer · 2022-05-05 07:03:58Z

1

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

answered May 5, 2022 at 7:03

Phuri Chalermkiatsakul

6016 silver badges11 bronze badges

Comments

Ashkan Goleh Pour · Accepted Answer · 2022-05-10 04:43:57Z

0

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

answered May 10, 2022 at 4:43

Ashkan Goleh Pour

5224 silver badges14 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related