Podjetje, varianta 1¶

In [1]:

from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func, select, create_engine
from sqlalchemy.orm import declarative_base, relationship, backref, sessionmaker
from datetime import datetime

In [2]:

Base = declarative_base()


class Oddelek(Base):
    __tablename__ = 'oddelek'

    id = Column(Integer, primary_key=True)
    naziv = Column(String)

    def __repr__(self):
        return f"Oddelek[{self.id}, {self.naziv}, {self.seznamZaposlenih}]"


class Zaposleni(Base):
    __tablename__ = 'zaposleni'

    id = Column(Integer, primary_key=True)
    ime = Column(String)

    # uporabimo razred func za dostop do funkcij na bazi, npr. func.now()
    datum_zaposlitve = Column(DateTime, default=func.now())
    oddelek_id = Column(Integer, ForeignKey('oddelek.id'))

    # cascade='delete,all' bo povzročil brisanje vseh zaposlenih v oddelku
    oddelek = relationship(Oddelek,
                           backref=backref('seznamZaposlenih', uselist=True, cascade='delete,all'))

    def __repr__(self):
        return f"Zaposleni[{self.id}, {self.ime}, {self.datum_zaposlitve}, {self.oddelek_id}]"

In [3]:

engine = create_engine('sqlite:///podjetje1.db', echo=True)
Base.metadata.bind = engine
Base.metadata.create_all(engine)

2024-05-13 17:33:37,765 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-05-13 17:33:37,766 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("oddelek")
2024-05-13 17:33:37,766 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-05-13 17:33:37,767 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("oddelek")
2024-05-13 17:33:37,767 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-05-13 17:33:37,768 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("zaposleni")
2024-05-13 17:33:37,768 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-05-13 17:33:37,769 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("zaposleni")
2024-05-13 17:33:37,769 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-05-13 17:33:37,771 INFO sqlalchemy.engine.Engine 
CREATE TABLE oddelek (
	id INTEGER NOT NULL, 
	naziv VARCHAR, 
	PRIMARY KEY (id)
)


2024-05-13 17:33:37,771 INFO sqlalchemy.engine.Engine [no key 0.00055s] ()
2024-05-13 17:33:37,780 INFO sqlalchemy.engine.Engine 
CREATE TABLE zaposleni (
	id INTEGER NOT NULL, 
	ime VARCHAR, 
	datum_zaposlitve DATETIME, 
	oddelek_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(oddelek_id) REFERENCES oddelek (id)
)


2024-05-13 17:33:37,781 INFO sqlalchemy.engine.Engine [no key 0.00066s] ()
2024-05-13 17:33:37,788 INFO sqlalchemy.engine.Engine COMMIT

In [4]:

DBSessionMaker = sessionmaker(bind=engine)
session = DBSessionMaker()

Ustvarimo oddelek.

In [5]:

(oddelek1 := Oddelek(naziv="IT"))

Out[5]:

Oddelek[None, IT, []]

Ustvarimo zaposlenega.

In [6]:

(zaposleni1 := Zaposleni(ime="Janez", oddelek=oddelek1))

Out[6]:

Zaposleni[None, Janez, None, None]

Dodajmo ustvarjena objekta v bazo.

In [7]:

session.add(oddelek1)
session.add(zaposleni1)
session.commit()

2024-05-13 17:35:19,188 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-05-13 17:35:19,189 INFO sqlalchemy.engine.Engine INSERT INTO oddelek (naziv) VALUES (?)
2024-05-13 17:35:19,190 INFO sqlalchemy.engine.Engine [generated in 0.00081s] ('IT',)
2024-05-13 17:35:19,193 INFO sqlalchemy.engine.Engine INSERT INTO zaposleni (ime, datum_zaposlitve, oddelek_id) VALUES (?, CURRENT_TIMESTAMP, ?)
2024-05-13 17:35:19,193 INFO sqlalchemy.engine.Engine [generated in 0.00069s] ('Janez', 1)
2024-05-13 17:35:19,194 INFO sqlalchemy.engine.Engine COMMIT

Izpišimo trenutno vsebino baze.

In [8]:

session.query(Zaposleni).all()

