Python PostgreSQL Integration using psycopg2

A beginner-friendly project demonstrating how Python can interact with a PostgreSQL database using the psycopg2 driver.

The purpose of this repository is to build a practical understanding of database programming with Python by implementing common database operations through simple, well-structured scripts. The current implementation focuses on creating an employees database table, providing a foundation for future CRUD (Create, Read, Update, Delete) operations.

---

Objectives

• Learn how Python connects to PostgreSQL.
• Execute SQL statements directly from Python.
• Understand database cursors and transactions.
• Practice proper database connection management.
• Build a strong foundation for database-driven Python applications.

---

Current Features

• Establishes a connection to a PostgreSQL database.
• Creates a cursor for executing SQL commands.
• Creates an employees table if it does not already exist.
• Commits changes to the database.
• Properly closes database resources.

---

Technologies Used

• Python 3
• PostgreSQL
• psycopg2

---

Project Structure

.
├── main.py          # Creates the employees table
└── README.md

---

Database Schema

The script creates an employees table with the following structure:

Column	Type	Description
emp_id	SERIAL	Primary key
emp_name	VARCHAR(40)	Employee name
emp_department	VARCHAR(40)	Department
emp_salary	DECIMAL(10,2)	Salary
emp_joining_date	DATE	Defaults to the current date

---

Prerequisites

Before running the project, ensure you have:

• Python 3.x
• PostgreSQL installed and running
• A PostgreSQL database (for example, test_db)
• The psycopg2 package installed

Install the required dependency:

pip install psycopg2

---

Configuration

Update the connection credentials in main.py to match your PostgreSQL server configuration before running the script.

---

Running the Project

Clone the repository:

git clone <repository-url>
cd <repository-name>

Run the script:

python main.py

---

Learning Outcomes

This project demonstrates:

• Python database connectivity
• SQL execution from Python
• PostgreSQL table creation
• Transaction handling
• Resource cleanup using cursors and connections

---

Future Enhancements

This repository is intended to grow as additional database operations are implemented, including:

• Insert records
• Retrieve records
• Update records
• Delete records
• Exception handling
• Parameterized queries
• Modular database utilities
• Connection configuration using environment variables

---

License

This project is intended for educational purposes.