---
tags: vaje, pb1, python, projekti, baza
hackmd: https://hackmd.io/MIMUW0YqTbiFE3N3SUU6vg
---
# Podatkovne baze 1 - vaje 13.12.2021

---

## SQL in Python

### Povezovanje, osnovne poizvedbe

# Uvoz knjižnice in povezava na bazo
In [1]: import sqlite3

In [2]: conn = sqlite3.connect('vaje.db')

In [5]: conn
Out[5]: <sqlite3.Connection at 0x7f311edbbc60>

# Izvajanje poizvedbe neposredno na povezavi - vrne kurzor
In [6]: conn.execute("CREATE TABLE tabela (id INTEGER PRIMARY KEY AUTOINCREMENT, ime TEXT NOT NULL)")
Out[6]: <sqlite3.Cursor at 0x7f311e28c260>

### Transakcije

# Ročno ustvarjanje kurzorja
In [7]: cur = conn.cursor()

# Izvajanje poizvedbe na kurzorju
In [8]: cur.execute("""
   ...:     INSERT INTO tabela (ime, geslo)
   ...:     VALUES ('admin', 'VarnoGeslo')
   ...: """)
Out[8]: <sqlite3.Cursor at 0x7f311e28c3b0>

# Vrnjeni kurzor je enak tistemu, s katerega smo klicali poizvedbo
In [9]: cur
Out[9]: <sqlite3.Cursor at 0x7f311e28c3b0>

# Poizvedba, ki vrača rezultat
In [10]: cur.execute("SELECT * FROM tabela")
Out[10]: <sqlite3.Cursor at 0x7f311e28c3b0>

# Pridobivanje ene vrstice
In [11]: cur.fetchone()
Out[11]: (1, 'admin', 'VarnoGeslo')

# Pridobivanje naslednje vrstice - ker je ni, dobimo None
In [12]: cur.fetchone()

In [13]: cur.fetchone()

# Ponovimo poizvedbo
In [14]: cur.execute("SELECT * FROM tabela")
Out[14]: <sqlite3.Cursor at 0x7f311e28c3b0>

# Pridobivanje seznama vseh (preostalih) vrstic
In [15]: cur.fetchall()
Out[15]: [(1, 'admin', 'VarnoGeslo')]

# Ker smo vse vrstice že pridobili, v drugo dobimo prazen seznam
In [16]: cur.fetchall()
Out[16]: []

# Ista poizvedba, tretjič
In [17]: cur.execute("SELECT * FROM tabela")
Out[17]: <sqlite3.Cursor at 0x7f311e28c3b0>

# Zanka for čez vrstice
In [19]: for id, ime, geslo in cur:
    ...:     print(f'Uporabnik {ime} ima ID {id} in geslo {geslo}')

Uporabnik admin ima ID 1 in geslo VarnoGeslo

# Da bodo spremembe vidne v drugih povezavah, na povezavi izvedemo metodo commit
In [20]: conn.commit()

# Medtem smo še eno vrstico dodali v SQLiteStudiu

# Drugi način: uporaba stavka with za potrditev transakcije ob uspehu
In [21]: with conn:
    ...:     cur.execute("""
    ...:         INSERT INTO tabela (ime, geslo)
    ...:         VALUES ('matija', 'ProfesorskoGeslo')
    ...:     """)

# Če pride do napake, se celotna transakcija prekliče
In [22]: with conn: 
    ...:     cur.execute("""
    ...:         INSERT INTO tabela (ime, geslo)
    ...:         VALUES ('student', 'ŠtudentskoGeslo')
    ...:     """)
    ...:     cur.execute("INSERT INTO tabela VALUES (1, 'ime', 'geslo')")

---------------------------------------------------------------------------
IntegrityError                            Traceback (most recent call last)
<ipython-input-22-e4c1fc5bcd4e> in <module>
      1 with conn:
      2     cur.execute("""
      3         INSERT INTO tabela (ime, geslo)
      4         VALUES ('student', 'ŠtudentskoGeslo')
      5     """)
----> 6     cur.execute("INSERT INTO tabela VALUES (1, 'ime', 'geslo')")
      7 

IntegrityError: UNIQUE constraint failed: tabela.id