2024-05-13 17:35:46,443 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-05-13 17:35:46,446 INFO sqlalchemy.engine.Engine SELECT zaposleni.id AS zaposleni_id, zaposleni.ime AS zaposleni_ime, zaposleni.datum_zaposlitve AS zaposleni_datum_zaposlitve, zaposleni.oddelek_id AS zaposleni_oddelek_id 
FROM zaposleni
2024-05-13 17:35:46,447 INFO sqlalchemy.engine.Engine [generated in 0.00095s] ()

Out[8]:

[Zaposleni[1, Janez, 2024-05-13 15:35:19, 1]]

In [9]:

session.query(Oddelek).all()

2024-05-13 17:36:02,117 INFO sqlalchemy.engine.Engine SELECT oddelek.id AS oddelek_id, oddelek.naziv AS oddelek_naziv 
FROM oddelek
2024-05-13 17:36:02,118 INFO sqlalchemy.engine.Engine [generated in 0.00075s] ()
2024-05-13 17:36:02,122 INFO sqlalchemy.engine.Engine SELECT zaposleni.id AS zaposleni_id, zaposleni.ime AS zaposleni_ime, zaposleni.datum_zaposlitve AS zaposleni_datum_zaposlitve, zaposleni.oddelek_id AS zaposleni_oddelek_id 
FROM zaposleni 
WHERE ? = zaposleni.oddelek_id
2024-05-13 17:36:02,122 INFO sqlalchemy.engine.Engine [generated in 0.00059s] (1,)

Out[9]:

[Oddelek[1, IT, [Zaposleni[1, Janez, 2024-05-13 15:35:19, 1]]]]

Izbrišimo oddelek1.

In [10]:

session.delete(oddelek1)
session.commit()

2024-05-13 17:37:36,855 INFO sqlalchemy.engine.Engine DELETE FROM zaposleni WHERE zaposleni.id = ?
2024-05-13 17:37:36,855 INFO sqlalchemy.engine.Engine [generated in 0.00075s] (1,)
2024-05-13 17:37:36,857 INFO sqlalchemy.engine.Engine DELETE FROM oddelek WHERE oddelek.id = ?
2024-05-13 17:37:36,857 INFO sqlalchemy.engine.Engine [generated in 0.00057s] (1,)
2024-05-13 17:37:36,858 INFO sqlalchemy.engine.Engine COMMIT

Spet izpišimo trenutno vsebino baze.

In [11]:

session.query(Zaposleni).all()

2024-05-13 17:38:27,880 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-05-13 17:38:27,881 INFO sqlalchemy.engine.Engine SELECT zaposleni.id AS zaposleni_id, zaposleni.ime AS zaposleni_ime, zaposleni.datum_zaposlitve AS zaposleni_datum_zaposlitve, zaposleni.oddelek_id AS zaposleni_oddelek_id 
FROM zaposleni
2024-05-13 17:38:27,882 INFO sqlalchemy.engine.Engine [cached since 161.4s ago] ()

Out[11]:

[]

In [12]:

session.query(Oddelek).all()

2024-05-13 17:38:30,534 INFO sqlalchemy.engine.Engine SELECT oddelek.id AS oddelek_id, oddelek.naziv AS oddelek_naziv 
FROM oddelek
2024-05-13 17:38:30,535 INFO sqlalchemy.engine.Engine [cached since 148.4s ago] ()

Out[12]:

[]

Ustvarimo naslednjega zaposlenega in izpišimo datum zaposlitve.

In [13]:

zaposleni2 = Zaposleni(ime="Francka")
session.add(zaposleni2)
print(zaposleni2.datum_zaposlitve)

None

Privzeta vrednost se nastavi šele, ko kličemo commit.

In [14]:

session.commit()
print(zaposleni2.datum_zaposlitve)

2024-05-13 17:39:04,056 INFO sqlalchemy.engine.Engine INSERT INTO zaposleni (ime, datum_zaposlitve, oddelek_id) VALUES (?, CURRENT_TIMESTAMP, ?)
2024-05-13 17:39:04,058 INFO sqlalchemy.engine.Engine [cached since 224.9s ago] ('Francka', None)
2024-05-13 17:39:04,059 INFO sqlalchemy.engine.Engine COMMIT
2024-05-13 17:39:04,074 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-05-13 17:39:04,075 INFO sqlalchemy.engine.Engine SELECT zaposleni.id AS zaposleni_id, zaposleni.ime AS zaposleni_ime, zaposleni.datum_zaposlitve AS zaposleni_datum_zaposlitve, zaposleni.oddelek_id AS zaposleni_oddelek_id 
FROM zaposleni 
WHERE zaposleni.id = ?
2024-05-13 17:39:04,076 INFO sqlalchemy.engine.Engine [generated in 0.00051s] (1,)
2024-05-13 15:39:04

