Darbas su SQL Python aplinkoje

Python aplinkoje darbas su duomenų bazėmis vyksta importavus atitinkamos duomenų bazės tvarkyklę, pvz:

• Postrgres - psycopg2
• MySQL - mysql-connector-python
• SQLite - sqlite3

Pythone SQLite tvarkyklės papildomai diegti nereikia, ji ateina jau integruota kartu su Python.

Parsisiųskite duomenų bazę su kuria dirbsime ir įsidėkite šalia jūsų paleidžiamojo Python failo (pvz į PyCharm projektą) - https://github.com/robotautas/kursas/blob/master/DB/db_python/zmones.db

Įrašymas

Užklausa

query = '''
INSERT INTO draugai (f_name, l_name, email) 
VALUES ("Jonas", "Viršaitis", "[email protected]");
'''

Tam, kad po kiekvienos užklausos nereikėtų uždarinėti prisijungimo, galime naudoti context manager'į:

import sqlite3

conn = sqlite3.connect("duomenu_baze.db")
c = conn.cursor()

with conn:
    c.execute("INSERT INTO draugai VALUES ('Domantas', 'Rutkauskas', '[email protected]')")
    c.execute("INSERT INTO draugai VALUES ('Rimas', 'Radzevičius', '[email protected]')")

Įrašų paieška

.fetchone():

with conn:
    c.execute("SELECT * From darbuotojai WHERE pavarde='Rutkauskas'")
    print(c.fetchall())

# ('Domantas', 'Rutkauskas', '[email protected]')

kitas pvz.:

with conn:
    c.execute("SELECT * From draugai WHERE l_name LIKE 'R%'")
    print(c.fetchone())
# ('Domantas', 'Rutkauskas', '[email protected]')

Jeigu rezultatų daugiau, negu vienas, .fetchone() mums spausdina pirmą rezultatą. Norint gauti juos visus, turėtumem naudoti .fetchall():

with conn:
    c.execute("SELECT * From draugai WHERE l_name LIKE 'R%'")
    print(c.fetchall())

Įrašų keitimas ir trynimas

with conn:
    c.execute("UPDATE draugai SET email='[email protected]' WHERE l_name='Radzevičius'")

with conn:
    c.execute("DELETE from draugai WHERE l_name='Rutkauskas'")

Įrašų atnaujinimui ir trynimui nereikia naudoti jokių specifinių metodų.

Dinaminės užklausos

Jeigu norėtumėm užklausoje panaudoti kintamuosius iš kodo, vienas iš galimų variantų būtų:

import sqlite3

conn = sqlite3.connect("zmones.db")
c = conn.cursor()

vardas = input('Įveskite vardą: ')
with conn:
    c.execute(f"SELECT * From draugai WHERE f_name = '{vardas}'")
    res = c.fetchall()
if res:
    print(res)
else:
    print('nėra tokio vardo!')

Įveskite vardą: Domantas
[('Domantas', 'Rutkauskas', '[email protected]')]

Tarkime, vardas suveikia kaip slaptažodis ir vartotojas gali matyti savo duomenis (labai primityvus pavyzdys :) ).

pamėginkime dar kartą:

Įveskite vardą: 'OR 1=1--
[('Jonas', 'Viršaitis', '[email protected]'), ('Jurgis', 'Vagelis', '[email protected]'), ('Domantas', 'Rutkauskas', '[email protected]'), ('Rimas', 'Radzevičius', '[email protected]')]

Atsitiko taip, kad mes pratęsėme SQL užklausą ir ji tapo tokia:

SELECT * From draugai WHERE f_name = '' OR 1=1--'

Tai yra vadinama SQL Injection ataka, kuri veikia, kuomet vartotojui yra palikta galimybė pratęsti SQL užklausą. Niekada nekelkite savo kintamųjų tiesiai į užklausas! :)

Saugus būdas

sqlite3 (kaip ir kitos DB tvarkyklės) turi integruotą sistemą, saugiam kintamųjų naudojimui užklausose:

with conn:
    c.execute("SELECT * From draugai WHERE f_name =?", (vardas,))
    res = c.fetchall()

Šiuo atveju klaustukas užklausoje yra pakeičiamas antrame parametre nurodytomis vertėmis. Jis turi būti pateiktas tuple formatu. Dabar viskas veikia, kaip priklauso:

Įveskite vardą: Domantas
[('Domantas', 'Rutkauskas', '[email protected]')]

Įveskite vardą: OR 1=1--
nėra tokio vardo!

Užklausoje galime naudoti ir daugiau klaustukų:

vardas = 'Algimantas'
pavarde = 'Guobys'
email = '[email protected]'

with conn:
    c.execute("INSERT INTO draugai VALUES(?,?,?)", (vardas, pavarde, email))

excecutemany()

Galime įterpti daug įrašų į lentelę vienu kartu, svarbu jas paduoti tinkamu formatu (list of tuples):

draugai = [
    ('Jonas', 'Jonaitis', '[email protected]'),
    ('Petras', 'Miltelis', '[email protected]'),
    ('Inga', 'Guobytė', '[email protected]')
]

with conn:
    c.executemany("INSERT INTO draugai VALUES(?,?,?)", draugai)

Rowid

SQLite turi ypatumą - ID stulpelį sukuria automatiškai, todėl kuriant lentelę nebūtina tuo rūpintis. Stulpelis vadinasi rowid:

ids = (1, 3, 5)

with conn:
    c.execute("SELECT * FROM draugai WHERE rowid IN (?,?,?)", ids)
    print(c.fetchall())

Kitose duomenų bazėse reikėtų kurti atskirą ID, PostgreSQL pvz:

CREATE TABLE lentele(
    id SERIAL NOT NULL PRIMARY KEY
....

Užduotis:

Duombazę parsisiųsti iš čia - https://github.com/robotautas/kursas/blob/master/DB/uzduotis_masinos/cars.db

Parašykite programą, kuri leistų vartotojui per konsolę:

• įvesti naują eilutę (automobilį su visais parametrais)
• ieškoti įrašų pagal visus duomenų bazės lentelės stulpelius. Vartotojas gali pasirinkti kuriuos parametrus paieškoje praleisti. Metus ir kainą vartotojas turėtų nurodinėti nuo-iki.