Querying a PostgreSQL table using Psycopg2

Psycopg2 the PostgreSQL adapter implements PEP 249 - Python Database API Specification.The example queries a PostgreSQL table connection_log and prints results.

PostgreSQL Updated 14 Mar 2018 2 min read

Overview

  • 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:

python
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.

Example

python
# 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

cur.execute(sqlSelect)

# 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]))

    print('=============================================')

Output

Output
ip_v4: 121.122.123.124

agent: YaBrowser

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

=============================================

ip_v4: 121.122.123.125

agent: Safari

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

=============================================

ip_v4: 121.122.123.125

agent: Safari

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

=============================================

ip_v4: 121.122.123.126

agent: Android Webview

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

=============================================