Za privzeto vrednost smo uporabili func.now(). Izpišimo njeno vrednost pred in po interakciji z bazo.

In [15]:

func.now()

Out[15]:

<sqlalchemy.sql.functions.now at 0x7f400ed42e50; now>

In [16]:

rs = session.execute(select(func.now()))
rs.fetchone()

2024-05-13 17:41:05,066 INFO sqlalchemy.engine.Engine SELECT CURRENT_TIMESTAMP AS now_1
2024-05-13 17:41:05,068 INFO sqlalchemy.engine.Engine [generated in 0.00138s] ()

Out[16]:

(datetime.datetime(2024, 5, 13, 15, 41, 5),)

Pobrišimo vse oddelke in uporabnike v njih.

In [17]:

for oddelek in session.query(Oddelek).all():
    session.delete(oddelek)
session.commit()

2024-05-13 17:41:40,109 INFO sqlalchemy.engine.Engine SELECT oddelek.id AS oddelek_id, oddelek.naziv AS oddelek_naziv 
FROM oddelek
2024-05-13 17:41:40,110 INFO sqlalchemy.engine.Engine [cached since 338s ago] ()
2024-05-13 17:41:40,111 INFO sqlalchemy.engine.Engine COMMIT

Še enkrat preglejmo zaposlene.

In [18]:

session.query(Zaposleni).all()

2024-05-13 17:41:50,949 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-05-13 17:41:50,949 INFO sqlalchemy.engine.Engine SELECT zaposleni.id AS zaposleni_id, zaposleni.ime AS zaposleni_ime, zaposleni.datum_zaposlitve AS zaposleni_datum_zaposlitve, zaposleni.oddelek_id AS zaposleni_oddelek_id 
FROM zaposleni
2024-05-13 17:41:50,950 INFO sqlalchemy.engine.Engine [cached since 364.5s ago] ()

Out[18]:

[Zaposleni[1, Francka, 2024-05-13 15:39:04, None]]

Ustvarimo dva oddelka in tri zaposlene, ki jih vstavimo v oddelke.

In [19]:

IT = Oddelek(naziv="IT")
finance = Oddelek(naziv="Finance")
janez = Zaposleni(ime="Janez", oddelek=IT)
metka = Zaposleni(ime="Metka", oddelek=finance)
session.add(IT)
session.add(finance)
session.add(janez)
session.add(metka)
session.commit()

2024-05-13 17:42:57,388 INFO sqlalchemy.engine.Engine INSERT INTO oddelek (naziv) VALUES (?)
2024-05-13 17:42:57,389 INFO sqlalchemy.engine.Engine [cached since 458.2s ago] ('IT',)
2024-05-13 17:42:57,390 INFO sqlalchemy.engine.Engine INSERT INTO oddelek (naziv) VALUES (?)
2024-05-13 17:42:57,391 INFO sqlalchemy.engine.Engine [cached since 458.2s ago] ('Finance',)
2024-05-13 17:42:57,392 INFO sqlalchemy.engine.Engine INSERT INTO zaposleni (ime, datum_zaposlitve, oddelek_id) VALUES (?, CURRENT_TIMESTAMP, ?)
2024-05-13 17:42:57,392 INFO sqlalchemy.engine.Engine [cached since 458.2s ago] ('Janez', 1)
2024-05-13 17:42:57,394 INFO sqlalchemy.engine.Engine INSERT INTO zaposleni (ime, datum_zaposlitve, oddelek_id) VALUES (?, CURRENT_TIMESTAMP, ?)
2024-05-13 17:42:57,394 INFO sqlalchemy.engine.Engine [cached since 458.2s ago] ('Metka', 2)
2024-05-13 17:42:57,395 INFO sqlalchemy.engine.Engine COMMIT

In [21]:

