{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Podjetje, varianta 1" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func, select, create_engine\n", "from sqlalchemy.orm import declarative_base, relationship, backref, sessionmaker\n", "from datetime import datetime" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "Base = declarative_base()\n", "\n", "\n", "class Oddelek(Base):\n", " __tablename__ = 'oddelek'\n", "\n", " id = Column(Integer, primary_key=True)\n", " naziv = Column(String)\n", "\n", " def __repr__(self):\n", " return f\"Oddelek[{self.id}, {self.naziv}, {self.seznamZaposlenih}]\"\n", "\n", "\n", "class Zaposleni(Base):\n", " __tablename__ = 'zaposleni'\n", "\n", " id = Column(Integer, primary_key=True)\n", " ime = Column(String)\n", "\n", " # uporabimo razred func za dostop do funkcij na bazi, npr. func.now()\n", " datum_zaposlitve = Column(DateTime, default=func.now())\n", " oddelek_id = Column(Integer, ForeignKey('oddelek.id'))\n", "\n", " # cascade='delete,all' bo povzročil brisanje vseh zaposlenih v oddelku\n", " oddelek = relationship(Oddelek,\n", " backref=backref('seznamZaposlenih', uselist=True, cascade='delete,all'))\n", "\n", " def __repr__(self):\n", " return f\"Zaposleni[{self.id}, {self.ime}, {self.datum_zaposlitve}, {self.oddelek_id}]\"" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:34:50,880 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", "2025-06-05 10:34:50,881 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"oddelek\")\n", "2025-06-05 10:34:50,883 INFO sqlalchemy.engine.Engine [raw sql] ()\n", "2025-06-05 10:34:50,884 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"oddelek\")\n", "2025-06-05 10:34:50,885 INFO sqlalchemy.engine.Engine [raw sql] ()\n", "2025-06-05 10:34:50,886 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"zaposleni\")\n", "2025-06-05 10:34:50,886 INFO sqlalchemy.engine.Engine [raw sql] ()\n", "2025-06-05 10:34:50,887 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"zaposleni\")\n", "2025-06-05 10:34:50,888 INFO sqlalchemy.engine.Engine [raw sql] ()\n", "2025-06-05 10:34:50,889 INFO sqlalchemy.engine.Engine \n", "CREATE TABLE oddelek (\n", "\tid INTEGER NOT NULL, \n", "\tnaziv VARCHAR, \n", "\tPRIMARY KEY (id)\n", ")\n", "\n", "\n", "2025-06-05 10:34:50,890 INFO sqlalchemy.engine.Engine [no key 0.00068s] ()\n", "2025-06-05 10:34:50,900 INFO sqlalchemy.engine.Engine \n", "CREATE TABLE zaposleni (\n", "\tid INTEGER NOT NULL, \n", "\time VARCHAR, \n", "\tdatum_zaposlitve DATETIME, \n", "\toddelek_id INTEGER, \n", "\tPRIMARY KEY (id), \n", "\tFOREIGN KEY(oddelek_id) REFERENCES oddelek (id)\n", ")\n", "\n", "\n", "2025-06-05 10:34:50,900 INFO sqlalchemy.engine.Engine [no key 0.00052s] ()\n", "2025-06-05 10:34:50,908 INFO sqlalchemy.engine.Engine COMMIT\n" ] } ], "source": [ "engine = create_engine('sqlite:///podjetje1.db', echo=True)\n", "Base.metadata.bind = engine\n", "Base.metadata.create_all(engine)" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "DBSessionMaker = sessionmaker(bind=engine)\n", "session = DBSessionMaker()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Ustvarimo oddelek." ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Oddelek[None, IT, []]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "(oddelek1 := Oddelek(naziv=\"IT\"))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Ustvarimo zaposlenega." ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Zaposleni[None, Janez, None, None]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "(zaposleni1 := Zaposleni(ime=\"Janez\", oddelek=oddelek1))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Dodajmo ustvarjena objekta v bazo." ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:35:53,736 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", "2025-06-05 10:35:53,739 INFO sqlalchemy.engine.Engine INSERT INTO oddelek (naziv) VALUES (?)\n", "2025-06-05 10:35:53,739 INFO sqlalchemy.engine.Engine [generated in 0.00066s] ('IT',)\n", "2025-06-05 10:35:53,742 INFO sqlalchemy.engine.Engine INSERT INTO zaposleni (ime, datum_zaposlitve, oddelek_id) VALUES (?, CURRENT_TIMESTAMP, ?)\n", "2025-06-05 10:35:53,742 INFO sqlalchemy.engine.Engine [generated in 0.00045s] ('Janez', 1)\n", "2025-06-05 10:35:53,743 INFO sqlalchemy.engine.Engine COMMIT\n" ] } ], "source": [ "session.add(oddelek1)\n", "session.add(zaposleni1)\n", "session.commit()" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:36:55,408 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", "2025-06-05 10:36:55,411 INFO sqlalchemy.engine.Engine SELECT oddelek.id AS oddelek_id, oddelek.naziv AS oddelek_naziv \n", "FROM oddelek \n", "WHERE oddelek.id = ?\n", "2025-06-05 10:36:55,412 INFO sqlalchemy.engine.Engine [generated in 0.00074s] (1,)\n", "2025-06-05 10:36:55,415 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 \n", "FROM zaposleni \n", "WHERE ? = zaposleni.oddelek_id\n", "2025-06-05 10:36:55,415 INFO sqlalchemy.engine.Engine [generated in 0.00048s] (1,)\n" ] }, { "data": { "text/plain": [ "(Oddelek[1, IT, [Zaposleni[1, Janez, 2025-06-05 08:35:53, 1]]],\n", " Zaposleni[1, Janez, 2025-06-05 08:35:53, 1])" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "oddelek1, zaposleni1" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Zaposleni[None, Micka, None, None]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "(zaposleni2 := Zaposleni(ime=\"Micka\"))" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:40:52,729 INFO sqlalchemy.engine.Engine INSERT INTO zaposleni (ime, datum_zaposlitve, oddelek_id) VALUES (?, CURRENT_TIMESTAMP, ?)\n", "2025-06-05 10:40:52,730 INFO sqlalchemy.engine.Engine [cached since 299s ago] ('Micka', None)\n", "2025-06-05 10:40:52,731 INFO sqlalchemy.engine.Engine COMMIT\n", "2025-06-05 10:40:52,743 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", "2025-06-05 10:40:52,744 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 \n", "FROM zaposleni \n", "WHERE zaposleni.id = ?\n", "2025-06-05 10:40:52,744 INFO sqlalchemy.engine.Engine [generated in 0.00060s] (2,)\n" ] }, { "data": { "text/plain": [ "Zaposleni[2, Micka, 2025-06-05 08:40:52, None]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "session.add(zaposleni2)\n", "session.commit()\n", "zaposleni2" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:41:50,837 INFO sqlalchemy.engine.Engine SELECT oddelek.id AS oddelek_id, oddelek.naziv AS oddelek_naziv \n", "FROM oddelek \n", "WHERE oddelek.id = ?\n", "2025-06-05 10:41:50,839 INFO sqlalchemy.engine.Engine [generated in 0.00181s] (1,)\n", "2025-06-05 10:41:50,845 INFO sqlalchemy.engine.Engine UPDATE zaposleni SET oddelek_id=? WHERE zaposleni.id = ?\n", "2025-06-05 10:41:50,846 INFO sqlalchemy.engine.Engine [generated in 0.00127s] (1, 2)\n", "2025-06-05 10:41:50,848 INFO sqlalchemy.engine.Engine COMMIT\n", "2025-06-05 10:41:50,860 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", "2025-06-05 10:41:50,860 INFO sqlalchemy.engine.Engine SELECT oddelek.id AS oddelek_id, oddelek.naziv AS oddelek_naziv \n", "FROM oddelek \n", "WHERE oddelek.id = ?\n", "2025-06-05 10:41:50,861 INFO sqlalchemy.engine.Engine [cached since 295.4s ago] (1,)\n", "2025-06-05 10:41:50,862 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 \n", "FROM zaposleni \n", "WHERE ? = zaposleni.oddelek_id\n", "2025-06-05 10:41:50,863 INFO sqlalchemy.engine.Engine [cached since 295.4s ago] (1,)\n" ] }, { "data": { "text/plain": [ "Oddelek[1, IT, [Zaposleni[1, Janez, 2025-06-05 08:35:53, 1], Zaposleni[2, Micka, 2025-06-05 08:40:52, 1]]]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "zaposleni2.oddelek = oddelek1\n", "session.commit()\n", "oddelek1" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Zaposleni[1, Janez, 2025-06-05 08:35:53, 1]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "zaposleni1" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Izpišimo trenutno vsebino baze." ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:42:38,961 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 \n", "FROM zaposleni\n", "2025-06-05 10:42:38,963 INFO sqlalchemy.engine.Engine [generated in 0.00116s] ()\n" ] }, { "data": { "text/plain": [ "[Zaposleni[1, Janez, 2025-06-05 08:35:53, 1],\n", " Zaposleni[2, Micka, 2025-06-05 08:40:52, 1]]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "session.query(Zaposleni).all()" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:43:01,329 INFO sqlalchemy.engine.Engine SELECT oddelek.id AS oddelek_id, oddelek.naziv AS oddelek_naziv \n", "FROM oddelek\n", "2025-06-05 10:43:01,331 INFO sqlalchemy.engine.Engine [generated in 0.00166s] ()\n" ] }, { "data": { "text/plain": [ "[Oddelek[1, IT, [Zaposleni[1, Janez, 2025-06-05 08:35:53, 1], Zaposleni[2, Micka, 2025-06-05 08:40:52, 1]]]]" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "session.query(Oddelek).all()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Izbrišimo `oddelek1`." ] }, { "cell_type": "code", "execution_count": 15, "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:43:26,176 INFO sqlalchemy.engine.Engine DELETE FROM zaposleni WHERE zaposleni.id = ?\n", "2025-06-05 10:43:26,178 INFO sqlalchemy.engine.Engine [generated in 0.00188s] ((1,), (2,))\n", "2025-06-05 10:43:26,179 INFO sqlalchemy.engine.Engine DELETE FROM oddelek WHERE oddelek.id = ?\n", "2025-06-05 10:43:26,180 INFO sqlalchemy.engine.Engine [generated in 0.00063s] (1,)\n", "2025-06-05 10:43:26,181 INFO sqlalchemy.engine.Engine COMMIT\n" ] } ], "source": [ "session.delete(oddelek1)\n", "session.commit()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Spet izpišimo trenutno vsebino baze." ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:44:07,218 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", "2025-06-05 10:44:07,220 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 \n", "FROM zaposleni\n", "2025-06-05 10:44:07,222 INFO sqlalchemy.engine.Engine [cached since 88.26s ago] ()\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "session.query(Zaposleni).all()" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:44:12,444 INFO sqlalchemy.engine.Engine SELECT oddelek.id AS oddelek_id, oddelek.naziv AS oddelek_naziv \n", "FROM oddelek\n", "2025-06-05 10:44:12,447 INFO sqlalchemy.engine.Engine [cached since 71.12s ago] ()\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "session.query(Oddelek).all()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Ustvarimo naslednjega zaposlenega in izpišimo datum zaposlitve." ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "None\n" ] } ], "source": [ "zaposleni2 = Zaposleni(ime=\"Francka\")\n", "session.add(zaposleni2)\n", "print(zaposleni2.datum_zaposlitve)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Privzeta vrednost se nastavi šele, ko kličemo `commit`." ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:44:34,952 INFO sqlalchemy.engine.Engine INSERT INTO zaposleni (ime, datum_zaposlitve, oddelek_id) VALUES (?, CURRENT_TIMESTAMP, ?)\n", "2025-06-05 10:44:34,955 INFO sqlalchemy.engine.Engine [cached since 521.2s ago] ('Francka', None)\n", "2025-06-05 10:44:34,958 INFO sqlalchemy.engine.Engine COMMIT\n", "2025-06-05 10:44:34,967 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", "2025-06-05 10:44:34,968 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 \n", "FROM zaposleni \n", "WHERE zaposleni.id = ?\n", "2025-06-05 10:44:34,969 INFO sqlalchemy.engine.Engine [cached since 222.2s ago] (1,)\n", "2025-06-05 08:44:34\n" ] } ], "source": [ "session.commit()\n", "print(zaposleni2.datum_zaposlitve)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Za privzeto vrednost smo uporabili `func.now()`. Izpišimo njeno vrednost pred in po interakciji z bazo." ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "func.now()" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:45:31,838 INFO sqlalchemy.engine.Engine SELECT CURRENT_TIMESTAMP AS now_1\n", "2025-06-05 10:45:31,840 INFO sqlalchemy.engine.Engine [generated in 0.00177s] ()\n" ] }, { "data": { "text/plain": [ "(datetime.datetime(2025, 6, 5, 8, 45, 31),)" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "rs = session.execute(select(func.now()))\n", "rs.fetchone()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Pobrišimo vse oddelke in uporabnike v njih." ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:46:00,347 INFO sqlalchemy.engine.Engine SELECT oddelek.id AS oddelek_id, oddelek.naziv AS oddelek_naziv \n", "FROM oddelek\n", "2025-06-05 10:46:00,348 INFO sqlalchemy.engine.Engine [cached since 179s ago] ()\n", "2025-06-05 10:46:00,349 INFO sqlalchemy.engine.Engine COMMIT\n" ] } ], "source": [ "for oddelek in session.query(Oddelek).all():\n", " session.delete(oddelek)\n", "session.commit()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Še enkrat preglejmo zaposlene." ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:46:19,763 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", "2025-06-05 10:46:19,767 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 \n", "FROM zaposleni\n", "2025-06-05 10:46:19,769 INFO sqlalchemy.engine.Engine [cached since 220.8s ago] ()\n" ] }, { "data": { "text/plain": [ "[Zaposleni[1, Francka, 2025-06-05 08:44:34, None]]" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "session.query(Zaposleni).all()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Ustvarimo dva oddelka in tri zaposlene, ki jih vstavimo v oddelke." ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:46:38,875 INFO sqlalchemy.engine.Engine INSERT INTO oddelek (naziv) VALUES (?)\n", "2025-06-05 10:46:38,876 INFO sqlalchemy.engine.Engine [cached since 645.1s ago] ('IT',)\n", "2025-06-05 10:46:38,877 INFO sqlalchemy.engine.Engine INSERT INTO oddelek (naziv) VALUES (?)\n", "2025-06-05 10:46:38,878 INFO sqlalchemy.engine.Engine [cached since 645.1s ago] ('Finance',)\n", "2025-06-05 10:46:38,879 INFO sqlalchemy.engine.Engine INSERT INTO zaposleni (ime, datum_zaposlitve, oddelek_id) VALUES (?, CURRENT_TIMESTAMP, ?)\n", "2025-06-05 10:46:38,879 INFO sqlalchemy.engine.Engine [cached since 645.1s ago] ('Janez', 1)\n", "2025-06-05 10:46:38,880 INFO sqlalchemy.engine.Engine INSERT INTO zaposleni (ime, datum_zaposlitve, oddelek_id) VALUES (?, CURRENT_TIMESTAMP, ?)\n", "2025-06-05 10:46:38,881 INFO sqlalchemy.engine.Engine [cached since 645.1s ago] ('Metka', 2)\n", "2025-06-05 10:46:38,881 INFO sqlalchemy.engine.Engine COMMIT\n" ] } ], "source": [ "IT = Oddelek(naziv=\"IT\")\n", "finance = Oddelek(naziv=\"Finance\")\n", "janez = Zaposleni(ime=\"Janez\", oddelek=IT)\n", "metka = Zaposleni(ime=\"Metka\", oddelek=finance)\n", "session.add(IT)\n", "session.add(finance)\n", "session.add(janez)\n", "session.add(metka)\n", "session.commit()" ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:46:46,790 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", "2025-06-05 10:46:46,793 INFO sqlalchemy.engine.Engine SELECT oddelek.id AS oddelek_id, oddelek.naziv AS oddelek_naziv \n", "FROM oddelek \n", "WHERE oddelek.id = ?\n", "2025-06-05 10:46:46,795 INFO sqlalchemy.engine.Engine [cached since 296s ago] (2,)\n", "2025-06-05 10:46:46,798 INFO sqlalchemy.engine.Engine INSERT INTO zaposleni (ime, datum_zaposlitve, oddelek_id) VALUES (?, CURRENT_TIMESTAMP, ?)\n", "2025-06-05 10:46:46,799 INFO sqlalchemy.engine.Engine [cached since 653.1s ago] ('Katka', 2)\n", "2025-06-05 10:46:46,800 INFO sqlalchemy.engine.Engine COMMIT\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "/tmp/ipykernel_118042/4238068839.py:1: RemovedIn20Warning: Deprecated API features detected! These feature(s) are not compatible with SQLAlchemy 2.0. To prevent incompatible upgrades prior to updating applications, ensure requirements files are pinned to \"sqlalchemy<2.0\". Set environment variable SQLALCHEMY_WARN_20=1 to show all deprecation warnings. Set environment variable SQLALCHEMY_SILENCE_UBER_WARNING=1 to silence this message. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)\n", " katka = Zaposleni(ime=\"Katka\", oddelek=finance)\n" ] } ], "source": [ "katka = Zaposleni(ime=\"Katka\", oddelek=finance)\n", "session.add(katka)\n", "session.commit()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Preštejmo zaposlene." ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:47:37,810 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", "2025-06-05 10:47:37,814 INFO sqlalchemy.engine.Engine SELECT count(*) AS count_1 \n", "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 \n", "FROM zaposleni) AS anon_1\n", "2025-06-05 10:47:37,814 INFO sqlalchemy.engine.Engine [generated in 0.00061s] ()\n" ] }, { "data": { "text/plain": [ "4" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "session.query(Zaposleni).count()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Izpišimo ime enega zaposlenega, ki se začne na črko K." ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:48:05,317 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 \n", "FROM zaposleni \n", "WHERE (zaposleni.ime LIKE ? || '%')\n", "2025-06-05 10:48:05,318 INFO sqlalchemy.engine.Engine [generated in 0.00193s] ('K',)\n" ] }, { "data": { "text/plain": [ "'Katka'" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "session.query(Zaposleni).filter(Zaposleni.ime.startswith(\"K\")).one().ime" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Izpišimo ime enega zaposlenega, ki je hkrati v oddelku Finance in se njegovo ime začne na črko K." ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:49:08,895 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 \n", "FROM zaposleni JOIN oddelek ON oddelek.id = zaposleni.oddelek_id \n", "WHERE (zaposleni.ime LIKE ? || '%') AND oddelek.naziv = ?\n", "2025-06-05 10:49:08,897 INFO sqlalchemy.engine.Engine [generated in 0.00241s] ('K', 'Finance')\n" ] }, { "data": { "text/plain": [ "[Zaposleni[4, Katka, 2025-06-05 08:46:46, 2]]" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "session.query(Zaposleni).join(Zaposleni.oddelek).filter(\n", " Zaposleni.ime.startswith('K'), Oddelek.naziv == 'Finance').all()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Izpišimo število zaposlenih, ki so bili zaposleni v preteklosti." ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2025-06-05 10:49:30,568 INFO sqlalchemy.engine.Engine SELECT count(*) AS count_1 \n", "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 \n", "FROM zaposleni \n", "WHERE zaposleni.datum_zaposlitve < CURRENT_TIMESTAMP) AS anon_1\n", "2025-06-05 10:49:30,569 INFO sqlalchemy.engine.Engine [generated in 0.00102s] ()\n" ] }, { "data": { "text/plain": [ "4" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "session.query(Zaposleni).filter(Zaposleni.datum_zaposlitve < func.now()).count()" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.12.3" } }, "nbformat": 4, "nbformat_minor": 4 }