I want to extract specific value from a column in a database which is hosted on PSQL-CodePudding

I would want to extract specific value from a column in a table but I cannot do that because the way that I do it in python keeps the specific value inside "" instead of ''. So I was wondering if there is any way to do it. In the following you can see the snippet of the query which I have tried.

query = sql.SQL("SELECT {fields} FROM {table} WHEN {col_name} = {var}").format(fields=sql.SQL(',')
        .join([sql.Identifier(cols[0]),sql.Identifier(cols[1]),]),table=sql.Identifier(table),col_name=sql
        .Identifier(cols[1]),var=sql.Identifier(var))

and the following is the printed query using

print(query.as_string(conn))

SELECT "column1","column2" FROM "table" WHEN "column2" = "string_value"

I was wondering if I could have something like "column2" = 'string_value', which would solve my problem.

Thanks in advance.

CodePudding user response：

var should be a sql.Placeholder, which is going to be inserted by psycopg2 while executing the query:

from psycopg2 import sql

cols = ('c1', 'c2')
table = "foo"
var = "value"

query = sql.SQL("SELECT {fields} FROM {table} WHEN {col_name} = {var}").format(
    fields=sql.SQL(',').join(
        [
            sql.Identifier(cols[0]),
            sql.Identifier(cols[1]),
        ]
    ),
    table=sql.Identifier(table),
    col_name=sql.Identifier(cols[1]),
    var=sql.Placeholder()
)

print(query.as_string(con))
print(cur.mogrify(query, (var, )).decode('utf-8'))
# cur.execute(query, (var, ))

Out:

SELECT "c1","c2" FROM "foo" WHEN "c2" = %s
SELECT "c1","c2" FROM "foo" WHEN "c2" = 'value'