Open In App

How to Log Queries in PostgreSQL using Python?

Improve
Improve
Like Article
Like
Save
Share
Report

Python has various database drivers for PostgreSQL. Current most used version is psycopg2. It fully implements the Python DB-API 2.0 specification. The psycopg2 provides many useful features such as client-side and server-side cursors, asynchronous notification and communication, COPY command support, etc.

Installation

Install the psycopg2 module :

pip install psycopg2

For logging the information about the commands, we need to install logtopg:

pip install logtopg

Application 

  • Information gathering
  • Troubleshooting
  • Generating statistics
  • Auditing
  • Profiling

Logging levels and their purpose

Level Purpose
DEBUG Detailed information, typically of interest only when diagnosing problems.
INFO Confirmation that things are working as expected.
WARNING An indication that something unexpected happened, or indicative of some problem in the near future (e.g. ‘disk space low’). The software is still working as expected.
ERROR Due to a more serious problem, the software has not been able to perform some function.
CRITICAL A serious error, indicating that the program itself may be unable to continue running.

All loggers are descendants of the root logger. The root logger always has an explicit level set, which is WARNING by default. Root logger can be used to easily turn all loggers from all libraries on and off.

Logger information display  

Level Display
debug Should only appear in the file
info Should appear in file
warning Should appear in file and stdout
error Should appear in file and stdout
critical Should appear in file and stdout

   
To understand logging better here is a simple code without connecting to PostgreSQL, which just displays messages in console.

Example:

Python3




import logging
  
# This will be printed only in file
logging.debug('This is a debug message')
  
# This will be printed only in file
logging.info('This is an info message')
  
logging.warning('This is a warning message')
logging.error('This is an error message')
logging.critical('This is a critical message')


Output:

Root logger was used and only three messages were written. This is because by default, only messages with level warning and up are written.

Given below is one more example to understand logging levels. The logging level is set with setLevel(). 

Example 1:

Python3




import logging
  
# The getLogger() returns a logger with the specified name.
# If name is None, it returns the root logger.
logger = logging.getLogger('dev')
  
# Level is set
logger.setLevel(logging.DEBUG)
  
logger.debug('This is a debug message')
logger.info('This is an info message')
logger.warning('This is a warning message via setLevel')
logger.error('This is an error message via setLevel')
logger.critical('This is a critical message via setLevel')


Output :

getLogger and setLevel

Example 2 :

“mylib.py” should be in the same directory where “sample.py” exists and also the created file “myapp.log” will be present in the same directory where mylib.py and sample.py present

mylib.py

Python3




#Let this program name be mylib.py
import logging
  
def from_mylib():
    logging.info('I am from mylib')


Let us use mylib.py in another code 

sample.py

Python3




#logging module is required to log information
import logging
  
# User created python code and should be in same directory
import mylib
  
def main():
  
    # As we have specified myapp.log, all log information will be there
    # logging.INFO is the level, it will get printed
    logging.basicConfig(filename='myapp.log', level=logging.INFO)
    logging.info('Started to print logger info')
      
    # calling mylib method here as have imported. It should be
    # in same directory
    mylib.from_mylib()
    logging.info('Finished logging the information!!!')
  
# Main code
if __name__ == '__main__':
    main()


Output of myapp.log (that is created in the same directory where mylib.py and sample.py) present

myapp.log contents

Example 3:

This example discusses interaction with PostgreSQL with database. pgAdmin or psql are the client tools one can used to log into the PostgreSQL database server.

DATABASE NAME: testdb

TABLE NAME: employee

Python3




import logging
import psycopg2
from psycopg2.extras import LoggingConnection
  
logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger("loggerinformation")
  
# We need to specify the necessary parameters in the below list
# As connecting with postgres/password as username/password and with testdb as parameters
db_settings = {
    "user": "postgres",
    "password": "password",
    "host": "127.0.0.1",
    "database": "testdb",
}
  
# connect to the PostgreSQL server
conn = psycopg2.connect(connection_factory=LoggingConnection, **db_settings)
conn.initialize(logger)
  
cur = conn.cursor()
cur.execute("SELECT * FROM employee")


Output :

Example 4:

Python3




import logging
import psycopg2
from psycopg2.extras import LoggingConnection
  
logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger("loggerinformation")
  
db_settings = {
    "user": "postgres",
    "password": "password",
    "host": "127.0.0.1",
    "database": "testdb",
}
  
conn = psycopg2.connect(connection_factory=LoggingConnection, **db_settings)
conn.initialize(logger)
  
cur = conn.cursor()
cur.execute("SELECT * FROM employee")
  
# insert records in employee table
cur.execute(
    "INSERT INTO employee (first_name,last_name,age,gender,income) VALUES ('123','456',20,'m',20000)")


Output:

select and insert query log



Last Updated : 11 Dec, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads