psql_test.py

import psycopg2

# Connect to PostgreSQL
conn = psycopg2.connect(
    database="todo",
    user="todo_db_user",
    host="localhost",
    password="2005",
    port=5432,
)

# Open a cursor to perform database operations
cur = conn.cursor()

# CREATE TABLE TASK
cur.execute(
    """
CREATE TABLE IF NOT EXISTS task (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed BOOLEAN DEFAULT FALSE
);
    """
)

# CREATE INDEX
cur.execute(
    """
CREATE INDEX IF NOT EXISTS idx_task_title ON task (title);
CREATE INDEX IF NOT EXISTS idx_task_completed ON task (completed);
    """
)

# INSEERT DATA
cur.execute(
    """
INSERT INTO task (title, description, created_at, updated_at, completed)
VALUES
('Task 1', 'Description for Task 1', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, FALSE),
('Task 2', 'Description for Task 2', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, FALSE),
('Task 3', 'Description for Task 3', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, TRUE);
    """
)

# SELECT DATA
cur.execute(
    """
SELECT * FROM task;
    """
)
# Fetch all rows from the executed query
rows = cur.fetchall()
# Print the fetched rows
for row in rows:
    print(row)

# Make the changes to the database persistent
conn.commit()
# Close cursor and communication with the database
cur.close()
conn.close()