A professional tool to connect to a PostgreSQL database, extract table data, and save them as JSON files.
It handles empty tables gracefully by logging them without creating empty JSON files.
- Connect to PostgreSQL database
- Extracts data from each table
- Saves table data as JSON files inside
output/ - Logs empty tables instead of creating empty files
- Logs all operations inside
output/folder - Supports SSH database connection (commented in script for optional use)
- Configurable via
.envfile - All functions and methods are well-commented with explanations
- Script is written in a modular structure for easy maintenance and reuse
- Includes a
tests/folder containing automated tests for the projec
- Python 3.13
- PostgreSQL
- pip (Python package manager)
# Create and activate virtual environment (optional)
python -m venv venv
source venv/bin/activate # On Windows: venv\Scripts\activate
# Install dependencies
pip install -r requirements.txt
Run the main script on the server:
python main.py
pytest tests/
JSON files will be generated in the output/ folder. Empty tables will be logged in output/log.txt and no empty JSON will be created.
The .env file is placed inside the project folder and contains all database credentials and output path variables.
PythonProject/
├── main.py
├── db_getting.py
├── db_connection.py
├── requirements.txt
├── exporter.py
├── output/
│ ├── log.txt
│ └── *.json
├── tests/
│ ├── test_db_connection.py
│ ├── test_db_getting.py
│ ├── test_db_exporter.py
│ └── test_main.py
└── README.md
Environment variables in .env file:
DB_HOST=localhost
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=postgres
DB_NAME=evalai
OUTPUT_DIR=output
Example of JSON file generated from a table:
[ {"column1": "value1", "column2": "value2"}, {"column1": "value3", "column2": "value4"} ]
Empty tables are logged in output/log.txt:
Table 'empty_table' is empty. No JSON file created.
Only PostgreSQL is supported
SSH connection is currently optional and commented in the script
This project is licensed under the MIT License
Mohammad Hassan Gerami
psycopg2 for database connection
Python JSON library