Postgresql statement in psycopg2 not returning expected values-CodePudding

I have a postgresql database established. I run the following code to get unique values in the 'state' column of the table (these are strings that represent FIPS numbers):

import psycopg2 as ps
con = ps.connect("dbname=example_db user=user password=password")
cursor = con.cursor()
cursor.execute('SELECT DISTINCT(%s) FROM example_table', ('state',))
results = cursor.fetchall()

This returns:

results
('state',)

If I run the same query in PGAdmin4 I get:

example_db=# SELECT DISTINCT state FROM example_table;

state
-------
06
(1 row)

I want to get the distinct values (i.e., 06, in this case) using psycopg2. What do I need to change?

CodePudding user response：

Example:

from psycopg2 import sql
cursor.execute(sql.SQL('SELECT DISTINCT {} FROM example_table').format(sql.Identifier('state')))

The issue is state is an identifier(column name) and you can't use parameters to include that in the query. You need to use the psycopg2.sql module to build the identifier into the query.

CodePudding user response：

Names of database objects, such as table or column names, cannot pass as SQL string literals for escaping. See the psycopg2.sql module

import psycopg2 as ps
con = ps.connect("dbname=example_db user=user password=password")
cursor = con.cursor()
query = ps.sql.SQL('SELECT DISTINCT {column_name} FROM example_table').format(column_name=sql.Identifier('state'))
cursor.execute(query)