Querying A PostgreSQL Table Using Psycopg2

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:

 

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:

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

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

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

 


Copyright 2020 © pythontic.com