ebSkola

10. tēma / 10.5

10.5 Python ↔ PostgreSQL (psycopg2)

Stundas uzdevums: 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

70 min plāns: Teorija un paraugs (~10 min) · 1. uzdevums (~15 min) - pieslēdzies datubāzei no Python · 2. uzdevums (~25 min) - ievadi un nolasi datus ar parametriem · 3. uzdevums (~20 min) - pārbaudi, kāpēc %s ir obligāts. Papildu uzdevumu sāc tikai tad, ja pārējie trīs ir gatavi.

Pirms sāc: aktivizē virtuālo vidi un izpildi pip install psycopg2-binary. Paroli neraksti kodā - to nekad nedrīkst nosūtīt uz GitHub.

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!

Praktiskie uzdevumi

1. uzdevums -

Pieslēdzies datubāzei no Python

Beigās Python varēs lasīt tavu datubāzi.

  1. Izveido failu db.py un ieraksti import psycopg2.
  2. Izveido savienojumu ar psycopg2.connect(host=..., database="spele", user=..., password=...).
  3. Izveido kursoru ar cur = conn.cursor().
  4. Izpildi cur.execute("SELECT COUNT(*) FROM speletaji").
  5. Izdrukā rezultātu ar cur.fetchone().
  6. Pievieno .gitignore failam rindu, kas neļauj nosūtīt paroles failu.

Gatavs, kad: Python izdrukā spēlētāju skaitu, un parole nav ierakstīta failā, kas nonāk GitHub.

2. uzdevums -

Ievadi un nolasi datus ar parametriem

Beigās tava programma pati pievienos rezultātus datubāzē.

  1. Uzraksti funkciju def pievieno_speletaju(vards, punkti):.
  2. Izpildi tajā cur.execute("INSERT INTO speletaji (vards, punkti) VALUES (%s, %s)", (vards, punkti)).
  3. Pievieno conn.commit() pēc ievades.
  4. Izsauc funkciju ar jaunu spēlētāju.
  5. Pārbaudi pgAdmin, vai rinda tiešām parādījās.
  6. Izņem commit() un mēģini vēlreiz ar citu vārdu.
  7. Pieraksti, kāpēc bez commit() dati nesaglabājas.

Gatavs, kad: ar commit() rinda parādās datubāzē, bez tā - pazūd pēc programmas beigām.

3. uzdevums -

Pārbaudi, kāpēc %s ir obligāts

Beigās tu sapratīsi, kā slikti uzrakstīts vaicājums var izdzēst tabulu.

  1. Uzraksti apzināti nedrošu variantu ar f"SELECT * FROM speletaji WHERE vards = '{vards}'".
  2. Izsauc to ar parastu vārdu un pārbaudi, ka strādā.
  3. Izsauc to ar vārdu ' OR '1'='1.
  4. Pieraksti, cik rindu atgriezās un kāpēc.
  5. Pārraksti vaicājumu ar %s vietturi.
  6. Izsauc to ar to pašu ļauno vārdu vēlreiz.
  7. Pieraksti vienu secinājumu: kāpēc %s nav tas pats, kas f-string.

Gatavs, kad: ar f-string ļaunais vārds atgriež visas rindas, bet ar %s tas tiek meklēts kā parasts teksts.

Papildu uzdevums - Lieto with un RealDictCursor

Ja pamatdarbs ir gatavs, padari kodu drošāku un ērtāku.

  1. Pārraksti savienojumu ar with psycopg2.connect(...) as conn:.
  2. Pievieno with conn.cursor(cursor_factory=RealDictCursor) as cur:.
  3. Izpildi SELECT un izdrukā vienu rindu.
  4. Pieraksti, ar ko rezultāts tagad atšķiras no parasta kursora.
  5. Piekļūsti laukam pēc nosaukuma, nevis pēc numura.

Gatavs, kad: rindas dati pieejami pēc kolonnas nosaukuma, piemēram rinda["vards"].

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