pip install psycopg2-binary
import psycopg2
from psycopg2 import sql
connection = psycopg2.connect(
host="localhost",
database="test",
user="postgres",
password="Heyiknow777",
port="5432"
)
print("Connection to PostgreSQL DB successful")
cursor = connection.cursor()
create_table_query = """
CREATE TABLE IF NOT EXISTS employees (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
age INTEGER,
salary DECIMAL(10, 2),
hire_date DATE,
department VARCHAR(50)
);
"""
cursor.execute(create_table_query)
connection.commit()
# # Sample data to insert
employees_data = [
('John', 'Doe', 'john.doe@email.com', 30, 50000.00, '2023-01-15', 'IT'),
('Jane', 'Smith', 'jane.smith@email.com', 28, 55000.00, '2023-02-20', 'HR'),
('Bob', 'Johnson', 'bob.johnson@email.com', 35, 60000.00, '2023-03-10', 'Finance'),
('Alice', 'Williams', 'alice.williams@email.com', 32, 52000.00, '2023-04-05', 'IT'),
('Charlie', 'Brown', 'charlie.brown@email.com', 29, 48000.00, '2023-05-12', 'Marketing')
]
# SQL query to insert data
insert_query = """
INSERT INTO employees (first_name, last_name, email, age, salary, hire_date, department)
VALUES (%s, %s, %s, %s, %s, %s, %s)
"""
cursor.executemany(insert_query, employees_data)
connection.commit()
print(f"Inserted {cursor.rowcount} records successfully")
select_query = "SELECT * FROM employees;"
cursor.execute(select_query)
# Fetch all rows
records = cursor.fetchall()
print("\nData in employees table:")
print("-" * 80)
for row in records:
print(row)
cursor.close()