« nazaj
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
Damijan, Benisa: Davčno svetovanje
Luka, Anže: Analiza GitHub repozitorija