# Pogledamo stanje v tabeli
In [23]: cur.execute("SELECT * FROM tabela")
Out[23]: <sqlite3.Cursor at 0x7f311e28c3b0>

# Najprej pridobimo eno vrstico
In [24]: cur.fetchone()
Out[24]: (1, 'admin', 'VarnoGeslo')

# Zanka for se ustavi po enem koraku, zato obravnava samo eno vrstico
In [25]: for id, ime, geslo in cur:
    ...:     print(f'Uporabnik {ime} ima ID {id} in geslo {geslo}')
    ...:     break

Uporabnik janoš ima ID 2 in geslo MojeGeslo

# Pridobimo še ostale vrstice
In [26]: cur.fetchall()
Out[26]: [(3, 'matija', 'ProfesorskoGeslo')]

# Poizvedba, ki sproži napako
In [27]: cur.execute("INSERT INTO tabela VALUES (1, 'ime', 'geslo')")

---------------------------------------------------------------------------
IntegrityError                            Traceback (most recent call last)
<ipython-input-27-3bd32c3e8484> in <module>
----> 1 cur.execute("INSERT INTO tabela VALUES (1, 'ime', 'geslo')")
      2 

IntegrityError: UNIQUE constraint failed: tabela.id

# SQLite dovoli, da po napaki izvajamo poizvedbe znotraj iste transakcije
# To za nekatere druge baze ne velja!
In [29]: cur.execute("""
    ...:     INSERT INTO tabela (ime, geslo)
    ...:     VALUES ('student', 'ŠtudentskoGeslo')
    ...: """)
Out[29]: <sqlite3.Cursor at 0x7f311e28c3b0>

# Pogledamo stanje v tabeli
In [30]: cur.execute("SELECT * FROM tabela")
Out[30]: <sqlite3.Cursor at 0x7f311e28c3b0>

# Vidna je sprememba iz nedokončane transakcije
In [31]: cur.fetchall()
Out[31]: 
[(1, 'admin', 'VarnoGeslo'),
 (2, 'janoš', 'MojeGeslo'),
 (3, 'matija', 'ProfesorskoGeslo'),
 (4, 'student', 'ŠtudentskoGeslo')]

# Transakcije še nismo potrdili in jo lahko prekličemo
In [33]: conn.rollback()

# Pogledamo še enkrat stanje
In [34]: cur.execute("SELECT * FROM tabela")
Out[34]: <sqlite3.Cursor at 0x7f311e28c3b0>

# Spremembe iz preklicane transakcije ni več
In [35]: cur.fetchall()
Out[35]: 
[(1, 'admin', 'VarnoGeslo'),
 (2, 'janoš', 'MojeGeslo'),
 (3, 'matija', 'ProfesorskoGeslo')]

### Vstavljanje podatkov v poizvedbe in SQL injection

# Denimo, da imamo v spremenljivkah shranjene podatke,
# ki jih želimo uporabiti v poizvedbi
In [36]: ime = 'matija'

In [42]: geslo = 'ProfesorskoGeslo'

# Podatke vstavimo v poizvedbo s f-nizom - TEGA NE POČNI!!!
In [45]: cur.execute(f"""
    ...:     SELECT * FROM tabela
    ...:     WHERE ime = '{ime}' AND geslo = '{geslo}'
    ...: """)
Out[45]: <sqlite3.Cursor at 0x7f311e28c3b0>

# Drugi neustrezni načini vstavljanja podatkov:
# - metoda format
# - vstavljanje s %
# - konkatenacija nizov s +

# Uporabniško ime in geslo sta ustrezna, dobimo vrstico
In [46]: cur.fetchone()
Out[46]: (3, 'matija', 'ProfesorskoGeslo')

# Poskusimo še z neveljavnim geslom
In [47]: geslo = 'nevem'

In [48]: cur.execute(f"""
    ...:     SELECT * FROM tabela
    ...:     WHERE ime = '{ime}' AND geslo = '{geslo}'
    ...: """)
Out[48]: <sqlite3.Cursor at 0x7f311e28c3b0>

# Geslo ni ustrezno, ne dobimo vrstice
In [49]: cur.fetchone()

