How to Log Queries in PostgreSQL using Python?
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.
Install the psycopg2 module :
pip install psycopg2
For logging the information about the commands, we need to install logtopg:
pip install logtopg
- Information gathering
- Generating statistics
Logging levels and their 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
|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.
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 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
Let us use mylib.py in another code
Output of myapp.log (that is created in the same directory where mylib.py and sample.py) present
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