owned this note
owned this note
Published
Linked with GitHub
---
tags: vaje, pb1, python, projekti, baza
hackmd: https://hackmd.io/MIMUW0YqTbiFE3N3SUU6vg
---
# Podatkovne baze 1 - vaje 13.12.2021
---
## SQL in Python
### Povezovanje, osnovne poizvedbe
```pycon
# 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
```pycon
# 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
```pycon
# 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
![](https://user-images.githubusercontent.com/44202510/145720137-c7f21823-c7dd-48fd-8b6e-0d8dce275149.png)
### Martina, Ajla: Evrovizija
![](https://user-images.githubusercontent.com/57373894/145723681-12edc955-fb98-4be7-9811-66f9cd1a00a3.png)
```sql
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
![](https://user-images.githubusercontent.com/44202983/145727727-c2252355-4801-4d3d-818a-f42ef855ee0f.png)
```sql
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
![](https://user-images.githubusercontent.com/28532399/145731108-251c36ba-59cf-4bb7-9807-c24197bc2dd1.png)
```sql
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
![](https://github.com/titoo1234/Evropska-liga-prvakov/blob/main/ER_DIAGRAM.png?raw=true)
```sql
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
![](https://i.imgur.com/CcJhRKg.png)
```sql
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
![](https://github.com/benisa21/Davcno_svetovanje/blob/main/ER%20Diagram_davcnoSvetovanje.png?raw=true)
```sql
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
![](https://i.imgur.com/vjEB9gS.png)