Postgresql statement in psycopg2 not returning expected values

Question

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?

Adrian Klaver · Accepted Answer · 2021-11-17 22:15:23Z

1

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.

edited Nov 17, 2021 at 22:15

answered Nov 17, 2021 at 22:04

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

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

3 Comments

jtam Over a year ago

Thank you. I had no idea that I couldn't use parameters for identifiers. But, I played around with it and I can if I alias the table name, i.e., this works: cursor.execute('SELECT DISTINCT a.state FROM example_table a'); Not sure why this would be allowed?

Adrian Klaver Over a year ago

What you show does not use parameters so yes it would work , as well as cursor.execute('SELECT DISTINCT state FROM example_table').

jtam Over a year ago

Oh. Duh. You're right. I thought I tried that (i.e., cursor.execute('SELECT DISTINCT state FROM example_table') and it didn't work, but just tried again and it did. Thanks for your help!

BlackCat · Accepted Answer · 2021-11-17 23:18:26Z

1

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)

answered Nov 17, 2021 at 23:18

BlackCat

191 silver badge5 bronze badges

Collectives™ on Stack Overflow

Postgresql statement in psycopg2 not returning expected values

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related