Querying A PostgreSQL Table Using Psycopg2


  • PostgreSQL is a powerful open source database management system in terms of number of features supported from the SQL standards and the size of the user base.
  • PostgreSQL is a fully ACID compliant database management system which adheres to the SQL: 2008 standard.
  • Psycopg2 is the database adapter developed for interfacing with the PostgreSQL from any Python Program.
  • This article explains the steps involved in querying a Psycopg2 from a Python program and retrieving a set of rows matching a specified where clause.


Installing psycopg2:

psycopg2 can be installed using pip as given below:


pip install psycopg2-binary



Querying from PostgreSQL table using Psycopg:

  • The factory function connect() of module Psycopg2 is used for creating a connection object.
  • The connect() function accepts parameters corresponding to the PostgreSQL database name and the authentication details of the PostgreSQL User.
  • A cursor object is created using the obtained connection object.
  • Using a cursor object any SQL Statements supported by the PostgreSQL can be executed.
  • Cursors in Psycopg are not thread safe. The thread safety while using the same cursor object across multiple Python threads need to be checked with the Psycopg documentation.
  • With a call to the execute () method of the cursor, a SQL statement supported by the PostgreSQL can be executed.
  • Upon calling execute() method, one among the data retrieval methods like fetchone(), fetchmany() and fetchall() is called to get the actual row data for processing inside the Python program.


# import the PostgreSQL adapter

import psycopg2


# Connect to the PostgreSQL database

conn    = psycopg2.connect("dbname=test user=test password=''")


# Obtain a cursor object from the PostgreSQL database connection

cur     = conn.cursor()


# SQL statement to query from a PostgreSQL database table

sqlSelect = "SELECT * from connection_log"


# Query a PostgreSQL table



# Retrieve all the rows from the cursor

rows = cur.fetchall()


for row in rows:

    print('ip_v4: %s' %(row[0]))

    print('agent: %s' %(row[1]))

    print('connected_time: %s' %(row[2]))






agent: YaBrowser

connected_time: 2018-03-14 12:30:59.110161



agent: Safari

connected_time: 2018-03-14 12:31:40.609902



agent: Safari

connected_time: 2018-03-14 12:31:43.159651



agent: Android Webview

connected_time: 2018-03-14 12:32:13.672064



Copyright 2022 © pythontic.com