katka = Zaposleni(ime="Katka", oddelek=finance)
session.add(katka)
session.commit()

2024-05-13 17:43:47,675 INFO sqlalchemy.engine.Engine SELECT oddelek.id AS oddelek_id, oddelek.naziv AS oddelek_naziv 
FROM oddelek 
WHERE oddelek.id = ?
2024-05-13 17:43:47,676 INFO sqlalchemy.engine.Engine [generated in 0.00090s] (2,)
2024-05-13 17:43:47,678 INFO sqlalchemy.engine.Engine INSERT INTO zaposleni (ime, datum_zaposlitve, oddelek_id) VALUES (?, CURRENT_TIMESTAMP, ?)
2024-05-13 17:43:47,678 INFO sqlalchemy.engine.Engine [cached since 508.5s ago] ('Katka', 2)
2024-05-13 17:43:47,680 INFO sqlalchemy.engine.Engine COMMIT

Preštejmo zaposlene.

In [22]:

session.query(Zaposleni).count()

2024-05-13 17:43:50,014 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-05-13 17:43:50,015 INFO sqlalchemy.engine.Engine SELECT count(*) AS count_1 
FROM (SELECT zaposleni.id AS zaposleni_id, zaposleni.ime AS zaposleni_ime, zaposleni.datum_zaposlitve AS zaposleni_datum_zaposlitve, zaposleni.oddelek_id AS zaposleni_oddelek_id 
FROM zaposleni) AS anon_1
2024-05-13 17:43:50,016 INFO sqlalchemy.engine.Engine [cached since 9.968s ago] ()

Out[22]:

Izpišimo ime enega zaposlenega, ki se začne na črko K.

In [23]:

session.query(Zaposleni).filter(Zaposleni.ime.startswith("K")).one().ime

2024-05-13 17:44:21,984 INFO sqlalchemy.engine.Engine SELECT zaposleni.id AS zaposleni_id, zaposleni.ime AS zaposleni_ime, zaposleni.datum_zaposlitve AS zaposleni_datum_zaposlitve, zaposleni.oddelek_id AS zaposleni_oddelek_id 
FROM zaposleni 
WHERE (zaposleni.ime LIKE ? || '%')
2024-05-13 17:44:21,985 INFO sqlalchemy.engine.Engine [generated in 0.00079s] ('K',)

Out[23]:

'Katka'

Izpišimo ime enega zaposlenega, ki je hkrati v oddelku Finance in se njegovo ime začne na črko K.

In [26]:

session.query(Zaposleni).join(Zaposleni.oddelek).filter(Zaposleni.ime.startswith('K'), Oddelek.naziv == 'Finance').all()

2024-05-13 17:45:46,134 INFO sqlalchemy.engine.Engine SELECT zaposleni.id AS zaposleni_id, zaposleni.ime AS zaposleni_ime, zaposleni.datum_zaposlitve AS zaposleni_datum_zaposlitve, zaposleni.oddelek_id AS zaposleni_oddelek_id 
FROM zaposleni JOIN oddelek ON oddelek.id = zaposleni.oddelek_id 
WHERE (zaposleni.ime LIKE ? || '%') AND oddelek.naziv = ?
2024-05-13 17:45:46,135 INFO sqlalchemy.engine.Engine [cached since 18.97s ago] ('K', 'Finance')

Out[26]:

[Zaposleni[4, Katka, 2024-05-13 15:43:47, 2]]

Izpišimo število zaposlenih, ki so bili zaposleni v preteklosti.

In [27]:

session.query(Zaposleni).filter(Zaposleni.datum_zaposlitve < func.now()).count()

2024-05-13 17:46:01,270 INFO sqlalchemy.engine.Engine SELECT count(*) AS count_1 
FROM (SELECT zaposleni.id AS zaposleni_id, zaposleni.ime AS zaposleni_ime, zaposleni.datum_zaposlitve AS zaposleni_datum_zaposlitve, zaposleni.oddelek_id AS zaposleni_oddelek_id 
FROM zaposleni 
WHERE zaposleni.datum_zaposlitve < CURRENT_TIMESTAMP) AS anon_1
2024-05-13 17:46:01,271 INFO sqlalchemy.engine.Engine [generated in 0.00101s] ()

Out[27]: