PostgreSQL

Introduction

PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. PostgreSQL docs

Installation

Docker Compose

Docker Compose setup is recommended for a more organized and scalable environment.

services:
  postgres:
    image: "postgres"
    container_name: postgres
    environment: 
      POSTGRES_USER: "postgres" 
      POSTGRES_PASSWORD: "123456"
      POSTGRES_DB: "app"
    ports:
      - 5432:5432
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

init.sql

The init.sql file at the root of the project will be executed when the container starts, initializing the database with the content of the file.

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);
 
INSERT INTO items (name, quantity, price) VALUES ('Apple', 100, 0.50);
INSERT INTO items (name, quantity, price) VALUES ('Banana', 150, 0.30);
INSERT INTO items (name, quantity, price) VALUES ('Orange', 200, 0.80);

Python Integration

Installation

pip install psycopg2

Installation in venv:

virtualenv env && source env/bin/activate
pip install psycopg2-binary

Usage

Using the psycopg2 library to connect to a PostgreSQL database. Getting the address, user, password and database from the environment variables for safety. The database used in this example is the one created in init.sql.

Environment Variables

Create a .env file at the root of the project with the following content:

POSTGRES_HOST=localhost
POSTGRES_USER=postgres
POSTGRES_PASSWORD=123456
POSTGRES_DB=app

Query

import psycopg2
import os
 
conn = psycopg2.connect(
    host=os.getenv("POSTGRES_HOST"),
    user=os.getenv("POSTGRES_USER"),
    password=os.getenv("POSTGRES_PASSWORD"),
    database=os.getenv("POSTGRES_DB")
)
 
cur = conn.cursor()
cur.execute("SELECT * FROM items")
 
rows = cur.fetchall()
for row in rows:
    print(row)

Command

import psycopg2
import os
 
conn = psycopg2.connect(
    host=os.getenv("POSTGRES_HOST"),
    user=os.getenv("POSTGRES_USER"),
    password=os.getenv("POSTGRES_PASSWORD"),
    database=os.getenv("POSTGRES_DB")
)
 
cur = conn.cursor()
cur.execute("INSERT INTO items (name, quantity, price) VALUES ('Grapes', 50, 1.20')")
conn.commit()