# Zlonamerni uporabnik vnese tako geslo
In [50]: geslo = "' OR 1 = 1; --"

In [51]: cur.execute(f"""
    ...:     SELECT * FROM tabela
    ...:     WHERE ime = '{ime}' AND geslo = '{geslo}'
    ...: """)
Out[51]: <sqlite3.Cursor at 0x7f311e28c3b0>

# Dobimo vrstico!
In [52]: cur.fetchone()
Out[52]: (1, 'admin', 'VarnoGeslo')

# Poglejmo, kako izgleda poizvedba, ki smo jo izvedli
In [53]: print(f"""
    ...:     SELECT * FROM tabela
    ...:     WHERE ime = '{ime}' AND geslo = '{geslo}'
    ...: """)

    SELECT * FROM tabela
    WHERE ime = 'matija' AND geslo = '' OR 1 = 1; --'

# Ker AND veže močneje kot OR, obvelja 1 = 1,
# del poizvedbe za podpičjem pa je zakomentiran in se ignorira.
# Prišlo je do napada SQL injection,
# pri katerem napadalec z ustreznim vnosom
# vstavi svojo logiko v stavek SQL.

# Za varno vstavljanje podatkov te navedemo v drugem argumentu metode execute,
# v sami poizvedbi pa pišemo vprašaje
In [54]: cur.execute("SELECT * FROM tabela WHERE ime = ? AND geslo = ?",
    ...:             [ime, geslo])
Out[54]: <sqlite3.Cursor at 0x7f311e28c3b0>

# Tokrat ne dobimo vrstice
In [55]: cur.fetchone()

# Poskusimo še s pravim geslom
In [56]: geslo = 'ProfesorskoGeslo'

In [57]: cur.execute("SELECT * FROM tabela WHERE ime = ? AND geslo = ?",
    ...:             [ime, geslo])
Out[57]: <sqlite3.Cursor at 0x7f311e28c3b0>

# Deluje!
In [58]: cur.fetchone()
Out[58]: (3, 'matija', 'ProfesorskoGeslo')

# Druga možnost: podatke podamo s slovarjem,
# v poizvedbi pa za dvopičjem navedemo ključ slovarja
In [59]: cur.execute("SELECT * FROM tabela WHERE ime = :ime AND geslo = :geslo", 
    ...:             {'ime': ime, 'geslo': geslo})
Out[59]: <sqlite3.Cursor at 0x7f311e28c3b0>

In [60]: cur.fetchone()                                                                        
Out[60]: (3, 'matija', 'ProfesorskoGeslo')

# Slovar si lahko seveda pripravimo tudi vnaprej
In [61]: d = {}

In [62]: d['ime'] = 'admin'

In [63]: d['geslo'] = 'VarnoGeslo'

In [64]: d
Out[64]: {'ime': 'admin', 'geslo': 'VarnoGeslo'}

In [65]: cur.execute("SELECT * FROM tabela WHERE ime = :ime AND geslo = :geslo", d)
Out[65]: <sqlite3.Cursor at 0x7f311e28c3b0>

In [66]: cur.fetchone()
Out[66]: (1, 'admin', 'VarnoGeslo')

# Pri drugih bazah namesto ? in :ključ uporabljamo %s in %s(ključ)

## Priprava baz

### Tomaž, Andreja: Knjižnica


### Martina, Ajla: Evrovizija


CREATE TABLE drzava (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ime TEXT UNIQUE NOT NULL
);

CREATE TABLE izvajalec (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ime TEXT UNIQUE NOT NULL
);

CREATE TABLE tekmovanje (
    leto INTEGER PRIMARY KEY,
    datum DATE,
    kraj TEXT,
    drzava INTEGER NOT NULL REFERENCES drzava (id)
);

CREATE TABLE pesem (
    drzava INTEGER REFERENCES drzava (id),
    leto INTEGER REFERENCES tekmovanje (leto),
    naslov TEXT NOT NULL,
    jezik TEXT,
    izvajalec INTEGER REFERENCES izvajalec (id),
    PRIMARY KEY (drzava, leto)
);

