{"id":290,"date":"2020-12-22T16:09:34","date_gmt":"2020-12-22T16:09:34","guid":{"rendered":"https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/?post_type=chapter&#038;p=290"},"modified":"2020-12-23T18:59:33","modified_gmt":"2020-12-23T18:59:33","slug":"andmebaasid-back-end","status":"publish","type":"chapter","link":"https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/chapter\/andmebaasid-back-end\/","title":{"raw":"Andmebaasid (back-end)","rendered":"Andmebaasid (back-end)"},"content":{"raw":"Andmebaaside jaoks on meil vaja paigaldada moodul Flask-SQLAlchemy. Selleks valime Thonny men\u00fc\u00fcst <em>T\u00f6\u00f6riistad \u2192 Halda lisapakke <\/em>(<em>Tools \u2192 Mangage Packages<\/em>), seej\u00e4rel kirjutame otsinguaknasse <em>flask-sqlalchemy<\/em> ja vajutame <em>Otsi pakki PyPI-st<\/em> (<em>Find package from PyPI<\/em>). N\u00fc\u00fcd saab installida Flask-SQLAlchemy mooduli, vajutades nuppu <em>Installi<\/em> (<em>Install<\/em>).\r\n\r\n<img class=\"alignnone wp-image-365\" src=\"https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-content\/uploads\/sites\/18\/2020\/12\/image9.png\" alt=\"Flask-SQLAlchemy paigaldamine\" width=\"700\" height=\"458\" \/>\r\n\r\nSeej\u00e4rel loome faili blogi.py ja impordime selle mooduli, lisades faili rea <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">from flask_sqlalchemy import SQLAlchemy<\/code>.\r\n\r\nSeega algus on meil n\u00fc\u00fcd j\u00e4rgnev:\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">from flask import Flask, render_template\r\nfrom flask_sqlalchemy import SQLAlchemy\r\napp = Flask(__name__)<\/pre>\r\nTeeme l\u00e4bi pikema n\u00e4ite, mille tulemus on rakendus, kus kasutajal on v\u00f5imalik lisada blogipostitusi, sisestades veebivormi kaudu autori nime, postituse pealkirja ja postituse sisu. Postitused kuvatakse veebilehel ja salvestatakse andmebaasi.\r\n\r\nK\u00f5igepealt kirjutame faili blogi.py k\u00f5ik vajaliku, et luua meie n\u00e4ite jaoks sobiv andmebaas. J\u00e4rgneva reaga m\u00e4\u00e4rame, kuhu andmebaas salvestatakse. Siis m\u00e4\u00e4rame muudatuste j\u00e4lgimise s\u00fcsteemi seaded ning seej\u00e4rel lisame rea andmebaasi postitused.db loomiseks. Reaalselt aga andmebaasi veel ei looda.\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:\/\/\/postitused.db' # M\u00e4\u00e4rame, kuhu andmebaas salvestatakse\r\napp.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False # m\u00e4\u00e4rame muudatuste j\u00e4lgimise s\u00fcsteemi seaded\r\ndb = SQLAlchemy(app) # loome andmebaasi<\/pre>\r\nSeej\u00e4rel m\u00e4\u00e4rame, millised veerud andmebaasis on ja millist t\u00fc\u00fcpi andmed on. Peamised andmet\u00fc\u00fcbid on <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Integer<\/code>, <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">String(pikkus)<\/code>, <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Text<\/code>, <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">DateTime<\/code>, <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Float<\/code> ja <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Boolean<\/code>.\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">class Postitus(db.Model):\r\n\u00a0 \u00a0 id = db.Column(db.Integer, primary_key=True)\r\n\u00a0 \u00a0 pealkiri = db.Column(db.String(100), nullable=False)\r\n\u00a0 \u00a0 sisu = db.Column(db.Text, nullable=False)\r\n\u00a0 \u00a0 autor = db.Column(db.String(20), nullable=False, default='Toimetaja')\r\n\u00a0 \u00a0 aeg = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)<\/pre>\r\nSiin on <em>id<\/em> unikaalne tunnus, seega m\u00e4\u00e4rame selle primaarseks v\u00f5tmeks (<code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">primary_key=True<\/code>). Meil on vaja, et nii <em>pealkiri<\/em>, <em>sisu<\/em>, <em>autor<\/em> kui ka <em>aeg<\/em> oleks alati t\u00e4idetud - seega lisasime <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">nullable=False<\/code>. Praeguses n\u00e4ites kirjutatakse autori nime asemel <em>Toimetaja, <\/em>kui nime ei sisestata. Ajaks m\u00e4\u00e4ratakse postituse sisestamise aeg.\r\n\r\nKasutasime datetime moodulit, mida on vaja importida. Seega lisame <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">from datetime import datetime<\/code>\r\n\r\nSeega algus on meil n\u00fc\u00fcd j\u00e4rgnev:\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">from flask import Flask, render_template\r\nfrom flask_sqlalchemy import SQLAlchemy\r\nfrom datetime import datetime\r\napp = Flask(__name__)<\/pre>\r\nN\u00fc\u00fcd kirjutame osa, mis v\u00e4ljastab info postituse kohta. Selle osa lisame klassi nimega <em>Postitus<\/em>.\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">def __repr__(self):\r\n\u00a0 \u00a0 return 'Postitus ' + str(self.id)<\/pre>\r\nKokku on klassi sisu j\u00e4rgnev:\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">class Postitus(db.Model):\r\n\u00a0 \u00a0 id = db.Column(db.Integer, primary_key=True)\r\n\u00a0 \u00a0 pealkiri = db.Column(db.String(100), nullable=False)\r\n\u00a0 \u00a0 sisu = db.Column(db.Text, nullable=False)\r\n\u00a0 \u00a0 autor = db.Column(db.String(20), nullable=False, default='Toimetaja')\r\n\u00a0 \u00a0 aeg = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)\r\n\u00a0 \u00a0 def __repr__(self):\r\n\u00a0 \u00a0 \u00a0 \u00a0 return 'Postitus ' + str(self.id)<\/pre>\r\nMeil on n\u00fc\u00fcd failis olemas info andmebaasi kohta, kuid andmebaas ise ei ole veel loodud. Kirjutame k\u00e4sureale <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">from blogi import db<\/code>, siis vajutame <em>Enter<\/em> ja <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">db.create_all()<\/code>. N\u00fc\u00fcd reaalselt luuakse andmebaas vastavalt nendele reeglitele, mis enne kirjutasime.\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; from blogi import db\r\n&gt;&gt;&gt; db.create_all()<\/pre>\r\nKui k\u00f5ik \u00f5nnestus, tekkis blogi.py-ga samasse kausta fail postitused.db.\r\n<h2>P\u00e4ringud, andmete lisamine, muutmine ja kustutamine<\/h2>\r\nEnne p\u00e4ringute tegemist on k\u00e4surealt vaja importida andmebaas ja klass.\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; from blogi import db\r\n&gt;&gt;&gt; from blogi import Postitus<\/pre>\r\nKui tahame v\u00e4ljastada k\u00f5ik postitused, siis valime k\u00e4sureal <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.all()<\/code>.\u00a0Kuna me ei ole andmebaasi veel \u00fchtegi postitust lisanud, kuvatakse t\u00fchi j\u00e4rjend.\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.all()\r\n[]<\/pre>\r\nVaatame k\u00f5igepealt, kuidas andmeid k\u00e4surealt andmebaasi lisada. Loome andmebaasi lisamiseks esimese postituse ja kirjutame selle k\u00e4sureale ning seej\u00e4rel vajutame <em>Enter<\/em>.\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">db.session.add(Postitus(pealkiri='Ilus p\u00e4ev', sisu='T\u00e4na on ilus p\u00e4ev.', autor='Heidi'))<\/pre>\r\nSarnaselt loome ka teise ja kolmanda postituse.\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">db.session.add(Postitus(pealkiri='P\u00e4ikseline hommik', sisu='T\u00e4na on p\u00e4ikseline hommik.', autor='Elle'))<\/pre>\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">db.session.add(Postitus(pealkiri='Sombune \u00f5htu', sisu='T\u00e4na on sombune \u00f5htu.', autor='Elle'))<\/pre>\r\nN\u00fc\u00fcd lisame need andmebaasi.\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">db.session.commit()<\/pre>\r\nKui n\u00fc\u00fcd kirjutame <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.all()<\/code>, saame tulemuseks <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">[Postitus 1, Postitus 2, Postitus 3]<\/code>. Seega need kolm postitust on andmebaasis olemas. Tulemuse kuju s\u00f5ltub sellest, et m\u00e4\u00e4rasime klassis <em>Postitus<\/em>, et tagastatakse <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">'Postitus ' + str(self.id)<\/code>.\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.all()\r\n[Postitus 1, Postitus 2, Postitus 3]<\/pre>\r\nKui kirjutame k\u00e4sureale <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.all()[0]<\/code>,kuvatakse esimese postituse info <em>id<\/em> alusel. Sama tulemuse annavad ka <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.first()<\/code> ja <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.get(1)<\/code>.\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.all()[0]\r\nPostitus 1<\/pre>\r\nKui kirjutame k\u00e4sureale <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.all()[0].pealkiri<\/code>, kuvatakse esimese postituse pealkiri, kasutades postituse otsimiseks selle indeksit. Muutujale <em>pealkiri<\/em> andsime v\u00e4\u00e4rtuse klassis <em>Postitus<\/em>.\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.all()[0].pealkiri\r\n'Ilus p\u00e4ev'<\/pre>\r\nAnaloogselt saame vaadata ka teiste postituste andmeid. N\u00e4iteks <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.all()[1].sisu<\/code> kuvab teise postituse sisu.\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.all()[1].sisu\r\n'T\u00e4na on p\u00e4ikseline hommik.'<\/pre>\r\nKui aga sisestame indeksi, millele vastavat postitust andmebaasis ei ole, tuleb veateade. Sama veateade tuleb sisestades <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.all()[5]<\/code> v\u00f5i <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.all()[5].sisu<\/code>.\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.all()[5].sisu\r\nTraceback (most recent call last):\r\n\u00a0 File \"&lt;pyshell&gt;\", line 1, in &lt;module&gt;\r\nIndexError: list index out of range<\/pre>\r\n<code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.get(5)<\/code> aga ei anna tulemuseks midagi. Kui kirjutame <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">print(Postitus.query.get(5))<\/code>, v\u00e4ljastatakse <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">None<\/code>.\r\n\r\nAndmeid saame ka filtreerida. N\u00e4iteks filtreerime k\u00f5ik postitused, mille autor on Elle.\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.filter_by(autor='Elle').all()\r\n[Postitus 2, Postitus 3]<\/pre>\r\nN\u00fc\u00fcd leiame 2. postituse (indeksiga 1), mille autor on Elle.\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.filter_by(autor='Elle')[1]\r\nPostitus\u00a03<\/pre>\r\nKui tahame andmebaasis andmeid muuta, siis saame seda teha nii, et omistame uue v\u00e4\u00e4rtuse. Meie n\u00e4ites oli 2. postituse autor Elle. Kui tahame selle autori nime muuta, saame teha seda j\u00e4rgnevalt:\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.get(2).autor = 'Heidi'\r\n&gt;&gt;&gt; db.session.commit()<\/pre>\r\nKui tahame kontrollida, kas n\u00fc\u00fcd on 2. postituse autor Heidi, saame seda teha nii:\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.get(2).autor\r\n'Heidi'<\/pre>\r\nKui tahame kustutada n\u00e4iteks 3. postituse andmebaasist, saame teha seda nii:\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; db.session.delete(Postitus.query.get(3))\r\n&gt;&gt;&gt; db.session.commit()<\/pre>\r\nKui tahame kontrollida, kas meil on n\u00fc\u00fcd 3 postituse asemel 2, saame seda teha nii:\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.all()\r\n[Postitus 1, Postitus 2]<\/pre>\r\n&nbsp;","rendered":"<p>Andmebaaside jaoks on meil vaja paigaldada moodul Flask-SQLAlchemy. Selleks valime Thonny men\u00fc\u00fcst <em>T\u00f6\u00f6riistad \u2192 Halda lisapakke <\/em>(<em>Tools \u2192 Mangage Packages<\/em>), seej\u00e4rel kirjutame otsinguaknasse <em>flask-sqlalchemy<\/em> ja vajutame <em>Otsi pakki PyPI-st<\/em> (<em>Find package from PyPI<\/em>). N\u00fc\u00fcd saab installida Flask-SQLAlchemy mooduli, vajutades nuppu <em>Installi<\/em> (<em>Install<\/em>).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-365\" src=\"https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-content\/uploads\/sites\/18\/2020\/12\/image9.png\" alt=\"Flask-SQLAlchemy paigaldamine\" width=\"700\" height=\"458\" srcset=\"https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-content\/uploads\/sites\/18\/2020\/12\/image9.png 1046w, https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-content\/uploads\/sites\/18\/2020\/12\/image9-300x196.png 300w, https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-content\/uploads\/sites\/18\/2020\/12\/image9-1024x670.png 1024w, https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-content\/uploads\/sites\/18\/2020\/12\/image9-768x502.png 768w, https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-content\/uploads\/sites\/18\/2020\/12\/image9-65x43.png 65w, https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-content\/uploads\/sites\/18\/2020\/12\/image9-225x147.png 225w, https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-content\/uploads\/sites\/18\/2020\/12\/image9-350x229.png 350w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><\/p>\n<p>Seej\u00e4rel loome faili blogi.py ja impordime selle mooduli, lisades faili rea <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">from flask_sqlalchemy import SQLAlchemy<\/code>.<\/p>\n<p>Seega algus on meil n\u00fc\u00fcd j\u00e4rgnev:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">from flask import Flask, render_template\r\nfrom flask_sqlalchemy import SQLAlchemy\r\napp = Flask(__name__)<\/pre>\n<p>Teeme l\u00e4bi pikema n\u00e4ite, mille tulemus on rakendus, kus kasutajal on v\u00f5imalik lisada blogipostitusi, sisestades veebivormi kaudu autori nime, postituse pealkirja ja postituse sisu. Postitused kuvatakse veebilehel ja salvestatakse andmebaasi.<\/p>\n<p>K\u00f5igepealt kirjutame faili blogi.py k\u00f5ik vajaliku, et luua meie n\u00e4ite jaoks sobiv andmebaas. J\u00e4rgneva reaga m\u00e4\u00e4rame, kuhu andmebaas salvestatakse. Siis m\u00e4\u00e4rame muudatuste j\u00e4lgimise s\u00fcsteemi seaded ning seej\u00e4rel lisame rea andmebaasi postitused.db loomiseks. Reaalselt aga andmebaasi veel ei looda.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:\/\/\/postitused.db' # M\u00e4\u00e4rame, kuhu andmebaas salvestatakse\r\napp.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False # m\u00e4\u00e4rame muudatuste j\u00e4lgimise s\u00fcsteemi seaded\r\ndb = SQLAlchemy(app) # loome andmebaasi<\/pre>\n<p>Seej\u00e4rel m\u00e4\u00e4rame, millised veerud andmebaasis on ja millist t\u00fc\u00fcpi andmed on. Peamised andmet\u00fc\u00fcbid on <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Integer<\/code>, <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">String(pikkus)<\/code>, <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Text<\/code>, <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">DateTime<\/code>, <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Float<\/code> ja <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Boolean<\/code>.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">class Postitus(db.Model):\r\n\u00a0 \u00a0 id = db.Column(db.Integer, primary_key=True)\r\n\u00a0 \u00a0 pealkiri = db.Column(db.String(100), nullable=False)\r\n\u00a0 \u00a0 sisu = db.Column(db.Text, nullable=False)\r\n\u00a0 \u00a0 autor = db.Column(db.String(20), nullable=False, default='Toimetaja')\r\n\u00a0 \u00a0 aeg = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)<\/pre>\n<p>Siin on <em>id<\/em> unikaalne tunnus, seega m\u00e4\u00e4rame selle primaarseks v\u00f5tmeks (<code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">primary_key=True<\/code>). Meil on vaja, et nii <em>pealkiri<\/em>, <em>sisu<\/em>, <em>autor<\/em> kui ka <em>aeg<\/em> oleks alati t\u00e4idetud &#8211; seega lisasime <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">nullable=False<\/code>. Praeguses n\u00e4ites kirjutatakse autori nime asemel <em>Toimetaja, <\/em>kui nime ei sisestata. Ajaks m\u00e4\u00e4ratakse postituse sisestamise aeg.<\/p>\n<p>Kasutasime datetime moodulit, mida on vaja importida. Seega lisame <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">from datetime import datetime<\/code><\/p>\n<p>Seega algus on meil n\u00fc\u00fcd j\u00e4rgnev:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">from flask import Flask, render_template\r\nfrom flask_sqlalchemy import SQLAlchemy\r\nfrom datetime import datetime\r\napp = Flask(__name__)<\/pre>\n<p>N\u00fc\u00fcd kirjutame osa, mis v\u00e4ljastab info postituse kohta. Selle osa lisame klassi nimega <em>Postitus<\/em>.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">def __repr__(self):\r\n\u00a0 \u00a0 return 'Postitus ' + str(self.id)<\/pre>\n<p>Kokku on klassi sisu j\u00e4rgnev:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">class Postitus(db.Model):\r\n\u00a0 \u00a0 id = db.Column(db.Integer, primary_key=True)\r\n\u00a0 \u00a0 pealkiri = db.Column(db.String(100), nullable=False)\r\n\u00a0 \u00a0 sisu = db.Column(db.Text, nullable=False)\r\n\u00a0 \u00a0 autor = db.Column(db.String(20), nullable=False, default='Toimetaja')\r\n\u00a0 \u00a0 aeg = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)\r\n\u00a0 \u00a0 def __repr__(self):\r\n\u00a0 \u00a0 \u00a0 \u00a0 return 'Postitus ' + str(self.id)<\/pre>\n<p>Meil on n\u00fc\u00fcd failis olemas info andmebaasi kohta, kuid andmebaas ise ei ole veel loodud. Kirjutame k\u00e4sureale <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">from blogi import db<\/code>, siis vajutame <em>Enter<\/em> ja <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">db.create_all()<\/code>. N\u00fc\u00fcd reaalselt luuakse andmebaas vastavalt nendele reeglitele, mis enne kirjutasime.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; from blogi import db\r\n&gt;&gt;&gt; db.create_all()<\/pre>\n<p>Kui k\u00f5ik \u00f5nnestus, tekkis blogi.py-ga samasse kausta fail postitused.db.<\/p>\n<h2>P\u00e4ringud, andmete lisamine, muutmine ja kustutamine<\/h2>\n<p>Enne p\u00e4ringute tegemist on k\u00e4surealt vaja importida andmebaas ja klass.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; from blogi import db\r\n&gt;&gt;&gt; from blogi import Postitus<\/pre>\n<p>Kui tahame v\u00e4ljastada k\u00f5ik postitused, siis valime k\u00e4sureal <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.all()<\/code>.\u00a0Kuna me ei ole andmebaasi veel \u00fchtegi postitust lisanud, kuvatakse t\u00fchi j\u00e4rjend.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.all()\r\n[]<\/pre>\n<p>Vaatame k\u00f5igepealt, kuidas andmeid k\u00e4surealt andmebaasi lisada. Loome andmebaasi lisamiseks esimese postituse ja kirjutame selle k\u00e4sureale ning seej\u00e4rel vajutame <em>Enter<\/em>.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">db.session.add(Postitus(pealkiri='Ilus p\u00e4ev', sisu='T\u00e4na on ilus p\u00e4ev.', autor='Heidi'))<\/pre>\n<p>Sarnaselt loome ka teise ja kolmanda postituse.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">db.session.add(Postitus(pealkiri='P\u00e4ikseline hommik', sisu='T\u00e4na on p\u00e4ikseline hommik.', autor='Elle'))<\/pre>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">db.session.add(Postitus(pealkiri='Sombune \u00f5htu', sisu='T\u00e4na on sombune \u00f5htu.', autor='Elle'))<\/pre>\n<p>N\u00fc\u00fcd lisame need andmebaasi.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">db.session.commit()<\/pre>\n<p>Kui n\u00fc\u00fcd kirjutame <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.all()<\/code>, saame tulemuseks <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">[Postitus 1, Postitus 2, Postitus 3]<\/code>. Seega need kolm postitust on andmebaasis olemas. Tulemuse kuju s\u00f5ltub sellest, et m\u00e4\u00e4rasime klassis <em>Postitus<\/em>, et tagastatakse <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">'Postitus ' + str(self.id)<\/code>.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.all()\r\n[Postitus 1, Postitus 2, Postitus 3]<\/pre>\n<p>Kui kirjutame k\u00e4sureale <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.all()[0]<\/code>,kuvatakse esimese postituse info <em>id<\/em> alusel. Sama tulemuse annavad ka <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.first()<\/code> ja <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.get(1)<\/code>.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.all()[0]\r\nPostitus 1<\/pre>\n<p>Kui kirjutame k\u00e4sureale <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.all()[0].pealkiri<\/code>, kuvatakse esimese postituse pealkiri, kasutades postituse otsimiseks selle indeksit. Muutujale <em>pealkiri<\/em> andsime v\u00e4\u00e4rtuse klassis <em>Postitus<\/em>.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.all()[0].pealkiri\r\n'Ilus p\u00e4ev'<\/pre>\n<p>Analoogselt saame vaadata ka teiste postituste andmeid. N\u00e4iteks <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.all()[1].sisu<\/code> kuvab teise postituse sisu.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.all()[1].sisu\r\n'T\u00e4na on p\u00e4ikseline hommik.'<\/pre>\n<p>Kui aga sisestame indeksi, millele vastavat postitust andmebaasis ei ole, tuleb veateade. Sama veateade tuleb sisestades <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.all()[5]<\/code> v\u00f5i <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.all()[5].sisu<\/code>.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.all()[5].sisu\r\nTraceback (most recent call last):\r\n\u00a0 File \"&lt;pyshell&gt;\", line 1, in &lt;module&gt;\r\nIndexError: list index out of range<\/pre>\n<p><code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Postitus.query.get(5)<\/code> aga ei anna tulemuseks midagi. Kui kirjutame <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">print(Postitus.query.get(5))<\/code>, v\u00e4ljastatakse <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">None<\/code>.<\/p>\n<p>Andmeid saame ka filtreerida. N\u00e4iteks filtreerime k\u00f5ik postitused, mille autor on Elle.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.filter_by(autor='Elle').all()\r\n[Postitus 2, Postitus 3]<\/pre>\n<p>N\u00fc\u00fcd leiame 2. postituse (indeksiga 1), mille autor on Elle.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.filter_by(autor='Elle')[1]\r\nPostitus\u00a03<\/pre>\n<p>Kui tahame andmebaasis andmeid muuta, siis saame seda teha nii, et omistame uue v\u00e4\u00e4rtuse. Meie n\u00e4ites oli 2. postituse autor Elle. Kui tahame selle autori nime muuta, saame teha seda j\u00e4rgnevalt:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.get(2).autor = 'Heidi'\r\n&gt;&gt;&gt; db.session.commit()<\/pre>\n<p>Kui tahame kontrollida, kas n\u00fc\u00fcd on 2. postituse autor Heidi, saame seda teha nii:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.get(2).autor\r\n'Heidi'<\/pre>\n<p>Kui tahame kustutada n\u00e4iteks 3. postituse andmebaasist, saame teha seda nii:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; db.session.delete(Postitus.query.get(3))\r\n&gt;&gt;&gt; db.session.commit()<\/pre>\n<p>Kui tahame kontrollida, kas meil on n\u00fc\u00fcd 3 postituse asemel 2, saame seda teha nii:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"bootstrap4\" data-enlighter-linenumbers=\"false\">&gt;&gt;&gt; Postitus.query.all()\r\n[Postitus 1, Postitus 2]<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"author":29,"menu_order":7,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":[],"pb_section_license":""},"chapter-type":[],"contributor":[],"license":[],"class_list":["post-290","chapter","type-chapter","status-publish","hentry"],"part":100,"_links":{"self":[{"href":"https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-json\/pressbooks\/v2\/chapters\/290","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-json\/wp\/v2\/users\/29"}],"version-history":[{"count":6,"href":"https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-json\/pressbooks\/v2\/chapters\/290\/revisions"}],"predecessor-version":[{"id":369,"href":"https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-json\/pressbooks\/v2\/chapters\/290\/revisions\/369"}],"part":[{"href":"https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-json\/pressbooks\/v2\/parts\/100"}],"metadata":[{"href":"https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-json\/pressbooks\/v2\/chapters\/290\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-json\/wp\/v2\/media?parent=290"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-json\/pressbooks\/v2\/chapter-type?post=290"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-json\/wp\/v2\/contributor?post=290"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/web.htk.tlu.ee\/digitaru\/tarkvara2\/wp-json\/wp\/v2\/license?post=290"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}