Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

python
postgresql
communitycreator

How to Log queries in PostgreSQL using Python

Njoku Ifeanyi Gerald

Overview

PostgreSQL is an object-relational database that uses and extends the SQL language to scale and store big, complicated data in the database.

In this shot, we’ll learn to log queries in PostgreSQL using python.

Installation

pip install pipenv 
pipenv shell
pipenv install psycopg2
pipenv install LogToPG

Note: LogToPG is a Python logging handler that stores logs in postgresql.

Code

Create a file and name it app.py.

import logging
import psycopg2
from psycopg2.extras import LoggingConnection

# setting debug  to True
logging.basicConfig(level=logging.DEBUG)
#referencing  the logger information
logger = logging.getLogger("loggerinformation")

# As connecting with postgres/password as username/password and with testdb as parameters
db_settings = {
	"user": "postgres",
	"password": "12345",
	"host": "127.0.0.1",
	"database": "postgres",
}

# creating a table
sql = '''CREATE TABLE school(
 id  SERIAL NOT NULL,
 name varchar(60) not null,
 department varchar(200) not null
)'''
 



# the data to be inserted into the table
data = [('Gerald', 'Biochemistry'), ('Wisdom', 'matritime'), 
        ('Donald', 'material and metallogical engineering '), ('Andrew', 'Food and Science Technology'),
        ('Alexander', 'Agricultural and Bioresources engineering')]
  

# connect to the PostgreSQL server
conn = psycopg2.connect(connection_factory=LoggingConnection, **db_settings)

# intializing the logging of the PostgreSQL database inserted data
conn.initialize(logger)


# cursor() = an object which helps to execute 
# the query and fetch the records from the database
cur = conn.cursor()

# selecting all from the already created school table
cur.execute(sql)

# inserting record into school table
for d in data:
    cur.execute("INSERT into school(name, department) VALUES (%s, %s)", d)
    print("List has been inserted to school table successfully...")

# Commit your changes in the database
conn.commit()
# Closing the connection
conn.close()

Go to your terminal, navigate to the directory where you have created the file and, then type:

python app.py

Note: It logged each data respectively.

RELATED TAGS

python
postgresql
communitycreator

CONTRIBUTOR

Njoku Ifeanyi Gerald
RELATED COURSES

View all Courses

Keep Exploring