CREATE TABLE glasovanje (
    kdo INTEGER REFERENCES drzava (id),
    komu INTEGER,
    leto INTEGER,
    tocke INTEGER NOT NULL,
    FOREIGN KEY (komu, leto) REFERENCES pesem (drzava, leto),
    PRIMARY KEY (kdo, komu, leto)
);

### Diana, Hana: OI Tokio 2021


CREATE TABLE drzava (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ime TEXT UNIQUE NOT NULL
);

CREATE TABLE trener (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ime TEXT NOT NULL,
    drzava INTEGER NOT NULL REFERENCES drzava (id)
);

CREATE TABLE tekmovalec (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ime TEXT NOT NULL,
    tip TEXT NOT NULL CHECK (tip IN ('posameznik', 'skupina')),
    trener INTEGER NOT NULL REFERENCES trener (id),
    drzava INTEGER NOT NULL REFERENCES drzava (id)
);

CREATE TABLE kategorija (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ime TEXT UNIQUE NOT NULL
);

CREATE TABLE rezultat (
    tekmovalec INTEGER REFERENCES tekmovalec (id),
    kategorija INTEGER REFERENCES kategorija (id),
    rezultat INTEGER,
    PRIMARY KEY (tekmovalec, kategorija)
);

### Ana, Klavdija: Izleti po Sloveniji


CREATE TABLE lokacija (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    naziv TEXT UNIQUE NOT NULL,
    ...
);

CREATE TABLE namen (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    naziv TEXT UNIQUE NOT NULL
);

CREATE TABLE ima_namen (
    lokacija INTEGER REFERENCES lokacija (id),
    namen INTEGER REFERENCES namen (id),
    PRIMARY KEY (lokacija, name)
);

...

### Tit, Marko: Evropska liga prvakov


CREATE TABLE igralec (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ime TEXT UNIQUE NOT NULL,
    ...
);

CREATE TABLE klub (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ime TEXT UNIQUE NOT NULL,
    ...
);

CREATE TABLE ekipa (
    sezona TEXT CHECK (sezona LIKE '____/__'),
    klub INTEGER REFERENCES klub (id),
    igralec INTEGER REFERENCES igralec (id),
    PRIMARY KEY (sezona, klub, igralec), -- za sklicevanje
    UNIQUE (sezona, igralec) -- igralec je v posamezni sezoni lahko le v enem klubu
);

CREATE TABLE stadion (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ime TEXT UNIQUE NOT NULL,
    ...
);

CREATE TABLE tekma (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sezona TEXT CHECK (sezona LIKE '____/__'),
    datum DATE NOT NULL,
    stadion INTEGER REFERENCES stadion (id),
    UNIQUE (id, sezona) -- da se lahko sklicujemo na ta dva stolpca
);

CREATE TABLE igra_klub (
    tekma INTEGER,
    sezona TEXT,
    klub INTEGER REFERENCES klub (id),
    tip TEXT NOT NULL CHECK (tip IN ('domači', 'gostje')),
    FOREIGN KEY (tekma, sezona) REFERENCES tekma (id, sezona),
    PRIMARY KEY (tekma, sezona, klub), -- za sklicevanje
    UNIQUE (tekma, tip) -- na vsaki tekmi je en domači in en gostujoči klub
);

CREATE TABLE igra_igralec (
    tekma INTEGER,
    sezona TEXT NOT NULL,
    klub INTEGER,
    igralec INTEGER,
    FOREIGN KEY (tekma, sezona, klub)        -- klub je odigral tekmo
        REFERENCES igra_klub (tekma, sezona, klub),  -- v neki sezoni
    FOREIGN KEY (sezona, klub, igralec) -- igralec je član tega kluba
        REFERENCES ekipa (sezona, klub, igralec),    -- v isti sezoni
    PRIMARY KEY (tekma, klub, igralec) -- sezona je odvisna od tekme in je ne potrebujemo v glavnem ključu
);

CREATE TABLE zadetek (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tekma INTEGER NOT NULL,
    klub INTEGER NOT NULL,
    igralec INTEGER NOT NULL,
    minuta INTEGER,
    FOREIGN KEY (tekma, klub, igralec) -- igralec je igral na tej tekmi
        REFERENCES igra_igralec (tekma, klub, igralec)    -- za ta klub
);

