summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorSimeon Simeonov2022-09-21 15:38:37 +0200
committerSimeon Simeonov2022-09-21 15:38:37 +0200
commit615cd66a873853f335177ec8b307c35be17d4b36 (patch)
tree76a98cd8ccf2d1e76d062d01885a7fe24b3b0816
parentc0487f22a9d0b85d12868e91a9e17a833e8dd351 (diff)
Add notebooks/python/python_oo.ipynb and update notebooks/sqlalchemy/sqlalchemy.ipynb
Diffstat
-rw-r--r--notebooks/python/python_oo.ipynb274
-rw-r--r--notebooks/sqlalchemy/sqlalchemy.ipynb398
-rwxr-xr-xreveal.js/sqlalchemy.html684
3 files changed, 954 insertions, 402 deletions
diff --git a/notebooks/python/python_oo.ipynb b/notebooks/python/python_oo.ipynb
new file mode 100644
index 0000000..0b33a09
--- /dev/null
+++ b/notebooks/python/python_oo.ipynb
@@ -0,0 +1,274 @@
1{
2 "cells": [
3 {
4 "cell_type": "markdown",
5 "id": "2612fc97-1e84-4cc1-bd55-4cc58dbd5074",
6 "metadata": {},
7 "source": [
8 "# Introduction to object-oriented programming in Python\n",
9 "\n",
10 "Simeon Simeonov @ Statnett"
11 ]
12 },
13 {
14 "cell_type": "markdown",
15 "id": "84139fa9-733e-4871-a2f9-920f6e7c4911",
16 "metadata": {},
17 "source": [
18 "# Goals\n",
19 "\n",
20 "- present the Python programming language in a different way than [https://docs.python.org](https://docs.python.org)\n",
21 "\n",
22 "- avoid information overload\n",
23 "\n",
24 "- use examples and interaction rather than documents and slides\n",
25 "\n",
26 "\n",
27 "## Target audience\n",
28 "\n",
29 "- beginner Python programmers\n",
30 "\n",
31 "- analysts using Python as a tool"
32 ]
33 },
34 {
35 "cell_type": "markdown",
36 "id": "d6d3d0e7-c1d5-4119-940e-0f94ee7542ec",
37 "metadata": {},
38 "source": [
39 "# What is object-oriented programming?\n",
40 "\n",
41 "Object-oriented programming does **not** mean using an object-oriented programming language.\n",
42 "\n",
43 "It is rather a programming paradigm based on the concept of object, as well as on some general principles and best practices aiming at:\n",
44 "\n",
45 "- improving readability\n",
46 "\n",
47 "- improving re-usability\n",
48 "\n",
49 "- improving modularity\n",
50 "\n",
51 "- providing foundation for a more intuitive design"
52 ]
53 },
54 {
55 "cell_type": "markdown",
56 "id": "ad19da5c-3c21-4130-9225-d8f7cb2a7f40",
57 "metadata": {},
58 "source": [
59 "# General principles\n",
60 "\n",
61 "The following four concepts / principles are presented in most object-oriented programming books:\n",
62 "\n",
63 "- separate and hide \"private\" details from the outside world and / or child (inheriting) functionality (*encapsulation*) - supports *separation of concerns*\n",
64 "\n",
65 "- separate the interface from its implementation (*abstraction*)\n",
66 "\n",
67 "- inherit and extend / adapt existing functionality, through \"is-a\" relationship hierarchy (*inheritance*)\n",
68 "\n",
69 "- execute different code / functionality based on the object's place in the hierarchy (polymorphism)"
70 ]
71 },
72 {
73 "cell_type": "markdown",
74 "id": "22b2bf7e-764b-44c8-a9fd-83733319fc6e",
75 "metadata": {},
76 "source": [
77 "# Building blocks and definitions\n",
78 "\n",
79 "**Note:** \"Lacking universally accepted terminology to talk about classes, I will make occasional use of Smalltalk and C++ terms.\" - The Python tutorial [https://docs.python.org](https://docs.python.org)\n",
80 "\n",
81 "When talking about object-oriented programming, the following building blocks are involved:\n",
82 "\n",
83 "- class - a blueprint / template for creating objects\n",
84 "\n",
85 "- object - an instance of a class that may contain its own attributes as well as references to its class' attributes\n",
86 "\n",
87 "- attribute - variable, property, function defined in the class and present in its instances\n",
88 "\n",
89 "- class variable - attribute of which a single copy exists, regardless of how many instances of the class exist\n",
90 "\n",
91 "- object / instance variable - attribute for which each instantiated object of the class has a separate copy, or instance\n",
92 "\n",
93 "- method - member function - function that is an attribute"
94 ]
95 },
96 {
97 "cell_type": "markdown",
98 "id": "9e15c843-f65b-4bd3-a536-e0217b3947f8",
99 "metadata": {},
100 "source": [
101 "# The object-oriented world of Python\n",
102 "\n",
103 "Some additional concepts and definitions are introduced in Python, as well as in some other programming languages:\n",
104 "\n",
105 "- metaclass - a class whose instances are classes\n",
106 "\n",
107 "- property - attribute that provides a flexible mechanism to read, write, or compute the value of a \"private\" attribute\n",
108 "\n",
109 "- type - old C object-oriented term, now (in Python 3) considered to be the same as class\n",
110 "\n",
111 "As is true for modules, classes partake of the dynamic nature of Python: they are created at runtime, and can be modified further after creation.\n",
112 "\n",
113 "Each object in Python has an ID - an integer which is guaranteed to be unique and constant for this object during its lifetime.\n",
114 "Two objects with non-overlapping lifetimes may have the same *id()* value. In *CPython* this is the address of the object in memory.\n",
115 "\n",
116 "Python has automatic memory management using reference counting. When an object no longer has\n",
117 "any references, the garbage collector kicks inn and removes the object from memory."
118 ]
119 },
120 {
121 "cell_type": "markdown",
122 "id": "d647b1ad-948d-495d-bb30-15761b806354",
123 "metadata": {},
124 "source": [
125 "# Example1\n",
126 "\n",
127 "We will create and extend classes for representing points and vectors in 2D space"
128 ]
129 },
130 {
131 "cell_type": "code",
132 "execution_count": 7,
133 "id": "391e816e-c53b-454b-9e9f-3587234f1fea",
134 "metadata": {},
135 "outputs": [
136 {
137 "name": "stdout",
138 "output_type": "stream",
139 "text": [
140 "2:8\n",
141 "2:8\n",
142 "2\n",
143 "2\n"
144 ]
145 },
146 {
147 "ename": "AttributeError",
148 "evalue": "'Point' object has no attribute '__y'",
149 "output_type": "error",
150 "traceback": [
151 "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
152 "\u001b[0;31mAttributeError\u001b[0m Traceback (most recent call last)",
153 "Cell \u001b[0;32mIn [7], line 69\u001b[0m\n\u001b[1;32m 66\u001b[0m \u001b[38;5;66;03m# we \"should not\" be accessing private and protected attributes directly\u001b[39;00m\n\u001b[1;32m 67\u001b[0m \u001b[38;5;28mprint\u001b[39m(my_first_point\u001b[38;5;241m.\u001b[39m_x)\n\u001b[0;32m---> 69\u001b[0m \u001b[38;5;28mprint\u001b[39m(\u001b[43mmy_first_point\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m__y\u001b[49m)\n",
154 "\u001b[0;31mAttributeError\u001b[0m: 'Point' object has no attribute '__y'"
155 ]
156 }
157 ],
158 "source": [
159 "class Point:\n",
160 " \"\"\"Basic class for representing points in 2D space\"\"\"\n",
161 "\n",
162 " def __init__(self, x: int, y: int):\n",
163 " \"\"\"\n",
164 " Called after the instance has been created (by __new__()), but before\n",
165 " it is returned to the caller. The arguments are those passed to the\n",
166 " class constructor expression.\n",
167 " \n",
168 " If a base class has an __init__() method, the derived class’s\n",
169 " __init__() method, if any, must explicitly call it to ensure proper\n",
170 " initialization of the base class part of the instance;\n",
171 " for example: super().__init__([args...]).\n",
172 " \"\"\"\n",
173 " self._x = x # _ indicates \"protected\" attribute\n",
174 " self.__y = y # not a typo: __ indicates \"private\" attribute\n",
175 "\n",
176 " def __repr__(self):\n",
177 " \"\"\"\n",
178 " Called by the repr() built-in function to compute the “official”\n",
179 " string representation of an object. If at all possible, this should\n",
180 " look like a valid Python expression that could be used to recreate an\n",
181 " object with the same value (given an appropriate environment). If this\n",
182 " is not possible, a string of the form <...some useful description...>\n",
183 " should be returned. The return value must be a string object.\n",
184 " \n",
185 " If a class defines __repr__() but not __str__(), then __repr__() is\n",
186 " also used when an “informal” string representation of instances of\n",
187 " that class is required. This is typically used for debugging, so it is\n",
188 " important that the representation is information-rich and unambiguous.\n",
189 " \"\"\"\n",
190 " return f\"<Point(x={self._x!r}, y={self.__y!r})>\"\n",
191 "\n",
192 " def __str__(self):\n",
193 " \"\"\"\n",
194 " Called by str(object) and the built-in functions format() and print()\n",
195 " to compute the “informal” or nicely printable string representation of\n",
196 " an object. The return value must be a string object.\n",
197 "\n",
198 " This method differs from object.__repr__() in that there is no\n",
199 " expectation that __str__() return a valid Python expression: a more\n",
200 " convenient or concise representation can be used.\n",
201 "\n",
202 " The default implementation defined by the built-in type object calls\n",
203 " object.__repr__().\n",
204 " \"\"\"\n",
205 " return f\"{self._x}:{self.__y}\"\n",
206 "\n",
207 " @property\n",
208 " def x(self) -> int:\n",
209 " \"\"\"getter property x\"\"\"\n",
210 " return self._x\n",
211 "\n",
212 " @property\n",
213 " def y(self) -> int:\n",
214 " \"\"\"getter property y\"\"\"\n",
215 " return self.__y\n",
216 "\n",
217 "\n",
218 "my_first_point = Point(2, 8)\n",
219 "\n",
220 "print(f\"{my_first_point}\")\n",
221 "print(f\"{my_first_point}\")\n",
222 "print(my_first_point.x)\n",
223 "\n",
224 "# we \"should not\" be accessing private and protected attributes directly\n",
225 "print(my_first_point._x)\n",
226 "\n",
227 "print(my_first_point.__y) # will not work\n",
228 "# Out: AttributeError: 'Point' object has no attribute '__y'\n",
229 "\n",
230 "# will work, but should not be used by a sane programmer:\n",
231 "# print(my_first_point._Point__y)"
232 ]
233 },
234 {
235 "cell_type": "code",
236 "execution_count": null,
237 "id": "e82feefb-025d-4cbb-a8ac-ddc3cc01269c",
238 "metadata": {},
239 "outputs": [],
240 "source": [
241 "class Vector:\n",
242 " \"\"\"Basic class for representing vectors in 2D space\"\"\"\n",
243 "\n",
244 " def __init__(self, start: Point, end: Point):\n",
245 " self._start = start\n",
246 " self._end = end\n",
247 "\n",
248 " def __str__(self):\n",
249 " return f\"{self._start} -> {self._end}\"\n"
250 ]
251 }
252 ],
253 "metadata": {
254 "kernelspec": {
255 "display_name": "Python 3 (ipykernel)",
256 "language": "python",
257 "name": "python3"
258 },
259 "language_info": {
260 "codemirror_mode": {
261 "name": "ipython",
262 "version": 3
263 },
264 "file_extension": ".py",
265 "mimetype": "text/x-python",
266 "name": "python",
267 "nbconvert_exporter": "python",
268 "pygments_lexer": "ipython3",
269 "version": "3.8.10"
270 }
271 },
272 "nbformat": 4,
273 "nbformat_minor": 5
274}
diff --git a/notebooks/sqlalchemy/sqlalchemy.ipynb b/notebooks/sqlalchemy/sqlalchemy.ipynb
index ce0070c..51b00ab 100644
--- a/notebooks/sqlalchemy/sqlalchemy.ipynb
+++ b/notebooks/sqlalchemy/sqlalchemy.ipynb
@@ -90,7 +90,7 @@
90 }, 90 },
91 { 91 {
92 "cell_type": "code", 92 "cell_type": "code",
93 "execution_count": 12, 93 "execution_count": 11,
94 "id": "d1ea9f51-e3de-45a8-8302-b4a89b5d1689", 94 "id": "d1ea9f51-e3de-45a8-8302-b4a89b5d1689",
95 "metadata": { 95 "metadata": {
96 "slideshow": { 96 "slideshow": {
@@ -114,7 +114,7 @@
114 }, 114 },
115 { 115 {
116 "cell_type": "code", 116 "cell_type": "code",
117 "execution_count": 13, 117 "execution_count": 12,
118 "id": "3b4a777d-7d38-4149-8ab2-c876e2533766", 118 "id": "3b4a777d-7d38-4149-8ab2-c876e2533766",
119 "metadata": { 119 "metadata": {
120 "slideshow": { 120 "slideshow": {
@@ -126,20 +126,20 @@
126 "name": "stdout", 126 "name": "stdout",
127 "output_type": "stream", 127 "output_type": "stream",
128 "text": [ 128 "text": [
129 "2022-09-20 15:40:36,125 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", 129 "2022-09-21 09:05:41,263 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
130 "2022-09-20 15:40:36,126 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_types\")\n", 130 "2022-09-21 09:05:41,264 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_types\")\n",
131 "2022-09-20 15:40:36,126 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 131 "2022-09-21 09:05:41,265 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
132 "2022-09-20 15:40:36,127 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"production_types\")\n", 132 "2022-09-21 09:05:41,267 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"production_types\")\n",
133 "2022-09-20 15:40:36,128 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 133 "2022-09-21 09:05:41,267 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
134 "2022-09-20 15:40:36,129 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"bidding_areas\")\n", 134 "2022-09-21 09:05:41,269 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"bidding_areas\")\n",
135 "2022-09-20 15:40:36,129 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 135 "2022-09-21 09:05:41,269 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
136 "2022-09-20 15:40:36,130 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"bidding_areas\")\n", 136 "2022-09-21 09:05:41,270 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"bidding_areas\")\n",
137 "2022-09-20 15:40:36,131 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 137 "2022-09-21 09:05:41,271 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
138 "2022-09-20 15:40:36,132 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n", 138 "2022-09-21 09:05:41,272 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n",
139 "2022-09-20 15:40:36,132 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 139 "2022-09-21 09:05:41,272 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
140 "2022-09-20 15:40:36,133 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"production_plans\")\n", 140 "2022-09-21 09:05:41,274 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"production_plans\")\n",
141 "2022-09-20 15:40:36,134 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 141 "2022-09-21 09:05:41,274 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
142 "2022-09-20 15:40:36,136 INFO sqlalchemy.engine.Engine \n", 142 "2022-09-21 09:05:41,275 INFO sqlalchemy.engine.Engine \n",
143 "CREATE TABLE production_types (\n", 143 "CREATE TABLE production_types (\n",
144 "\tproduction_type_id INTEGER NOT NULL, \n", 144 "\tproduction_type_id INTEGER NOT NULL, \n",
145 "\tcode VARCHAR(3) NOT NULL, \n", 145 "\tcode VARCHAR(3) NOT NULL, \n",
@@ -149,8 +149,8 @@
149 ")\n", 149 ")\n",
150 "\n", 150 "\n",
151 "\n", 151 "\n",
152 "2022-09-20 15:40:36,137 INFO sqlalchemy.engine.Engine [no key 0.00055s] ()\n", 152 "2022-09-21 09:05:41,276 INFO sqlalchemy.engine.Engine [no key 0.00063s] ()\n",
153 "2022-09-20 15:40:36,138 INFO sqlalchemy.engine.Engine \n", 153 "2022-09-21 09:05:41,278 INFO sqlalchemy.engine.Engine \n",
154 "CREATE TABLE bidding_areas (\n", 154 "CREATE TABLE bidding_areas (\n",
155 "\tbidding_area_id INTEGER NOT NULL, \n", 155 "\tbidding_area_id INTEGER NOT NULL, \n",
156 "\tcode VARCHAR(3) NOT NULL, \n", 156 "\tcode VARCHAR(3) NOT NULL, \n",
@@ -160,8 +160,8 @@
160 ")\n", 160 ")\n",
161 "\n", 161 "\n",
162 "\n", 162 "\n",
163 "2022-09-20 15:40:36,138 INFO sqlalchemy.engine.Engine [no key 0.00049s] ()\n", 163 "2022-09-21 09:05:41,278 INFO sqlalchemy.engine.Engine [no key 0.00059s] ()\n",
164 "2022-09-20 15:40:36,140 INFO sqlalchemy.engine.Engine \n", 164 "2022-09-21 09:05:41,280 INFO sqlalchemy.engine.Engine \n",
165 "CREATE TABLE production_plans (\n", 165 "CREATE TABLE production_plans (\n",
166 "\trecord_created_time DATETIME NOT NULL, \n", 166 "\trecord_created_time DATETIME NOT NULL, \n",
167 "\tstart_time DATETIME NOT NULL, \n", 167 "\tstart_time DATETIME NOT NULL, \n",
@@ -174,8 +174,8 @@
174 ")\n", 174 ")\n",
175 "\n", 175 "\n",
176 "\n", 176 "\n",
177 "2022-09-20 15:40:36,141 INFO sqlalchemy.engine.Engine [no key 0.00068s] ()\n", 177 "2022-09-21 09:05:41,280 INFO sqlalchemy.engine.Engine [no key 0.00050s] ()\n",
178 "2022-09-20 15:40:36,142 INFO sqlalchemy.engine.Engine COMMIT\n" 178 "2022-09-21 09:05:41,281 INFO sqlalchemy.engine.Engine COMMIT\n"
179 ] 179 ]
180 } 180 }
181 ], 181 ],
@@ -293,7 +293,7 @@
293 }, 293 },
294 { 294 {
295 "cell_type": "code", 295 "cell_type": "code",
296 "execution_count": 14, 296 "execution_count": 13,
297 "id": "70b0c5fa-35ae-44b5-b57b-2fa21b4f7da5", 297 "id": "70b0c5fa-35ae-44b5-b57b-2fa21b4f7da5",
298 "metadata": {}, 298 "metadata": {},
299 "outputs": [ 299 "outputs": [
@@ -301,58 +301,58 @@
301 "name": "stdout", 301 "name": "stdout",
302 "output_type": "stream", 302 "output_type": "stream",
303 "text": [ 303 "text": [
304 "2022-09-20 15:40:36,151 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_types\")\n", 304 "2022-09-21 09:05:41,292 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_types\")\n",
305 "2022-09-20 15:40:36,152 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 305 "2022-09-21 09:05:41,293 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
306 "2022-09-20 15:40:36,153 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", 306 "2022-09-21 09:05:41,294 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n",
307 "2022-09-20 15:40:36,153 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", 307 "2022-09-21 09:05:41,295 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n",
308 "2022-09-20 15:40:36,155 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_types\")\n", 308 "2022-09-21 09:05:41,296 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_types\")\n",
309 "2022-09-20 15:40:36,156 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 309 "2022-09-21 09:05:41,297 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
310 "2022-09-20 15:40:36,157 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"production_types\")\n", 310 "2022-09-21 09:05:41,298 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"production_types\")\n",
311 "2022-09-20 15:40:36,158 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 311 "2022-09-21 09:05:41,298 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
312 "2022-09-20 15:40:36,160 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", 312 "2022-09-21 09:05:41,299 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n",
313 "2022-09-20 15:40:36,160 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", 313 "2022-09-21 09:05:41,300 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n",
314 "2022-09-20 15:40:36,161 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n", 314 "2022-09-21 09:05:41,301 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n",
315 "2022-09-20 15:40:36,162 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 315 "2022-09-21 09:05:41,302 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
316 "2022-09-20 15:40:36,163 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n", 316 "2022-09-21 09:05:41,303 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n",
317 "2022-09-20 15:40:36,164 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 317 "2022-09-21 09:05:41,303 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
318 "2022-09-20 15:40:36,165 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_types_1\")\n", 318 "2022-09-21 09:05:41,304 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_types_1\")\n",
319 "2022-09-20 15:40:36,166 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 319 "2022-09-21 09:05:41,305 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
320 "2022-09-20 15:40:36,168 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", 320 "2022-09-21 09:05:41,306 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n",
321 "2022-09-20 15:40:36,169 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", 321 "2022-09-21 09:05:41,307 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n",
322 "2022-09-20 15:40:36,171 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"bidding_areas\")\n", 322 "2022-09-21 09:05:41,309 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"bidding_areas\")\n",
323 "2022-09-20 15:40:36,172 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 323 "2022-09-21 09:05:41,310 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
324 "2022-09-20 15:40:36,173 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", 324 "2022-09-21 09:05:41,311 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n",
325 "2022-09-20 15:40:36,174 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", 325 "2022-09-21 09:05:41,311 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n",
326 "2022-09-20 15:40:36,175 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"bidding_areas\")\n", 326 "2022-09-21 09:05:41,313 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"bidding_areas\")\n",
327 "2022-09-20 15:40:36,176 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 327 "2022-09-21 09:05:41,313 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
328 "2022-09-20 15:40:36,176 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"bidding_areas\")\n", 328 "2022-09-21 09:05:41,314 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"bidding_areas\")\n",
329 "2022-09-20 15:40:36,177 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 329 "2022-09-21 09:05:41,315 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
330 "2022-09-20 15:40:36,179 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", 330 "2022-09-21 09:05:41,316 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n",
331 "2022-09-20 15:40:36,179 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", 331 "2022-09-21 09:05:41,316 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n",
332 "2022-09-20 15:40:36,181 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n", 332 "2022-09-21 09:05:41,318 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n",
333 "2022-09-20 15:40:36,181 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 333 "2022-09-21 09:05:41,318 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
334 "2022-09-20 15:40:36,182 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n", 334 "2022-09-21 09:05:41,319 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n",
335 "2022-09-20 15:40:36,183 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 335 "2022-09-21 09:05:41,320 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
336 "2022-09-20 15:40:36,184 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_bidding_areas_1\")\n", 336 "2022-09-21 09:05:41,321 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_bidding_areas_1\")\n",
337 "2022-09-20 15:40:36,185 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 337 "2022-09-21 09:05:41,322 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
338 "2022-09-20 15:40:36,186 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", 338 "2022-09-21 09:05:41,323 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n",
339 "2022-09-20 15:40:36,186 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", 339 "2022-09-21 09:05:41,323 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n",
340 "2022-09-20 15:40:36,189 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_plans\")\n", 340 "2022-09-21 09:05:41,326 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_plans\")\n",
341 "2022-09-20 15:40:36,189 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 341 "2022-09-21 09:05:41,327 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
342 "2022-09-20 15:40:36,191 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", 342 "2022-09-21 09:05:41,328 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n",
343 "2022-09-20 15:40:36,191 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n", 343 "2022-09-21 09:05:41,329 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n",
344 "2022-09-20 15:40:36,193 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_plans\")\n", 344 "2022-09-21 09:05:41,330 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_plans\")\n",
345 "2022-09-20 15:40:36,194 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 345 "2022-09-21 09:05:41,331 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
346 "2022-09-20 15:40:36,195 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", 346 "2022-09-21 09:05:41,331 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n",
347 "2022-09-20 15:40:36,196 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n", 347 "2022-09-21 09:05:41,332 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n",
348 "2022-09-20 15:40:36,197 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n", 348 "2022-09-21 09:05:41,334 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n",
349 "2022-09-20 15:40:36,197 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 349 "2022-09-21 09:05:41,334 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
350 "2022-09-20 15:40:36,199 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n", 350 "2022-09-21 09:05:41,336 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n",
351 "2022-09-20 15:40:36,199 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 351 "2022-09-21 09:05:41,336 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
352 "2022-09-20 15:40:36,200 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_plans_1\")\n", 352 "2022-09-21 09:05:41,337 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_plans_1\")\n",
353 "2022-09-20 15:40:36,201 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 353 "2022-09-21 09:05:41,338 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
354 "2022-09-20 15:40:36,202 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", 354 "2022-09-21 09:05:41,339 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n",
355 "2022-09-20 15:40:36,202 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n" 355 "2022-09-21 09:05:41,340 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n"
356 ] 356 ]
357 } 357 }
358 ], 358 ],
@@ -442,7 +442,7 @@
442 }, 442 },
443 { 443 {
444 "cell_type": "code", 444 "cell_type": "code",
445 "execution_count": 15, 445 "execution_count": 14,
446 "id": "1f18d9c1-bd39-4195-b868-114c3296eac2", 446 "id": "1f18d9c1-bd39-4195-b868-114c3296eac2",
447 "metadata": { 447 "metadata": {
448 "slideshow": { 448 "slideshow": {
@@ -454,42 +454,42 @@
454 "name": "stdout", 454 "name": "stdout",
455 "output_type": "stream", 455 "output_type": "stream",
456 "text": [ 456 "text": [
457 "2022-09-20 15:40:36,217 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", 457 "2022-09-21 09:05:41,358 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
458 "2022-09-20 15:40:36,219 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", 458 "2022-09-21 09:05:41,360 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n",
459 "2022-09-20 15:40:36,219 INFO sqlalchemy.engine.Engine [generated in 0.00067s] ('NO1', 'Elspot NO1')\n", 459 "2022-09-21 09:05:41,360 INFO sqlalchemy.engine.Engine [generated in 0.00079s] ('NO1', 'Elspot NO1')\n",
460 "2022-09-20 15:40:36,221 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", 460 "2022-09-21 09:05:41,361 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n",
461 "2022-09-20 15:40:36,221 INFO sqlalchemy.engine.Engine [cached since 0.002563s ago] ('NO2', 'Elspot NO2')\n", 461 "2022-09-21 09:05:41,362 INFO sqlalchemy.engine.Engine [cached since 0.002301s ago] ('NO2', 'Elspot NO2')\n",
462 "2022-09-20 15:40:36,222 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", 462 "2022-09-21 09:05:41,364 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n",
463 "2022-09-20 15:40:36,223 INFO sqlalchemy.engine.Engine [cached since 0.004114s ago] ('NO3', 'Elspot NO3')\n", 463 "2022-09-21 09:05:41,364 INFO sqlalchemy.engine.Engine [cached since 0.004883s ago] ('NO3', 'Elspot NO3')\n",
464 "2022-09-20 15:40:36,224 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", 464 "2022-09-21 09:05:41,366 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n",
465 "2022-09-20 15:40:36,224 INFO sqlalchemy.engine.Engine [cached since 0.0056s ago] ('NO4', 'Elspot NO4')\n", 465 "2022-09-21 09:05:41,366 INFO sqlalchemy.engine.Engine [cached since 0.006756s ago] ('NO4', 'Elspot NO4')\n",
466 "2022-09-20 15:40:36,226 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", 466 "2022-09-21 09:05:41,367 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n",
467 "2022-09-20 15:40:36,226 INFO sqlalchemy.engine.Engine [cached since 0.007587s ago] ('NO5', 'Elspot NO5')\n", 467 "2022-09-21 09:05:41,368 INFO sqlalchemy.engine.Engine [cached since 0.008241s ago] ('NO5', 'Elspot NO5')\n",
468 "2022-09-20 15:40:36,228 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", 468 "2022-09-21 09:05:41,370 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n",
469 "2022-09-20 15:40:36,228 INFO sqlalchemy.engine.Engine [generated in 0.00059s] ('B19', 'Wind Onshore')\n", 469 "2022-09-21 09:05:41,370 INFO sqlalchemy.engine.Engine [generated in 0.00058s] ('B19', 'Wind Onshore')\n",
470 "2022-09-20 15:40:36,229 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", 470 "2022-09-21 09:05:41,371 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n",
471 "2022-09-20 15:40:36,230 INFO sqlalchemy.engine.Engine [cached since 0.002172s ago] ('B10', 'Hydro-electric pure pumped storage head installation')\n", 471 "2022-09-21 09:05:41,372 INFO sqlalchemy.engine.Engine [cached since 0.00176s ago] ('B10', 'Hydro-electric pure pumped storage head installation')\n",
472 "2022-09-20 15:40:36,231 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", 472 "2022-09-21 09:05:41,373 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n",
473 "2022-09-20 15:40:36,232 INFO sqlalchemy.engine.Engine [cached since 0.003747s ago] ('B11', 'Hydro Run-of-river head installation')\n", 473 "2022-09-21 09:05:41,374 INFO sqlalchemy.engine.Engine [cached since 0.003672s ago] ('B11', 'Hydro Run-of-river head installation')\n",
474 "2022-09-20 15:40:36,233 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", 474 "2022-09-21 09:05:41,375 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n",
475 "2022-09-20 15:40:36,233 INFO sqlalchemy.engine.Engine [cached since 0.00528s ago] ('B12', 'Hydro-electric storage head installation')\n", 475 "2022-09-21 09:05:41,375 INFO sqlalchemy.engine.Engine [cached since 0.00531s ago] ('B12', 'Hydro-electric storage head installation')\n",
476 "2022-09-20 15:40:36,234 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", 476 "2022-09-21 09:05:41,376 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n",
477 "2022-09-20 15:40:36,234 INFO sqlalchemy.engine.Engine [cached since 0.006383s ago] ('A04', 'Generation')\n", 477 "2022-09-21 09:05:41,377 INFO sqlalchemy.engine.Engine [cached since 0.00679s ago] ('A04', 'Generation')\n",
478 "2022-09-20 15:40:36,236 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", 478 "2022-09-21 09:05:41,377 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n",
479 "2022-09-20 15:40:36,236 INFO sqlalchemy.engine.Engine [cached since 0.00839s ago] ('B37', 'Thermal unspecified')\n", 479 "2022-09-21 09:05:41,378 INFO sqlalchemy.engine.Engine [cached since 0.008175s ago] ('B37', 'Thermal unspecified')\n",
480 "2022-09-20 15:40:36,237 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", 480 "2022-09-21 09:05:41,379 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n",
481 "2022-09-20 15:40:36,238 INFO sqlalchemy.engine.Engine [cached since 0.009993s ago] ('B30', 'Wind unspecified')\n", 481 "2022-09-21 09:05:41,380 INFO sqlalchemy.engine.Engine [cached since 0.01012s ago] ('B30', 'Wind unspecified')\n",
482 "2022-09-20 15:40:36,240 INFO sqlalchemy.engine.Engine INSERT INTO production_plans (record_created_time, start_time, bidding_area_id, production_type_id, value) VALUES (?, ?, ?, ?, ?)\n", 482 "2022-09-21 09:05:41,382 INFO sqlalchemy.engine.Engine INSERT INTO production_plans (record_created_time, start_time, bidding_area_id, production_type_id, value) VALUES (?, ?, ?, ?, ?)\n",
483 "2022-09-20 15:40:36,241 INFO sqlalchemy.engine.Engine [generated in 0.00109s] (('2022-09-20 15:40:36.216893', '2022-11-02 01:00:00.000000', 1, 6, 80.5), ('2022-09-20 15:40:36.217038', '2022-11-02 02:00:00.000000', 1, 6, 90.5))\n", 483 "2022-09-21 09:05:41,383 INFO sqlalchemy.engine.Engine [generated in 0.00075s] (('2022-09-21 09:05:41.357984', '2022-11-02 01:00:00.000000', 1, 6, 80.5), ('2022-09-21 09:05:41.358126', '2022-11-02 02:00:00.000000', 1, 6, 90.5))\n",
484 "2022-09-20 15:40:36,242 INFO sqlalchemy.engine.Engine COMMIT\n", 484 "2022-09-21 09:05:41,384 INFO sqlalchemy.engine.Engine COMMIT\n",
485 "2022-09-20 15:40:36,243 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", 485 "2022-09-21 09:05:41,385 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
486 "2022-09-20 15:40:36,245 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id \n", 486 "2022-09-21 09:05:41,387 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id \n",
487 "FROM production_plans \n", 487 "FROM production_plans \n",
488 "WHERE production_plans.record_created_time = ? AND production_plans.start_time = ? AND production_plans.bidding_area_id = ? AND production_plans.production_type_id = ?\n", 488 "WHERE production_plans.record_created_time = ? AND production_plans.start_time = ? AND production_plans.bidding_area_id = ? AND production_plans.production_type_id = ?\n",
489 "2022-09-20 15:40:36,246 INFO sqlalchemy.engine.Engine [generated in 0.00069s] ('2022-09-20 15:40:36.217038', '2022-11-02 02:00:00.000000', 1, 6)\n", 489 "2022-09-21 09:05:41,388 INFO sqlalchemy.engine.Engine [generated in 0.00093s] ('2022-09-21 09:05:41.358126', '2022-11-02 02:00:00.000000', 1, 6)\n",
490 "2022-09-20 15:40:36,248 INFO sqlalchemy.engine.Engine UPDATE production_plans SET value=? WHERE production_plans.record_created_time = ? AND production_plans.start_time = ? AND production_plans.bidding_area_id = ? AND production_plans.production_type_id = ?\n", 490 "2022-09-21 09:05:41,390 INFO sqlalchemy.engine.Engine UPDATE production_plans SET value=? WHERE production_plans.record_created_time = ? AND production_plans.start_time = ? AND production_plans.bidding_area_id = ? AND production_plans.production_type_id = ?\n",
491 "2022-09-20 15:40:36,248 INFO sqlalchemy.engine.Engine [generated in 0.00064s] (70.5, '2022-09-20 15:40:36.217038', '2022-11-02 02:00:00.000000', 1, 6)\n", 491 "2022-09-21 09:05:41,391 INFO sqlalchemy.engine.Engine [generated in 0.00054s] (70.5, '2022-09-21 09:05:41.358126', '2022-11-02 02:00:00.000000', 1, 6)\n",
492 "2022-09-20 15:40:36,249 INFO sqlalchemy.engine.Engine COMMIT\n" 492 "2022-09-21 09:05:41,392 INFO sqlalchemy.engine.Engine COMMIT\n"
493 ] 493 ]
494 } 494 }
495 ], 495 ],
@@ -561,13 +561,189 @@
561 ] 561 ]
562 }, 562 },
563 { 563 {
564 "cell_type": "code",
565 "execution_count": 15,
566 "id": "875195c5-ae99-4aa8-9aca-89d6e9c27ae2",
567 "metadata": {},
568 "outputs": [
569 {
570 "name": "stdout",
571 "output_type": "stream",
572 "text": [
573 "2022-09-21 09:05:41,400 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
574 "2022-09-21 09:05:41,402 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n",
575 "FROM production_plans ORDER BY production_plans.start_time\n",
576 "2022-09-21 09:05:41,403 INFO sqlalchemy.engine.Engine [generated in 0.00078s] ()\n",
577 "2022-09-21 09:05:41,406 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n",
578 "FROM production_plans \n",
579 "WHERE production_plans.start_time > ?\n",
580 "2022-09-21 09:05:41,406 INFO sqlalchemy.engine.Engine [generated in 0.00070s] ('2022-09-01 00:00:00.000000',)\n",
581 "2022-09-21 09:05:41,409 INFO sqlalchemy.engine.Engine SELECT count(*) AS count_1 \n",
582 "FROM (SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n",
583 "FROM production_plans \n",
584 "WHERE production_plans.value > ? ORDER BY production_plans.start_time) AS anon_1\n",
585 "2022-09-21 09:05:41,410 INFO sqlalchemy.engine.Engine [generated in 0.00059s] (80,)\n",
586 "2022-09-21 09:05:41,412 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n",
587 "FROM production_plans \n",
588 "WHERE production_plans.value > ? ORDER BY production_plans.start_time\n",
589 " LIMIT ? OFFSET ?\n",
590 "2022-09-21 09:05:41,412 INFO sqlalchemy.engine.Engine [generated in 0.00056s] (80, 1, 0)\n",
591 "2022-09-21 09:05:41,414 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n",
592 "FROM production_plans \n",
593 "WHERE production_plans.value > ? ORDER BY production_plans.start_time\n",
594 "2022-09-21 09:05:41,415 INFO sqlalchemy.engine.Engine [generated in 0.00066s] (80,)\n",
595 "2022-09-21 09:05:41,417 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n",
596 "2022-09-21 09:05:41,418 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
597 "2022-09-21 09:05:41,419 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_plans\")\n",
598 "2022-09-21 09:05:41,420 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
599 "2022-09-21 09:05:41,421 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n",
600 "2022-09-21 09:05:41,422 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n",
601 "2022-09-21 09:05:41,423 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_plans\")\n",
602 "2022-09-21 09:05:41,424 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
603 "2022-09-21 09:05:41,425 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n",
604 "2022-09-21 09:05:41,425 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n",
605 "2022-09-21 09:05:41,427 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"bidding_areas\")\n",
606 "2022-09-21 09:05:41,427 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
607 "2022-09-21 09:05:41,428 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n",
608 "2022-09-21 09:05:41,429 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n",
609 "2022-09-21 09:05:41,430 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"bidding_areas\")\n",
610 "2022-09-21 09:05:41,430 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
611 "2022-09-21 09:05:41,432 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"bidding_areas\")\n",
612 "2022-09-21 09:05:41,432 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
613 "2022-09-21 09:05:41,434 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n",
614 "2022-09-21 09:05:41,434 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n",
615 "2022-09-21 09:05:41,436 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n",
616 "2022-09-21 09:05:41,436 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
617 "2022-09-21 09:05:41,437 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n",
618 "2022-09-21 09:05:41,438 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
619 "2022-09-21 09:05:41,439 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_bidding_areas_1\")\n",
620 "2022-09-21 09:05:41,440 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
621 "2022-09-21 09:05:41,441 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n",
622 "2022-09-21 09:05:41,441 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n",
623 "2022-09-21 09:05:41,443 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_types\")\n",
624 "2022-09-21 09:05:41,443 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
625 "2022-09-21 09:05:41,445 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n",
626 "2022-09-21 09:05:41,445 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n",
627 "2022-09-21 09:05:41,446 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_types\")\n",
628 "2022-09-21 09:05:41,447 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
629 "2022-09-21 09:05:41,448 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"production_types\")\n",
630 "2022-09-21 09:05:41,448 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
631 "2022-09-21 09:05:41,450 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n",
632 "2022-09-21 09:05:41,450 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n",
633 "2022-09-21 09:05:41,452 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n",
634 "2022-09-21 09:05:41,452 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
635 "2022-09-21 09:05:41,453 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n",
636 "2022-09-21 09:05:41,454 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
637 "2022-09-21 09:05:41,455 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_types_1\")\n",
638 "2022-09-21 09:05:41,456 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
639 "2022-09-21 09:05:41,457 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n",
640 "2022-09-21 09:05:41,458 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n",
641 "2022-09-21 09:05:41,459 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n",
642 "2022-09-21 09:05:41,459 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
643 "2022-09-21 09:05:41,461 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n",
644 "2022-09-21 09:05:41,461 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
645 "2022-09-21 09:05:41,462 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_plans_1\")\n",
646 "2022-09-21 09:05:41,462 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
647 "2022-09-21 09:05:41,464 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n",
648 "2022-09-21 09:05:41,464 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n",
649 "2022-09-21 09:05:41,466 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n",
650 "FROM production_plans\n",
651 "2022-09-21 09:05:41,467 INFO sqlalchemy.engine.Engine [generated in 0.00114s] ()\n",
652 "2022-09-21 09:05:41,471 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n",
653 "FROM production_plans \n",
654 "WHERE production_plans.value > ? ORDER BY production_plans.start_time\n",
655 "2022-09-21 09:05:41,472 INFO sqlalchemy.engine.Engine [generated in 0.00061s] (80,)\n",
656 "2022-09-21 09:05:41,475 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n",
657 "FROM production_plans, production_types \n",
658 "WHERE production_plans.production_type_id = production_types.production_type_id AND production_types.code = ?\n",
659 "2022-09-21 09:05:41,476 INFO sqlalchemy.engine.Engine [generated in 0.00097s] ('B37',)\n",
660 "2022-09-21 09:05:41,478 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n",
661 "FROM production_plans JOIN production_types ON production_types.production_type_id = production_plans.production_type_id \n",
662 "WHERE production_types.code = ?\n",
663 "2022-09-21 09:05:41,478 INFO sqlalchemy.engine.Engine [generated in 0.00060s] ('B37',)\n",
664 "2022-09-21 09:05:41,481 INFO sqlalchemy.engine.Engine SELECT production_types.production_type_id AS production_types_production_type_id, production_types.code AS production_types_code, production_types.description AS production_types_description \n",
665 "FROM production_types \n",
666 "WHERE production_types.production_type_id = ?\n",
667 "2022-09-21 09:05:41,482 INFO sqlalchemy.engine.Engine [generated in 0.00098s] (6,)\n",
668 "2022-09-21 09:05:41,483 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n",
669 "FROM production_plans \n",
670 "WHERE ? = production_plans.production_type_id\n",
671 "2022-09-21 09:05:41,484 INFO sqlalchemy.engine.Engine [generated in 0.00342s] (6,)\n",
672 "2022-09-21 09:05:41,486 INFO sqlalchemy.engine.Engine SELECT pp.* FROM production_plans pp, production_types pt WHERE pp.production_type_id = pt.production_type_id AND pt.code=?\n",
673 "2022-09-21 09:05:41,486 INFO sqlalchemy.engine.Engine [generated in 0.00058s] ('B37',)\n",
674 "2022-09-21 09:05:41,492 INFO sqlalchemy.engine.Engine SELECT bidding_areas.bidding_area_id AS bidding_areas_bidding_area_id, bidding_areas.code AS bidding_areas_code, bidding_areas.name AS bidding_areas_name \n",
675 "FROM bidding_areas \n",
676 "WHERE bidding_areas.bidding_area_id = ?\n",
677 "2022-09-21 09:05:41,492 INFO sqlalchemy.engine.Engine [generated in 0.00065s] (1,)\n"
678 ]
679 },
680 {
681 "name": "stderr",
682 "output_type": "stream",
683 "text": [
684 "/tmp/ipykernel_611/3660144138.py:7: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.\n",
685 " session.query(ProductionPlan).order_by(ProductionPlan.start_time).all() # returns an object-list\n"
686 ]
687 },
688 {
689 "data": {
690 "text/plain": [
691 "[<ProductionPlan(record_created_time=datetime.datetime(2022, 9, 21, 9, 5, 41, 357984), start_time=datetime.datetime(2022, 11, 2, 1, 0), production_type=<ProductionType(production_type_id=6, code='B37', description='Thermal unspecified')>, bidding_area=<BiddingArea(bidding_area_id=1, code='NO1', name='Elspot NO1')>, value=Decimal('80.5000000000'))>,\n",
692 " <ProductionPlan(record_created_time=datetime.datetime(2022, 9, 21, 9, 5, 41, 358126), start_time=datetime.datetime(2022, 11, 2, 2, 0), production_type=<ProductionType(production_type_id=6, code='B37', description='Thermal unspecified')>, bidding_area=<BiddingArea(bidding_area_id=1, code='NO1', name='Elspot NO1')>, value=Decimal('70.5000000000'))>]"
693 ]
694 },
695 "execution_count": 15,
696 "metadata": {},
697 "output_type": "execute_result"
698 }
699 ],
700 "source": [
701 "import pandas as pd\n",
702 "\n",
703 "from sqlalchemy import text\n",
704 "\n",
705 "\n",
706 "session.query(ProductionPlan).order_by(ProductionPlan.start_time) # returns a Query instance\n",
707 "session.query(ProductionPlan).order_by(ProductionPlan.start_time).all() # returns an object-list\n",
708 "\n",
709 "# return all production plans where start_time after 2022-09-01 00:00\n",
710 "session.query(ProductionPlan).filter(ProductionPlan.start_time > datetime.datetime(2022, 9, 1, 0, 0)).all()\n",
711 "\n",
712 "# return production plans with value > 80\n",
713 "query = session.query(ProductionPlan).filter(ProductionPlan.value > 80).order_by(ProductionPlan.start_time)\n",
714 "query.count() # returns 1\n",
715 "production_plan = query.first() # returns the first object (element)\n",
716 "production_plan = query.one() # raises NoResultFound exception or MultipleResultsFound in case elements != 1\n",
717 "\n",
718 "# generate Pandas DataFrame from a query or entire table\n",
719 "df = pd.read_sql_table(\"production_plans\", con=session.get_bind()) # or con=engine\n",
720 "df = pd.read_sql_query(query.statement, engine)\n",
721 "\n",
722 "# return production plans with production type 'B37'\n",
723 "session.query(ProductionPlan).filter(\n",
724 " ProductionPlan.production_type_id == ProductionType.production_type_id,\n",
725 " ProductionType.code == \"B37\"\n",
726 ").all()\n",
727 "session.query(ProductionPlan).join(ProductionType).filter(ProductionType.code == \"B37\").all()\n",
728 "session.query(ProductionPlan).filter(ProductionPlan.production_type == production_type_B37).all()\n",
729 "session.query(\n",
730 " ProductionPlan\n",
731 ").from_statement(\n",
732 " text(\n",
733 " \"SELECT pp.* FROM production_plans pp, production_types pt \"\n",
734 " \"WHERE pp.production_type_id = pt.production_type_id AND pt.code=:code\"\n",
735 " )\n",
736 ").params(code=\"B37\").all()\n"
737 ]
738 },
739 {
564 "cell_type": "markdown", 740 "cell_type": "markdown",
565 "id": "86336aaa-540c-466d-b5c1-f3fc179bd1e3", 741 "id": "86336aaa-540c-466d-b5c1-f3fc179bd1e3",
566 "metadata": {}, 742 "metadata": {},
567 "source": [ 743 "source": [
568 "# TODO\n", 744 "# TODO\n",
569 "\n", 745 "\n",
570 "- reflection\n" 746 "- delete\n"
571 ] 747 ]
572 } 748 }
573 ], 749 ],
diff --git a/reveal.js/sqlalchemy.html b/reveal.js/sqlalchemy.html
index 96f83e7..a0d6617 100755
--- a/reveal.js/sqlalchemy.html
+++ b/reveal.js/sqlalchemy.html
@@ -1,375 +1,477 @@
1<!doctype html> 1<!doctype html>
2<html lang="en"> 2<html lang="en">
3 <head> 3 <head>
4 <meta charset="utf-8"> 4 <meta charset="utf-8">
5 <title>SQLAlchemy</title> 5 <title>SQLAlchemy</title>
6 <meta name="author" content="Simeon Simeonov"> 6 <meta name="author" content="Simeon Simeonov">
7 <meta name="apple-mobile-web-app-capable" content="yes"> 7 <meta name="apple-mobile-web-app-capable" content="yes">
8 <meta name="apple-mobile-web-app-status-bar-style" content="black-translucent"> 8 <meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
9 <meta name="viewport" content="width=device-width, initial-scale=1.0"> 9 <meta name="viewport" content="width=device-width, initial-scale=1.0">
10 10
11 <link rel="stylesheet" href="dist/reset.css"> 11 <link rel="stylesheet" href="dist/reset.css">
12 <link rel="stylesheet" href="dist/reveal.css"> 12 <link rel="stylesheet" href="dist/reveal.css">
13 13
14 <link rel="stylesheet" href="dist/theme/statnett.css" id="theme"> 14 <link rel="stylesheet" href="dist/theme/statnett.css" id="theme">
15 15
16 <!-- Theme used for syntax highlighting of code --> 16 <!-- Theme used for syntax highlighting of code -->
17 <link rel="stylesheet" href="plugin/highlight/monokai.css" id="highlight-theme"> 17 <link rel="stylesheet" href="plugin/highlight/monokai.css" id="highlight-theme">
18 <!-- <link rel="stylesheet" href="plugin/highlight/zenburn.css" id="highlight-theme"> --> 18 <!-- <link rel="stylesheet" href="plugin/highlight/zenburn.css" id="highlight-theme"> -->
19 </head> 19 </head>
20 <body> 20 <body>
21 <div class="reveal"> 21 <div class="reveal">
22 22
23 <!-- Any section element inside of this container is displayed as a slide --> 23 <!-- Any section element inside of this container is displayed as a slide -->
24 <div class="slides"> 24 <div class="slides">
25 25
26 <section> 26 <section>
27 <h2>SQLAlchemy</h2> 27 <h2>SQLAlchemy</h2>
28 <h4>Data Science @ Statnett</h4> 28 <h4>Data Engineering @ Statnett</h4>
29 </br> 29 </br>
30 <p><small>Simeon Simeonov</small></p> 30 <p><small>Simeon Simeonov</small></p>
31 </section> 31 </section>
32 32
33 <section> 33 <section>
34 34
35 <section id="fragments"> 35 <section id="fragments">
36 <h2>Agenda</h2> 36 <h2>Agenda</h2>
37 </br> 37 </br>
38 <ul> 38 <ul>
39 <span class="fragment"><li>SQLAlchemy - Design & overview</li></span> 39 <span class="fragment"><li>SQLAlchemy - Design & overview</li></span>
40 <span class="fragment"><li>SQLAlchemy - A small practical example</li></span> 40 <span class="fragment"><li>SQLAlchemy - A small practical example</li></span>
41 <span class="fragment"><li>Q &amp; A</li></span> 41 <span class="fragment"><li>Q &amp; A</li></span>
42 </ul> 42 </ul>
43 </section> 43 </section>
44 44
45 </section> 45 </section>
46 46
47 <section> 47 <section>
48 <h2>What is SQLAlchemy?</h2> 48 <h2>What is SQLAlchemy?</h2>
49 </br> 49 </br>
50 <p><em>SQLAlchemy</em> is a Python library created by Mike Bayer to provide a high-level Pythonic interface to RDBMS such as <em>PostgreSQL</em>, <em>SQLite</em>, <em>MySQL</em>, <em>Oracle</em>, <em>DB2</em>.</p> 50 <p><em>SQLAlchemy</em> is a Python library created by Mike Bayer to provide a high-level Pythonic interface to RDBMS such as <em>PostgreSQL</em>, <em>SQLite</em>, <em>MySQL</em>, <em>Oracle</em>, <em>DB2</em>.</p>
51 <p><em>SQLAlchemy</em> includes RDBMS-independent <em>SQL expression language</em> and an <em>object-relational mapper (ORM)</em>.</p> 51 <p><em>SQLAlchemy</em> includes RDBMS-independent <em>SQL expression language</em> and an <em>object-relational mapper (ORM)</em>.</p>
52 </section> 52 </section>
53 53
54 <section> 54 <section>
55 <h2>Why use SQLAlchemy?</h2> 55 <h2>Why use SQLAlchemy?</h2>
56 </br> 56 </br>
57 <ul> 57 <ul>
58 <li><p>free software - free as in "freedom" (MIT licensed)</p></li> 58 <li><p>free software - free as in "freedom" (MIT licensed)</p></li>
59 <li><p>portability - the programming interface is independent of the type of RDBMS and connector used</p></li> 59 <li><p>portability - the programming interface is independent of the type of RDBMS and connector used</p></li>
60 <li><p>security - no more SQL injections</p></li> 60 <li><p>security - no more SQL injections</p></li>
61 <li><p>abstraction - no need to bother with complex JOINs</p></li> 61 <li><p>abstraction - no need to bother with complex JOINs</p></li>
62 <li><p>object-orientation - you work with objects instead of tables and rows</p></li> 62 <li><p>object-orientation - you work with objects instead of tables and rows</p></li>
63 <li><p>performance - exploits the likehood of reusing a particular query</p></li> 63 <li><p>performance - exploits the likehood of reusing a particular query</p></li>
64 <li><p>flexibility - you can override almost anything</p></li> 64 <li><p>flexibility - you can override almost anything</p></li>
65 </ul> 65 </ul>
66 </section> 66 </section>
67 67
68 <section> 68 <section>
69 <h2>Basic architecture</h2> 69 <h2>Basic architecture</h2>
70 <p><em>SQLAlchemy</em> consists of several components, including the <em>ORM</em>.</p> 70 <p><em>SQLAlchemy</em> consists of several components, including the <em>ORM</em>.</p>
71 <ul> 71 <ul>
72 <li><em>Engine</em>- manages the connection pool and the RDBMS-independent SQL dialect layer</li> 72 <li><em>Engine</em>- manages the connection pool and the RDBMS-independent SQL dialect layer</li>
73 <li><em>MetaData</em> - used to collect and organize information about your table layout (schema)</li> 73 <li><em>MetaData</em> - used to collect and organize information about your table layout (schema)</li>
74 <li><em>SQL expression language</em> - provides an API to execute your queries and updates against your tables, all from Python, and all in a database-independent way (low-level interface)</li> 74 <li><em>SQL expression language</em> - provides an API to execute your queries and updates against your tables, all from Python, and all in a database-independent way (low-level interface)</li>
75 <li><em>ORM</em> - provides a convenient way to add database persistence to your Python objects without requiring you to design your objects around the database, or the database around the objects (high-level interface)</li> 75 <li><em>ORM</em> - provides a convenient way to add database persistence to your Python objects without requiring you to design your objects around the database, or the database around the objects (high-level interface)</li>
76 <li><em>Session</em> - establishes all conversations with the RDBMS and represents a "holding zone" for all the objects which you've loaded or associated with it during its lifespan</li> 76 <li><em>Session</em> - establishes all conversations with the RDBMS and represents a "holding zone" for all the objects which you've loaded or associated with it during its lifespan</li>
77 </ul> 77 </ul>
78 <img src="images/sqlalchemy/sqla_arch.png"></img> 78 <img src="images/sqlalchemy/sqla_arch.png"></img>
79 </section> 79 </section>
80
81 <section data-auto-animate>
82 <h3>Example</h3>
83 <p><em>SQLAlchemy</em> gives us the choice between <em>classical mapping</em> and the newer <em>declarative mapping</em></p>
84 <pre id="smallertext" data-id="code-animation">
85 <code class="python" data-trim data-line-numbers type="text/template">
86 # option 1: classical mapping
87 # explicitly defining Table objects and mapping them to pure Python base classes
88 from sqlalchemy import create_engine
89
90 # engine = create_engine("postgresql+psycopg2://user:zipassword@localhost/mydb" , echo=True)
91 # The string form of the URL is dialect+driver://user:password@host/dbname[?key=value..],
92 # engine = create_engine("sqlite:///library.db", echo=True)
93 engine = create_engine("sqlite:///:memory:", echo=True)
94
95 from sqlalchemy import Column, MetaData, Table
96 from sqlalchemy import DateTime, ForeignKey, Integer, Numeric, String
97
98 metadata = MetaData()
99
100 production_types_table = Table(
101 "production_types",
102 metadata,
103 Column("production_type_id", Integer, primary_key=True),
104 Column("code", String(3), nullable=False, unique=True),
105 Column("description", String), # Column("name", String(128)) is possible
106 )
107
108 bidding_areas_table = Table(
109 "bidding_areas",
110 metadata,
111 Column("bidding_area_id", Integer, primary_key=True),
112 Column("code", String(3), nullable=False, unique=True),
113 Column("name", String(32)),
114 )
115
116 production_plans_table = Table(
117 "production_plans",
118 metadata,
119 Column("record_created_time", DateTime(timezone=False), primary_key=True),
120 Column("start_time", DateTime(timezone=False), primary_key=True),
121 Column("bidding_area_id", Integer, ForeignKey("bidding_areas.bidding_area_id"), primary_key=True),
122 Column("production_type_id", Integer, ForeignKey("production_types.production_type_id"), primary_key=True),
123 Column("value", Numeric, nullable=False),
124 )
80 125
81 <section data-auto-animate> 126 metadata.create_all(engine) # creates the tables
82 <h2>Example</h2> 127 </code>
83 <p><em>SQLAlchemy</em> gives us the choice between <em>classical mapping</em> and the newer <em>declarative mapping</em></p> 128 </pre>
84 <pre data-id="code-animation"> 129 </section>
85 <code class="python" data-trim data-line-numbers type="text/template">
86 import sqlalchemy
87 sqlalchemy.__version__
88 # Out: '1.3.23'
89 130
90 from sqlalchemy import create_engine 131 <section data-auto-animate>
132 <h2>Example (cont...)</h2>
133 <h4 data-id="code-title">Use of <em>SQL expression language</em></h4>
134 <pre data-id="code-animation">
135 <code class="python" data-trim data-line-numbers type="text/template">
136 # option 1: classical mapping (continues)
137 # Using the SQL expression language (low level interface)
138 from sqlalchemy import text
91 139
92 # engine = create_engine("postgresql+psycopg2://user:zipassword@localhost/mydb" , echo=True) 140 insert_stmt = bidding_areas_table.insert(bind=engine)
93 # The string form of the URL is dialect+driver://user:password@host/dbname[?key=value..], 141 type(insert_stmt)
94 # where dialect is a database name such as mysql, oracle, postgresql, etc., 142 # Out: &lt;class 'sqlalchemy.sql.dml.Insert'&gt;
95 # and driver the name of a DBAPI, such as psycopg2, pyodbc, cx_oracle 143 print(insert_stmt)
96 # The echo flag is a shortcut to setting up SQLAlchemy logging, 144 # Out: INSERT INTO bidding_areas (bidding_area_id, code, name) VALUES (?, ?, ?)
97 # which is accomplished via Python’s standard logging module.
98 # engine = create_engine("sqlite:///library.db", echo=True)
99 engine = create_engine("sqlite:///:memory:", echo=True)
100 145
101 from sqlalchemy import Column, ForeignKey, Integer, String, Table 146 compiled_stmt = insert_stmt.compile()
147 print(compiled_stmt.params)
148 # Out: {'bidding_area_id': None, 'code': None, 'name': None}
102 149
103 metadata = MetaData() 150 insert_stmt.execute(bidding_area_id=1, code="NO1", name="Elspot NO1") # insert a single entry
151 # ... or a list of entries
152 insert_stmt.execute(
153 [
154 {"bidding_area_id": 2, "code": "NO2", "name": "Elspot NO2"},
155 {"bidding_area_id": 3, "code": "NO3", "name": "Elspot NO3"},
156 {"bidding_area_id": 4, "code": "NO4", "name": "Elspot NO4"},
157 {"bidding_area_id": 5, "code": "NO5", "name": "Elspot NO5"},
158 {"bidding_area_id": 6, "code": "NO6", "name": "Elspot NO6"},
159 ]
160 )
104 161
105 authors_table = Table( 162 metadata.bind = engine # no need to explicitly bind the engine from now on
106 "authors", 163 select_stmt = bidding_areas_table.select(bidding_areas_table.c.bidding_area_id==2)
107 metadata, 164 result = select_stmt.execute()
108 Column("author_id", Integer, primary_key=True), 165 result.fetchall()
109 Column("name", String), 166 # Out: [(2, 'NO2', 'Elspot NO2')]
110 ) # Column("name", String(50)) is possible
111 167
112 books_table = Table( 168 del_stmt = bidding_areas_table.delete()
113 "books", 169 del_stmt.execute(whereclause=text("name='Elspot NO6'"))
114 metadata, 170 del_stmt.execute() # delete NO6
115 Column("book_id", Integer, primary_key=True), 171 </code>
116 Column("title", String), 172 </pre>
117 Column("description", String), 173 </section>
118 Column("author_id", ForeignKey('authors.author_id')),
119 )
120 174
121 metadata.create_all(engine) # creates the tables 175 <section data-auto-animate>
122 </code> 176 <h2>Example (cont...)</h2>
123 </pre> 177 <h4 data-id="code-title">Use of <em>classical mapping</em></h4>
124 </section> 178 <pre data-id="code-animation">
179 <code class="python" data-trim data-line-numbers type="text/template">
180 # option 1: classical mapping (continues)
181 # Defining regular base classes and mapping them to the Table objects
182 from sqlalchemy.orm import mapper
125 183
126 <section data-auto-animate> 184 class ProductionType:
127 <h2>Example (cont...)</h2> 185 def __init__(self, code, description):
128 <h4 data-id="code-title">Use of <em>SQL expression language</em></h4> 186 self.code = code
129 <pre data-id="code-animation"> 187 self.description = description
130 <code class="python" data-trim data-line-numbers type="text/template">
131 insert_stmt = authors_table.insert(bind=engine)
132 type(insert_stmt)
133 # Out: &lt;class 'sqlalchemy.sql.expression.Insert'&gt;
134 print(insert_stmt)
135 # Out: INSERT INTO authors (id, name) VALUES (:id,:name)
136 188
137 compiled_stmt = insert_stmt.compile() 189 def __str__(self):
138 print(compiled_stmt.params) 190 return self.code
139 # Out: {'id': None, 'name': None}
140 191
141 insert_stmt.execute(name="Alexandre Dumas") # insert a single entry
142 insert_stmt.execute([{"name": "Mr X"}, {"name": "Mr Y"}]) # a list of entries
143 192
144 metadata.bind = engine # no need to explicitly bind the engine from now on 193 class BiddingArea:
145 select_stmt = authors_table.select(authors_table.c.id==2) 194 def __init__(self, code, name):
146 result = select_stmt.execute() 195 self.code = code
147 result.fetchall() 196 self.name = name
148 # Out: [(1, 'Mr X')]
149 197
150 del_stmt = authors_table.delete() 198 def __str__(self):
151 del_stmt.execute(whereclause=text("name='Mr Y'")) 199 return self.code
152 del_stmt.execute() # delete all
153 </code>
154 </pre>
155 </section>
156 200
157 <section data-auto-animate> 201 mapper(ProductionType, production_types_table)
158 <h2>Example (cont...)</h2> 202 mapper(BiddingArea, bidding_areas_table)
159 <h4 data-id="code-title">Use of <em>classical mapping</em></h4> 203 </code>
160 <pre data-id="code-animation"> 204 </pre>
161 <code class="python" data-trim data-line-numbers type="text/template"> 205 </section>
162 from sqlalchemy.orm import backref, mapper, relation
163 206
164 class Author: 207 <section data-auto-animate>
165 def __init__(self, name): 208 <h2>Example (cont...)</h2>
166 self.name = name 209 <h4 data-id="code-title">Use of <em>classical mapping</em></h4>
210 <pre data-id="code-animation">
211 <code class="python" data-trim data-line-numbers type="text/template">
212 from sqlalchemy.orm import relationship
167 213
168 def __str__(self): 214 class ProductionPlan:
169 return self.name 215 def __init__(self, record_created_time, start_time, production_type, bidding_area, value):
216 self.record_created_time = record_created_time
217 self.start_time = start_time
218 self.production_type = production_type
219 self.bidding_area = bidding_area
220 self.value = value
170 221
222 def __str__(self):
223 return (
224 f"{self.record_created_time} {self.start_time} "
225 f"{self.production_type} {self.bidding_area} {self.value}"
226 )
171 227
172 class Book:
173 def __init__(self, title, description, author):
174 self.title = title
175 self.description = description
176 self.author = author
177 228
178 def __str__(self): 229 mapper(
179 return self.title 230 ProductionPlan,
231 production_plans_table,
232 properties = {
233 "production_type": relationship(ProductionType, backref="production_plans"),
234 "bidding_area": relationship(BiddingArea, backref="production_plans"),
235 },
236 )
237 </code>
238 </pre>
239 </section>
180 240
181 mapper(Book, books_table) 241 <section data-auto-animate>
182 mapper(Author, authors_table, properties = {"books": relation(Book, backref="author")}) 242 <h2>Example (cont...)</h2>
183 </code> 243 <h4 data-id="code-title">Doing the same thing the easy way with <em>declarative mapping</em></h4>
184 </pre> 244 <pre data-id="code-animation">
185 </section> 245 <code class="python" data-trim data-line-numbers type="text/template">
246 # option 2: declarative mapping
247 from sqlalchemy.ext.declarative import declarative_base
186 248
187 <section data-auto-animate> 249 Base = declarative_base()
188 <h2>Example (cont...)</h2>
189 <h4 data-id="code-title">Doing the same thing the easy way with <em>declarative mapping</em></h4>
190 <pre data-id="code-animation">
191 <code class="python" data-trim data-line-numbers type="text/template">
192 from sqlalchemy.ext.declarative import declarative_base
193 from sqlalchemy.orm import relationship, backref
194 250
195 Base = declarative_base() 251 class ProductionType(Base):
252 __tablename__ = "production_types"
196 253
197 class Author(Base): 254 production_type_id = Column(Integer, primary_key=True)
198 __tablename__ = "authors" 255 code = Column(String(3), nullable=False, unique=True)
256 description = Column(String)
199 257
200 author_id = Column(Integer, primary_key=True) 258 def __init__(self, code, description):
201 name = Column(String) 259 self.code = code
260 self.description = description
202 261
203 def __init__(self, name): 262 def __str__(self):
204 self.name = name 263 return self.code
205 264
206 def __str__(self): 265 class BiddingArea(Base):
207 return self.name 266 __tablename__ = "bidding_areas"
208 267
268 bidding_area_id = Column(Integer, primary_key=True)
269 code = Column(String(3), nullable=False, unique=True)
270 name = Column(String(32))
209 271
210 class Book(Base): 272 def __init__(self, code, name):
211 __tablename__ = "books" # self.__table__ will be available for our objects 273 self.code = code
274 self.name = name
212 275
213 book_id = Column(Integer, primary_key=True) 276 def __str__(self):
214 title = Column(String) 277 return self.code
215 description = Column(String) 278 </code>
216 author_id = Column(Integer, ForeignKey("authors.author_id")) 279 </pre>
217 author = relationship(Author, backref=backref("books", order_by=title)) 280 </section>
218 281
219 def __init__(self, title, description, author): 282 <section data-auto-animate>
220 self.title = title 283 <h2>Example (cont...)</h2>
221 self.description = description 284 <h4 data-id="code-title">Doing the same thing the easy way with <em>declarative mapping</em></h4>
222 self.author = author 285 <pre data-id="code-animation">
286 <code class="python" data-trim data-line-numbers type="text/template">
287 # option 2: declarative mapping (continues)
288 from sqlalchemy.orm import relationship, backref
223 289
224 def __str__(self): 290 class ProductionPlan(Base):
225 return self.title 291 __tablename__ = "production_plans"
226 292
227 Base.metadata.create_all(engine) # create tables 293 record_created_time = Column(DateTime(timezone=False), primary_key=True)
228 </code> 294 start_time = Column(DateTime(timezone=False), primary_key=True)
229 </pre> 295 bidding_area_id = Column(Integer, ForeignKey("bidding_areas.bidding_area_id"), primary_key=True)
230 </section> 296 production_type_id = Column(Integer, ForeignKey("production_types.production_type_id"), primary_key=True)
297 value = Column(Numeric, nullable=False)
231 298
232 <section data-auto-animate> 299 # defining relationships.
233 <h2>Example (cont...)</h2> 300 # the defined attributes will reference 'ProductionType' and 'BiddingArea' objects
234 <h4 data-id="code-title">Creating instances</h4> 301 production_type = relationship(ProductionType, backref=backref("production_plans"))
235 <pre data-id="code-animation"> 302 bidding_area = relationship(BiddingArea, backref=backref("production_plans"))
236 <code class="python" data-trim data-line-numbers type="text/template">
237 from sqlalchemy.orm import sessionmaker
238 303
239 Session = sessionmaker(bind=engine) # bound session 304 def __init__(self, record_created_time, start_time, production_type, bidding_area, value):
240 session = Session() 305 self.record_created_time = record_created_time
306 self.start_time = start_time
307 self.production_type = production_type # a 'ProductionType' object
308 self.bidding_area = bidding_area # a 'BiddingArea' object
309 self.value = value
241 310
242 author_1 = Author("Richard Dawkins") 311 def __str__(self):
243 author_2 = Author("Matt Ridley") 312 return (
313 f"{self.record_created_time} {self.start_time} "
314 f"{self.production_type} {self.bidding_area} {self.value}"
315 )
244 316
245 book_1 = Book("The Red Queen", "A popular science book", author_2) 317 Base.metadata.create_all(engine) # create tables
246 book_2 = Book("The Selfish Gene", "A popular science book", author_1) 318 </code>
247 book_3 = Book("The Blind Watchmaker", "The theory of evolutio", author_1) # typo 319 </pre>
320 </section>
248 321
249 session.add(author_1) 322 <section data-auto-animate>
250 session.add(author_2) 323 <h2>Example (cont...)</h2>
251 session.add(book_1) 324 <h4 data-id="code-title">Creating instances</h4>
252 session.add(book_2) 325 <pre data-id="code-animation">
253 session.add(book_3) 326 <code class="python" data-trim data-line-numbers type="text/template">
254 # or simply session.add_all([author_1, author_2, book_1, book_2, book_3]) 327 # adding some data...
328 import datetime
329 import decimal
255 330
256 # session.flush() 331 from sqlalchemy.orm import sessionmaker
257 session.commit() # flushes (issues the statements and sends them to the RDBMS) and commits
258 332
259 book_3.description = "The theory of evolution" # update the object 333 Session = sessionmaker(bind=engine) # bound session
260 book_3 in session # check whether the object is in the session 334 session = Session()
261 # Out: True
262 335
263 session.commit() 336 bidding_area1 = BiddingArea("NO1", "Elspot NO1")
264 </code> 337 session.add(bidding_area1)
265 </pre>
266 </section>
267 338
268 <section data-auto-animate> 339 session.add_all(
269 <h2>Example (cont...)</h2> 340 [
270 <h4 data-id="code-title">Queries</h4> 341 BiddingArea("NO2", "Elspot NO2"),
271 <pre data-id="code-animation"> 342 BiddingArea("NO3", "Elspot NO3"),
272 <code class="python" data-trim data-line-numbers type="text/template"> 343 BiddingArea("NO4", "Elspot NO4"),
273 session.query(Book).order_by(Book.book_id) # returns a Query instance with a .statement attribute 344 BiddingArea("NO5", "Elspot NO5"),
274 session.query(Book).order_by(Book.book_id).all() # returns an object-list 345 ]
346 )
275 347
276 # return all book objects where title == "The Selfish Gene" 348 production_type_B37 = ProductionType("B37", "Thermal unspecified")
277 session.query(Book).filter(Book.title == "The Selfish Gene").order_by(Book.book_id).all() 349 production_type_B30 = ProductionType("B30", "Wind unspecified")
278 350
279 # using LIKE 351 session.add_all(
280 session.query(Book).filter(Book.title.like("The%")).order_by(Book.book_id).all() 352 [
353 ProductionType("B19", "Wind Onshore"),
354 ProductionType("B10", "Hydro-electric pure pumped storage head installation"),
355 ProductionType("B11", "Hydro Run-of-river head installation"),
356 ProductionType("B12", "Hydro-electric storage head installation"),
357 ProductionType("A04", "Generation"),
358 production_type_B37,
359 production_type_B30,
360 ]
361 )
362 </code>
363 </pre>
364 </section>
281 365
282 query = session.query(Book).filter(Book.book_id == 9).order_by(Book.book_id) 366 <section data-auto-animate>
283 query.count() # returns 0 367 <h2>Example (cont...)</h2>
284 query.all() # returns an empty list 368 <h4 data-id="code-title">Creating instances</h4>
285 query.first() # returns None 369 <pre data-id="code-animation">
286 query.one() # raises NoResultFound exception 370 <code class="python" data-trim data-line-numbers type="text/template">
371 # adding some production plans...
287 372
288 query = session.query(Book).filter(Book.book_id == 1).order_by(Book.book_id) 373 session.add(
289 book_1 = query.one() 374 ProductionPlan(
290 book_1.description # returns "A popular science book" 375 datetime.datetime.now(),
291 book_1.author.books # returns a list of Book-objects representing all the books from the same author. 376 datetime.datetime(2022, 11, 2, 1, 0),
377 production_type_B37,
378 bidding_area1,
379 decimal.Decimal("80.5"),
380 )
381 )
292 382
293 # get a list of all Book-instances where the author"s name is "Richard Dawkins" 383 production_plan2 = ProductionPlan(
294 session.query(Book).filter(Book.author_id == Author.author_id).filter(Author.name == "Richard Dawkins").all() 384 datetime.datetime.now(),
295 session.query(Book).join(Author).filter(Author.name == "Richard Dawkins").all() 385 datetime.datetime(2022, 11, 2, 2, 0),
296 session.query(Book).\ 386 production_type_B37,
297 from_statement("SELECT b.* FROM books b, authors a WHERE b.author_id = a.author_id AND a.name=:name").\ 387 bidding_area1,
298 params(name="Richard Dawkins").all() 388 decimal.Decimal("90.5"),
299 session.query(Book).filter(Book.author == author_1).all() 389 )
300 </code>
301 </pre>
302 </section>
303 390
304 <section> 391 session.add(production_plan2)
305 <h2>Some nice features</h2>
306 <pre data-id="code-animation">
307 <code class="python" data-trim data-line-numbers type="text/template">
308 import pandas as pd
309 392
310 from sqlalchemy import func 393 session.flush() # execute pending operations
394 session.commit() # execute and commit pending operations
311 395
312 class Book(Base): 396 production_plan2.value = decimal.Decimal("70.5")
313 # ... 397 production_plan2 in session
314 author = relationship( 398 # Out: True
315 Author, backref=backref("books", lazy="dynamic", order_by=title)
316 )
317 # ...
318 399
319 @hybrid_property 400 session.commit()
320 def newly_arrived(self): 401 </code>
321 return self.book_id > 2 402 </pre>
403 </section>
322 404
323 @newly_arrived.expression 405 <section data-auto-animate>
324 def newly_arrived(cls): 406 <h2>Example (cont...)</h2>
325 return cls.book_id > 2 407 <h4 data-id="code-title">Queries</h4>
326 # return func.abs(cls.book_id) > 2 408 <pre data-id="code-animation">
409 <code class="python" data-trim data-line-numbers type="text/template">
410 import pandas as pd
327 411
328 # .books is now a Query object 412 session.query(ProductionPlan).order_by(ProductionPlan.start_time) # returns a Query instance
329 query = author_obj.books.filter(Book.title.ilike("%red%")) 413 session.query(ProductionPlan).order_by(ProductionPlan.start_time).all() # returns an object-list
330 414
331 session.query(Book).filter(Book.newly_arrived.is_(True)).all() 415 # return all production plans where start_time after 2022-09-01 00:00
332 # Out: [<__main__.Book at 0x7f132bdf0130>] 416 session.query(ProductionPlan).filter(ProductionPlan.start_time > datetime.datetime(2022, 9, 1, 0, 0)).all()
333 # WHERE (abs(books.book_id) > ?) IS 1 ... in the case of func.abs
334 417
335 # with Pandas 418 # return production plans with value > 80
336 df = pd.read_sql_table("my_table", con=session.get_bind()) # or con=engine 419 query = session.query(ProductionPlan).filter(ProductionPlan.value > 80).order_by(ProductionPlan.start_time)
420 query.count() # returns 1
421 production_plan = query.first() # returns the first object (element)
422 production_plan = query.one() # raises NoResultFound exception or MultipleResultsFound in case elements != 1
337 423
338 df = pd.read_sql_query(query.statement, engine) 424 # generate Pandas DataFrame from a query or entire table
425 df = pd.read_sql_table("my_table", con=session.get_bind()) # or con=engine
426 df = pd.read_sql_query(query.statement, engine)
339 427
340 </code> 428 # return production plans with production type 'B37'
341 </pre> 429 session.query(ProductionPlan).filter(
342 </section> 430 ProductionPlan.production_type_id == ProductionType.production_type_id
431 ).filter(ProductionType.code == "B37").all()
432 session.query(ProductionPlan).join(ProductionType).filter(ProductionType.code == "B37").all()
433 session.query(ProductionPlan).filter(ProductionPlan.production_type == production_type_B37).all()
434 session.query(
435 ProductionPlan
436 ).from_statement(
437 text(
438 "SELECT pp.* FROM production_plans pp, production_types pt "
439 "WHERE pp.production_type_id = pt.production_type_id AND pt.code=:code"
440 )
441 ).params(code="B37").all()
442 </code>
443 </pre>
444 </section>
343 445
344 <section> 446 <section>
345 <h1>Q &amp; A</h1> 447 <h1>Q &amp; A</h1>
346 </section> 448 </section>
347 449
348 </div> 450 </div>
349 </div> 451 </div>
350 452
351 <script src="dist/reveal.js"></script> 453 <script src="dist/reveal.js"></script>
352 <script src="plugin/zoom/zoom.js"></script> 454 <script src="plugin/zoom/zoom.js"></script>
353 <script src="plugin/notes/notes.js"></script> 455 <script src="plugin/notes/notes.js"></script>
354 <script src="plugin/search/search.js"></script> 456 <script src="plugin/search/search.js"></script>
355 <script src="plugin/markdown/markdown.js"></script> 457 <script src="plugin/markdown/markdown.js"></script>
356 <script src="plugin/highlight/highlight.js"></script> 458 <script src="plugin/highlight/highlight.js"></script>
357 <script> 459 <script>
358 460
359 // Also available as an ES module, see: 461 // Also available as an ES module, see:
360 // https://revealjs.netlify.app/initialization/ 462 // https://revealjs.netlify.app/initialization/
361 Reveal.initialize({ 463 Reveal.initialize({
362 controls: true, 464 controls: true,
363 progress: true, 465 progress: true,
364 center: true, 466 center: true,
365 hash: true, 467 hash: true,
366 468
367 // Learn about plugins: https://revealjs.netlify.app/plugins/ 469 // Learn about plugins: https://revealjs.netlify.app/plugins/
368 plugins: [ RevealZoom, RevealNotes, RevealSearch, RevealMarkdown, RevealHighlight ] 470 plugins: [ RevealZoom, RevealNotes, RevealSearch, RevealMarkdown, RevealHighlight ]
369 }); 471 });
370 Reveal.configure({ pdfSeparateFragments: false }); 472 Reveal.configure({ pdfSeparateFragments: false });
371 473
372 </script> 474 </script>
373 475
374 </body> 476 </body>
375</html> 477</html>
generated by cgit v1.3 (git 2.53.0) at 2026-09-25 09:43:13 +0000