I think you're using the query parameters incorrectly. Pass a list instance as a parameter to the execute function to pass your query parameters. From memory the psycopg2 manual explicitly discourages doing this in the manner you were trying. Try something closer to this:

import psycopg2

someuserid = "u62194"

conn = psycopg2.connect(
    user = "postgres",
    password = "xxxxxx",
    host = "127.0.0.1",
    port = "5432",
    database = "mydb"
)

cur = conn.cursor()

# Use a list here to insert query parameters into the query string.
cur.execute(
    """
    SELECT userid 
    FROM myusers u
    WHERE u.userid = %s;
    """,
    [someuserid,]
)

result = cur.fetchone()

print(result)

cur.close()
Answer from ajxs on Stack Overflow
🌐
Psycopg
psycopg.org › docs › usage.html
Basic module usage — Psycopg 2.9.12 documentation
>>> psycopg2.extensions.register_type(psycopg2.extensions.UNICODE, cur) >>> cur.execute("SELECT data FROM test WHERE num = 74") >>> x = cur.fetchone()[0] >>> print(x, type(x), repr(x)) àèìòù€ <type 'unicode'> u'\xe0\xe8\xec\xf2\xf9\u20ac' In the above example, the UNICODE typecaster is registered only on the cursor.
🌐
Stack Overflow
stackoverflow.com › questions › 57343510 › how-to-use-a-string-variable-in-psycopg2-select-query
python - How to use a string variable in psycopg2 select query? - Stack Overflow
Top answer
1 of 1
9

I think you're using the query parameters incorrectly. Pass a list instance as a parameter to the execute function to pass your query parameters. From memory the psycopg2 manual explicitly discourages doing this in the manner you were trying. Try something closer to this:

import psycopg2

someuserid = "u62194"

conn = psycopg2.connect(
    user = "postgres",
    password = "xxxxxx",
    host = "127.0.0.1",
    port = "5432",
    database = "mydb"
)

cur = conn.cursor()

# Use a list here to insert query parameters into the query string.
cur.execute(
    """
    SELECT userid 
    FROM myusers u
    WHERE u.userid = %s;
    """,
    [someuserid,]
)

result = cur.fetchone()

print(result)