### Jure, Petra: Ženski smučarski skoki


CREATE TABLE drzava (
    id_drzave  INTEGER PRIMARY KEY AUTOINCREMENT,
    ime_drzave TEXT    UNIQUE
                       NOT NULL,
    populacija INTEGER NOT NULL,
    bdp        INTEGER NOT NULL
);

CREATE TABLE tekmovalka (
    id_tekmovalke INTEGER PRIMARY KEY AUTOINCREMENT,
    ime_priimek   TEXT    NOT NULL,
    datum_rojstva DATE    NOT NULL,
    drzava        INTEGER NOT NULL
                          REFERENCES drzava (id_drzave) 
);
CREATE TABLE skakalnica (
    id_skakalnice  INTEGER PRIMARY KEY AUTOINCREMENT,
    ime_skakalnice TEXT    NOT NULL,
    kraj           TEXT    NOT NULL,
    tip_skakalnice TEXT    NOT NULL CHECK (tip_skakalnice IN ('srednja', 'velika')),
    velikost       INTEGER NOT NULL,
    rekord         INTEGER NOT NULL
);

CREATE TABLE tekme (
    id_tekme   INTEGER PRIMARY KEY AUTOINCREMENT,
    datum      DATE    NOT NULL,
    tip_tekme  TEXT    NOT NULL
                       CHECK (tip_tekme IN ('posamična', 'tekme') ),
    tekmovanje TEXT    NOT NULL
                       CHECK (tekmovanje IN ('svetovni pokal', 'svetovno prvenstvo', 'olimpijske igre') ),
    skakalnica INTEGER NOT NULL
                       REFERENCES skakalnica (id_skakalnice),
    sezona TEXT CHECK (sezona LIKE '____/__')
);

CREATE TABLE rezultati (
    tocke      INTEGER NOT NULL,
    mesto      INTEGER NOT NULL,
    tekmovalka INTEGER NOT NULL
                       REFERENCES tekmovalka (id_tekmovalke),
    tekma      INTEGER NOT NULL
                       REFERENCES tekme (id_tekme),
    PRIMARY KEY (
        tekmovalka,
        tekma
    )
);

### Damijan, Benisa: Davčno svetovanje


CREATE TABLE narocnik (
    davcna_stevilka INTEGER PRIMARY KEY AUTOINCREMENT,
    priimek         TEXT    NOT NULL,
    ime             TEXT    NOT NULL,
    nalov           TEXT    NOT NULL,
    kontakt_tel     INTEGER,
    kontakt_mail    TEXT
);


CREATE TABLE narocniska_pogodba (
    stevilka_pogodbe INTEGER PRIMARY KEY AUTOINCREMENT,
    datum            DATE    NOT NULL,
    vrsta_postopka   TEXT    NOT NULL,
    narocnik INTEGER NOT NULL REFERENCES narocnik (davcna_stevilka)
);

CREATE TABLE vloga (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    datum          DATE    NOT NULL,
    vrsta_postopka TEXT    NOT NULL,
    narocnik INTEGER NOT NULL REFERENCES narocnik (davcna_stevilka),
    upravni_organ INTEGER NOT NULL REFERENCES upravni_organ (id),
    odlocba INTEGER NOT NULL REFERENCES odlocba (id)
);

CREATE TABLE upravni_organ (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    naziv               TEXT    NOT NULL,
    naslov              TEXT    NOT NULL,
    telefonska_stevilka INTEGER,
    elektronska_posta   TEXT
);

CREATE TABLE odlocba (
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    stevilka_odlocbe TEXT    NOT NULL,
    datoteka         BLOB,
    datum            DATE    NOT NULL,
    koncna           TEXT
    racun INTEGER NOT NULL REFERENCES racun (id)
);

CREATE TABLE racun (
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    datum_izdaje     DATE    NOT NULL,
    datum_zapadlosti DATE    NOT NULL,
    znesek           INTEGER NOT NULL,
    status_placila INTEGER NOT NULL REFERENCES status_placila (id)
);

CREATE TABLE status_placila (
    id     INTEGER PRIMARY KEY AUTOINCREMENT,
    status TEXT    NOT NULL
);

### Luka, Anže: Analiza GitHub repozitorija