10.5 Python ↔ PostgreSQL (psycopg2)

Tavs šīs stundas izaicinājums: Savienot Python kodu ar PostgreSQL datubāzi un izpildīt drošus parametrizētus vaicājumus.

SR 2.4.11. Lieto standartizētas bibliotēkas un API SR 3.2.5. Drošības riski

Teorija: psycopg2 — Python ↔ PostgreSQL tilts

psycopg2 ir Python bibliotēka, kas savieno tavu kodu ar PostgreSQL datubāzi. Instalācija: pip install psycopg2-binary.

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    database="spele",
    user="postgres",
    password="tavs_parole"
)
cur = conn.cursor()

# DROŠA parametrizēta vaicājuma forma — %s ir TIKAI vietturis
cur.execute("SELECT * FROM speletaji WHERE punkti > %s", (100,))
for rinda in cur.fetchall():
    print(rinda)

conn.commit()  # apstiprina izmaiņas
cur.close()
conn.close()

⚠ DROŠĪBA: NEKAD nelieto string formatēšanu (f"...{x}...") SQL vaicājumos — tas ir SQL injekcijas uzbrukums!

1. uzdevums: Pirmais savienojums

Pārbaudi, ka Python var pieslēgties datubāzei.

Izpildes soļi:

Termināli: pip install psycopg2-binary
Izveido failu db_test.py: importē psycopg2, savienojas, izpilda SELECT NOW(), izvada laiku.
Izveido .env failu paroles glabāšanai (NEPUSH GitHub-ā!).
Pievieno .env savam .gitignore failam.

2. uzdevums: CRUD ar Python

Ieprogrammē funkcijas datu pievienošanai un nolasīšanai.

Izpildes soļi:

Funkcija pievienot_speletaju(vards) — INSERT ar parametriem un RETURNING id.
Funkcija iegut_visus() — SELECT un atgriež sarakstu.
Funkcija atjauninat_punktus(id, jaunie) — UPDATE ar WHERE.
Funkcija dzest_speletaju(id) — DELETE ar WHERE.
Pārbaudi visas četras funkcijas — datubāzē jāparādās izmaiņām.

3. uzdevums: Drošība un commit/rollback

Demonstrē transakcijas un drošību pret SQL injekciju.

Izpildes soļi:

Eksperiments: ievadi spēlētāja vārdu kā '); DROP TABLE speletaji; --. Vai ar parametriem tas nodara skādi? (Nē — drošs!)
Mēģini izpildīt 5 INSERT operācijas vienā transakcijā, bet pirms commit() piesauc raise Exception().
Pieliec conn.rollback() — pārbaudi datubāzi, vai izmaiņas atcēlās.
Lieto with conn: kontekstu — automātiska commit/rollback.

Papildus uzdevums: RealDictCursor

Atgriež rezultātus kā vārdnīcas, nevis kā kortežus.

Izpildes soļi:

from psycopg2.extras import RealDictCursor
cur = conn.cursor(cursor_factory=RealDictCursor)
Tagad cur.fetchall() atgriež [{'id': 1, 'vards': 'Anna', ...}, ...]
Pārveido savas funkcijas, lai lieto vārdnīcas (skaidrāks kods).

Biežākās kļūdas

SQL injekcija ar f-string: NEKAD f"WHERE id = {user_id}" — vienmēr %s + parametru tuple.
Aizmirsts commit(): Bez tā INSERT/UPDATE neparādīsies.
Nav close(): Lieto with bloku vai try/finally — savienojumi jāatbrīvo.

Koda piemērs

import psycopg2
from psycopg2.extras import RealDictCursor

with psycopg2.connect(
    host="localhost", database="spele",
    user="postgres", password="parole"
) as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cur:
        # DROŠA INSERT
        cur.execute(
            "INSERT INTO speletaji (vards) VALUES (%s) RETURNING id",
            ("Anna",)
        )
        jauns_id = cur.fetchone()["id"]
        print(f"Pievienots ar id = {jauns_id}")

        cur.execute("SELECT * FROM speletaji ORDER BY id DESC LIMIT 3")
        for r in cur.fetchall():
            print(r["vards"], r["punkti"])

Pievienots ar id = 12
Anna 0
Marta 200
Eva 145