cur.close()
python - How to pass parameterized statements with values and table names to Psycopg2?
postgresql - psycopg2 cursor.execute() pass in variable table names and items
python - Error in exceuting psql command using psycopg2?
python - How do I query a postgres-db view with psycopg2?
python 3.x - WHERE IN psycopg2 clause not formatting
More results from stackoverflow.com
Discussions
Help with properly executing SELECT * query with psycopg2
cur.execute returns None. You need to explicitly fetch the results using a for loop on the cursor object. More on reddit.com
🌐 r/learnpython
2
2
March 1, 2018
Parameterized queries with psycopg2: SELECT from a Python list - Stack Overflow
Using your example, use where id in and then pass a parameter which is a tuple of the id values you want to select: >>> lst2 [{'id': 97600167}, {'id': 97600168}, {'id': 97611194}] >>> ids = tuple(x['id'] for x in lst2) >>> cur.execute("SELECT id, parent_id FROM my_table where id in %s",[ids]) ... Sign up to request clarification or add additional context in comments. ... Thanks, but It gives me a psycopg2... More on stackoverflow.com
🌐 stackoverflow.com
(Python psycopg) How do I add additional WHERE … AND … conditions to a SQL statement?
If I understand the use case, you’re having some user provided parameters passed in, but allowing them to be optional, which is why you want the AND statements to be pasted together. In that case, it’s best to add the AND statement in the original query, but structure it like this: AND (:var IS NULL OR columnname = :var) So, even if the parameter isn’t provided, your AND statement still works. Edit: that’s not verbatim, you’d replace :var with whatever formatting you’d need for a parameterized query. More on reddit.com
🌐 r/PostgreSQL
9
1
December 2, 2022
Fetching by batch (1M+ records)
Fetching by batch is pretty common. If you have an index on your primary key, you can just start with a query like this: SELECT * FROM my_table ORDER BY id LIMIT 50; Then for every next batch, do this: SELECT * FROM my_table WHERE id > (max id from previous query) ORDER BY id LIMIT 50; All you need to do is substitute the highest id of the previous batch and your next batch of 50 will be the next chunk of data. You don't need a cursor to do this, and 50 rows is small enough that it should always use an index and come back very quickly. More on reddit.com
🌐 r/PostgreSQL
12
2
February 24, 2024
Videos
12:54
🌐 YouTube
How to connect to PSQL Database using psycopg2 + Python - YouTube
November 26, 2022
19:18
🌐 YouTube
psycopg2 python Connect, fetch data, pass SQL query ...
December 15, 2021
16:59
🌐 YouTube
Connecting to a PostgreSQL database from Python with Psycopg2 - ...
December 12, 2019
18:10
🌐 YouTube
Step-by-Step Python and Postgres Tutorial with psycopg2 - YouTube
January 6, 2019
83.7K
🌐 youtube.com
Extra: Psycopg2 Example Usage | The Complete SQL ...
32:25
🌐 YouTube
Base de données : PostgreSQL et Python (psycopg2) - YouTube
August 12, 2022
4.66K
View all
View all
🌐
PostgreSQL Wiki
wiki.postgresql.org › wiki › Psycopg2_Tutorial
Psycopg2 Tutorial - PostgreSQL wiki
The snippet below illustrates accessing the results of the SELECT statement by column. #!/usr/bin/env python # # Small script to show Pyscopg2 cursor with dictionary # import psycopg2 # note the extras import import psycopg2.extras try: conn = psycopg2.connect("dbname='dbtest' user='dbuser' host='localhost' password='dbpass'") except: print("I am unable to connect to the database") # pass the DictCursor factory to the cursor with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as curs: try: curs.execute("SELECT * FROM postgresqldotorg") pg_rows = curs.fetchall() # loop on the results for pg_row in pg_rows: # access the column by name print(f"{pg_row['page_name']}") except (Exception, psycopg2.DatabaseError) as error: print(error)
🌐
PYnative
pynative.com › home › python › databases › python select from postgresql table using psycopg2
Python Select from PostgreSQL Table using psycopg2
March 9, 2021 - If the where condition is used, then it decides the number of rows to fetch. For example, SELECT col1, col2,…colnN FROM postgresql_table WHERE id = 5;. This will return row number 5.
🌐
GeeksforGeeks
geeksforgeeks.org › python › python-select-from-postgresql-table-using-psycopg2
Python Select from PostgreSQL Table using Psycopg2 - GeeksforGeeks
July 23, 2025 - This article will introduce you to the use of the psycopg2 module which is used to connect to a PostgreSQL database from Python. We will look over how to establish a connection to a database, create a cursor object to execute DBMS SQL statements, execute a SELECT statement to retrieve the data from a table, and create a loop through the rows which are returned by the SELECT statement to process the following data.
🌐
PostgreSQL Wiki
wiki.postgresql.org › wiki › Using_psycopg2_with_PostgreSQL
Using psycopg2 with PostgreSQL - PostgreSQL wiki
#!/usr/bin/python import psycopg2 ... print "Connected!\n" if __name__ == "__main__": main() This example shows how to connect to a database, and then obtain and use a cursor object to retrieve records from a table....
🌐
IQCode
iqcode.com › code › other › psycopg2-select-example
psycopg2 select example Code Example
October 19, 2021 - psycopg2 select with params psycopg2 select data from table postgre select with IN psycopg2 conn = psycopg2 &quot;&quot;&quot;select * psycopg2 query select psycopg2 postgre select psycopg select example copy_from psycopg2 example psycopg2 realdictrow psycopg2 select from table where select in psycopg2 psycopg2 get data from table psycopg2 get all returned rows psycopg2 fetchall select postgres select query python psql query psycopg2 psycopg2 execute select into list python psycopg2 select psycopg2 tutorial pg-cursor example python python query postgresql database psycopg2 fetc run postges sta
Find elsewhere
🌐
TutorialsPoint
tutorialspoint.com › python_data_access › python_postgresql_select_data.htm
Python PostgreSQL - Select Data
import psycopg2 #establishing the connection conn = psycopg2.connect( database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432' ) #Setting auto commit false conn.autocommit = True #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving data cursor.execute('''SELECT * from EMPLOYEE''') #Fetching 1st row from the table result = cursor.fetchone(); print(result) #Fetching 1st row from the table result = cursor.fetchall(); print(result) #Commit your changes in the database conn.commit() #Closing the connection conn.close()
🌐
Neon
neon.com › postgresql › postgresql-python › query
PostgreSQL Python: Querying Data
May 19, 2024 - The following get_vendor() function ...ct(**config) as conn: with conn.cursor() as cur: cur.execute("SELECT vendor_id, vendor_name FROM vendors ORDER BY vendor_name") print("The number of parts: ", cur.rowcount) row = cur.fetchone() ...
🌐
GeeksforGeeks
geeksforgeeks.org › executing-sql-query-with-psycopg2-in-python
Executing SQL query with Psycopg2 in Python - GeeksforGeeks
October 17, 2021 - In this article, we are going to see how to execute SQL queries in PostgreSQL using Psycopg2 in Python.
🌐
TigerData
tigerdata.com › blog › when-and-how-to-use-psycopg2
When and How to Use Psycopg2 | Tiger Data
December 9, 2025 - SELECT time_bucket('1 hour', time) as one_hour_interval, location, AVG(temperature) as avg_temperature FROM conditions WHERE temperature > 76 GROUP BY one_hour_interval, location ORDER BY one_hour_interval DESC, avg_temperature DESC; In this comprehensive guide, we've delved into the world of Psycopg2 and how it can help you connect to your PostgreSQL database from your Python application.
🌐
ZetCode
zetcode.com › python › psycopg2
Python PostgreSQL proramming with psycopg2 module
January 29, 2024 - $ parameterized_query.py Number of rows updated: 1 testdb=> SELECT * FROM cars WHERE id=1; id | name | price ----+------+------- 1 | Audi | 62300 (1 row) The price of the car was updated. We check the change with the psql tool. The second example uses parameterized statements with Python extended ...
🌐
Reddit
reddit.com › r/learnpython › help with properly executing select * query with psycopg2
r/learnpython on Reddit: Help with properly executing SELECT * query with psycopg2
March 1, 2018 -

Hi All,

So I've got a simple postgres database setup that I populated using the Python psycopg2 library.

I want to run a SELECT * query using psycopg2's cursor object, however None is returned when I run the query. This is odd because running the same query from psql command line returns my database.

Basically, here's my code that only produces None

conn = psycopg2.connect(dbname='cooking', user='me', host='localhost')
cur = conn.cursor()
print(cur.execute("SELECT * FROM dishes;"))
cur.close()
conn.close()

And here is a work around I produced using Pandas.

conn = psycopg2.connect(dbname='cooking', user='sean', host='localhost')
sql_query = "SELECT * FROM dishes"
print(pd.read_sql(sql_query, con=conn))
conn.close()

This gives me the desired output. Can someone aid me so that I only need psycopg2, and can avoid the overhead associated with Pandas?

Top answer
1 of 1
3
cur.execute returns None. You need to explicitly fetch the results using a for loop on the cursor object.
🌐
Psycopg
psycopg.org › docs › sql.html
psycopg2.sql – SQL string composition — Psycopg 2.9.12 documentation
>>> print(sql.SQL("select * from {} where {} = %s") ... .format(sql.Identifier('people'), sql.Identifier('id')) ... .as_string(conn)) select * from "people" where "id" = %s >>> print(sql.SQL("select * from {tbl} where {pkey} = %s") ... .format(tbl=sql.Identifier('people'), pkey=sql.Identif...
🌐
PYnative
pynative.com › home › python › databases › python postgresql tutorial using psycopg2
Python PostgreSQL Tutorial Using Psycopg2 [Complete Guide]
March 9, 2021 - In our example, we are executing a SELECT version(); query to fetch the PostgreSQL version. Using the Error class of Psycopg2, we can handle any database error and exception while working with PostgreSQL from Python.
🌐
Stack Overflow
stackoverflow.com › questions › 6018966 › parameterized-queries-with-psycopg2-select-from-a-python-list
Parameterized queries with psycopg2: SELECT from a Python list - Stack Overflow
Top answer
1 of 1
8

Using your example, use where id in and then pass a parameter which is a tuple of the id values you want to select:

>>> lst2
[{'id': 97600167}, {'id': 97600168}, {'id': 97611194}]
>>> ids = tuple(x['id'] for x in lst2)
>>> cur.execute("SELECT id, parent_id FROM my_table where id in %s",[ids])
🌐
CSE CGI Server
cgi.cse.unsw.edu.au › ~cs3311 › 22T3 › content › psycopg2 › slides.html
Psycopg2
COMP3311 20T3 ♢ Psycopg2 ♢ ... statement · results are available via the cursor's fetch methods Examples: # run a fixed query cur.execute("select * from R where x = 1"); # run a query with values inserted cur.execute("select * from R where x = %s", (1,)) cur.execute("select ...
🌐
Medium
medium.com › @interviewbuddies › postgresql-data-handling-using-pythons-psycopg2-062ce19abe1e
PostgreSQL Data handling using Python’s psycopg2 | by InterviewBuddies | Medium
May 6, 2024 - psycopg2 provides methods like fetchone(), fetchall(), and fetchmany() to retrieve query results from PostgreSQL tables. Example demonstrating the execution of SELECT queries and fetching results using psycopg2’s cursor.
🌐
GitHub
gist.github.com › jakebrinkmann › de7fd185efe9a1f459946cf72def057e
Read SQL query from psycopg2 into pandas dataframe · GitHub
import pandas as pd import psycopg2 with psycopg2.connect("host='{}' port={} dbname='{}' user={} password={}".format(host, port, dbname, username, pwd)) as conn: sql = "select count(*) from table;" dat = pd.read_sql_query(sql, conn)
🌐
Reddit
reddit.com › r/postgresql › (python psycopg) how do i add additional where … and … conditions to a sql statement?
r/PostgreSQL on Reddit: (Python psycopg) How do I add additional WHERE … AND … conditions to a SQL statement?
December 2, 2022 -

Using psycopg, I’d like to build a SQL statement according to certain conditions. There would be a base SELECT query, and then append WHERE. And then append AND. And then append another AND, etc.

I appreciate help on this!

Top answer
1 of 2
2
If I understand the use case, you’re having some user provided parameters passed in, but allowing them to be optional, which is why you want the AND statements to be pasted together. In that case, it’s best to add the AND statement in the original query, but structure it like this: AND (:var IS NULL OR columnname = :var) So, even if the parameter isn’t provided, your AND statement still works. Edit: that’s not verbatim, you’d replace :var with whatever formatting you’d need for a parameterized query.
2 of 2
1
I’m thinking I can maybe just incrementally add to a string sql with %s included and append to a parameter list params at the same time before finally running cursor.execute(sql,params) Edit: People seem to be misunderstanding what I’m saying. Here’s an example: sql = “select * from t” params = [] if condition: sql += “ where a = %s” params.append(first_filter) If condition_2: sql += “ and b = %s” params.append(second_filter) cursor.execute(sql,params) I’m not saying to include unsanitized input in the sql string.
Related queries
psycopg2 select query with parameters
psycopg2 connect
psycopg2 documentation
python postgresql select query with where clause
psycopg2 fetchall to list
psycopg2 parameterized query in list
psycopg2 executemany example
psycopg2 fetchone example