summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorSimeon Simeonov2022-09-20 15:44:45 +0200
committerSimeon Simeonov2022-09-20 15:44:45 +0200
commitc0487f22a9d0b85d12868e91a9e17a833e8dd351 (patch)
tree9446bf4497254527c5887d6fdb965b3b2406a2ec
parent0e50a48d04184aac1d940369590599697d0a874b (diff)
Add reflection example in notebooks/sqlalchemy/sqlalchemy.ipynb
Diffstat
-rw-r--r--notebooks/sqlalchemy/sqlalchemy.ipynb359
1 files changed, 288 insertions, 71 deletions
diff --git a/notebooks/sqlalchemy/sqlalchemy.ipynb b/notebooks/sqlalchemy/sqlalchemy.ipynb
index f2f65cc..ce0070c 100644
--- a/notebooks/sqlalchemy/sqlalchemy.ipynb
+++ b/notebooks/sqlalchemy/sqlalchemy.ipynb
@@ -2,8 +2,11 @@
2 "cells": [ 2 "cells": [
3 { 3 {
4 "cell_type": "markdown", 4 "cell_type": "markdown",
5 "id": "f37249e7-a474-407f-ae2e-17def087bd72", 5 "id": "8d981972-51c1-4f44-98fb-47abefacffb7",
6 "metadata": { 6 "metadata": {
7 "slideshow": {
8 "slide_type": "slide"
9 },
7 "tags": [] 10 "tags": []
8 }, 11 },
9 "source": [ 12 "source": [
@@ -22,7 +25,11 @@
22 { 25 {
23 "cell_type": "markdown", 26 "cell_type": "markdown",
24 "id": "19d6fced-434e-4604-bcb7-b71fadfe6dcb", 27 "id": "19d6fced-434e-4604-bcb7-b71fadfe6dcb",
25 "metadata": {}, 28 "metadata": {
29 "slideshow": {
30 "slide_type": "slide"
31 }
32 },
26 "source": [ 33 "source": [
27 "# What is SQLAlchemy?\n", 34 "# What is SQLAlchemy?\n",
28 "\n", 35 "\n",
@@ -34,7 +41,11 @@
34 { 41 {
35 "cell_type": "markdown", 42 "cell_type": "markdown",
36 "id": "fa5ad88f-d2ec-4d86-9982-73f7b9fe0e9b", 43 "id": "fa5ad88f-d2ec-4d86-9982-73f7b9fe0e9b",
37 "metadata": {}, 44 "metadata": {
45 "slideshow": {
46 "slide_type": "slide"
47 }
48 },
38 "source": [ 49 "source": [
39 "# Why use SQLAlchemy?\n", 50 "# Why use SQLAlchemy?\n",
40 "\n", 51 "\n",
@@ -56,12 +67,14 @@
56 { 67 {
57 "cell_type": "markdown", 68 "cell_type": "markdown",
58 "id": "d68798f0-de17-44f9-b677-d7ee06409a9c", 69 "id": "d68798f0-de17-44f9-b677-d7ee06409a9c",
59 "metadata": {}, 70 "metadata": {
71 "slideshow": {
72 "slide_type": "slide"
73 }
74 },
60 "source": [ 75 "source": [
61 "# Basic architecture\n", 76 "# Basic architecture\n",
62 "\n", 77 "\n",
63 "SQLAlchemy consists of several components, including the ORM.\n",
64 "\n",
65 "- Engine- manages the connection pool and the RDBMS-independent SQL dialect layer\n", 78 "- Engine- manages the connection pool and the RDBMS-independent SQL dialect layer\n",
66 "\n", 79 "\n",
67 "- MetaData - used to collect and organize information about your table layout (schema)\n", 80 "- MetaData - used to collect and organize information about your table layout (schema)\n",
@@ -77,9 +90,13 @@
77 }, 90 },
78 { 91 {
79 "cell_type": "code", 92 "cell_type": "code",
80 "execution_count": 1, 93 "execution_count": 12,
81 "id": "d1ea9f51-e3de-45a8-8302-b4a89b5d1689", 94 "id": "d1ea9f51-e3de-45a8-8302-b4a89b5d1689",
82 "metadata": {}, 95 "metadata": {
96 "slideshow": {
97 "slide_type": "slide"
98 }
99 },
83 "outputs": [], 100 "outputs": [],
84 "source": [ 101 "source": [
85 "from sqlalchemy import create_engine\n", 102 "from sqlalchemy import create_engine\n",
@@ -97,28 +114,32 @@
97 }, 114 },
98 { 115 {
99 "cell_type": "code", 116 "cell_type": "code",
100 "execution_count": 2, 117 "execution_count": 13,
101 "id": "3b4a777d-7d38-4149-8ab2-c876e2533766", 118 "id": "3b4a777d-7d38-4149-8ab2-c876e2533766",
102 "metadata": {}, 119 "metadata": {
120 "slideshow": {
121 "slide_type": "subslide"
122 }
123 },
103 "outputs": [ 124 "outputs": [
104 { 125 {
105 "name": "stdout", 126 "name": "stdout",
106 "output_type": "stream", 127 "output_type": "stream",
107 "text": [ 128 "text": [
108 "2022-09-19 15:33:48,105 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", 129 "2022-09-20 15:40:36,125 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
109 "2022-09-19 15:33:48,106 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_types\")\n", 130 "2022-09-20 15:40:36,126 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_types\")\n",
110 "2022-09-19 15:33:48,107 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 131 "2022-09-20 15:40:36,126 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
111 "2022-09-19 15:33:48,108 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"production_types\")\n", 132 "2022-09-20 15:40:36,127 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"production_types\")\n",
112 "2022-09-19 15:33:48,108 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 133 "2022-09-20 15:40:36,128 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
113 "2022-09-19 15:33:48,110 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"bidding_areas\")\n", 134 "2022-09-20 15:40:36,129 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"bidding_areas\")\n",
114 "2022-09-19 15:33:48,110 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 135 "2022-09-20 15:40:36,129 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
115 "2022-09-19 15:33:48,112 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"bidding_areas\")\n", 136 "2022-09-20 15:40:36,130 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"bidding_areas\")\n",
116 "2022-09-19 15:33:48,112 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 137 "2022-09-20 15:40:36,131 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
117 "2022-09-19 15:33:48,113 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n", 138 "2022-09-20 15:40:36,132 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n",
118 "2022-09-19 15:33:48,113 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 139 "2022-09-20 15:40:36,132 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
119 "2022-09-19 15:33:48,115 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"production_plans\")\n", 140 "2022-09-20 15:40:36,133 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"production_plans\")\n",
120 "2022-09-19 15:33:48,115 INFO sqlalchemy.engine.Engine [raw sql] ()\n", 141 "2022-09-20 15:40:36,134 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
121 "2022-09-19 15:33:48,117 INFO sqlalchemy.engine.Engine \n", 142 "2022-09-20 15:40:36,136 INFO sqlalchemy.engine.Engine \n",
122 "CREATE TABLE production_types (\n", 143 "CREATE TABLE production_types (\n",
123 "\tproduction_type_id INTEGER NOT NULL, \n", 144 "\tproduction_type_id INTEGER NOT NULL, \n",
124 "\tcode VARCHAR(3) NOT NULL, \n", 145 "\tcode VARCHAR(3) NOT NULL, \n",
@@ -128,8 +149,8 @@
128 ")\n", 149 ")\n",
129 "\n", 150 "\n",
130 "\n", 151 "\n",
131 "2022-09-19 15:33:48,117 INFO sqlalchemy.engine.Engine [no key 0.00045s] ()\n", 152 "2022-09-20 15:40:36,137 INFO sqlalchemy.engine.Engine [no key 0.00055s] ()\n",
132 "2022-09-19 15:33:48,119 INFO sqlalchemy.engine.Engine \n", 153 "2022-09-20 15:40:36,138 INFO sqlalchemy.engine.Engine \n",
133 "CREATE TABLE bidding_areas (\n", 154 "CREATE TABLE bidding_areas (\n",
134 "\tbidding_area_id INTEGER NOT NULL, \n", 155 "\tbidding_area_id INTEGER NOT NULL, \n",
135 "\tcode VARCHAR(3) NOT NULL, \n", 156 "\tcode VARCHAR(3) NOT NULL, \n",
@@ -139,8 +160,8 @@
139 ")\n", 160 ")\n",
140 "\n", 161 "\n",
141 "\n", 162 "\n",
142 "2022-09-19 15:33:48,119 INFO sqlalchemy.engine.Engine [no key 0.00051s] ()\n", 163 "2022-09-20 15:40:36,138 INFO sqlalchemy.engine.Engine [no key 0.00049s] ()\n",
143 "2022-09-19 15:33:48,121 INFO sqlalchemy.engine.Engine \n", 164 "2022-09-20 15:40:36,140 INFO sqlalchemy.engine.Engine \n",
144 "CREATE TABLE production_plans (\n", 165 "CREATE TABLE production_plans (\n",
145 "\trecord_created_time DATETIME NOT NULL, \n", 166 "\trecord_created_time DATETIME NOT NULL, \n",
146 "\tstart_time DATETIME NOT NULL, \n", 167 "\tstart_time DATETIME NOT NULL, \n",
@@ -153,18 +174,16 @@
153 ")\n", 174 ")\n",
154 "\n", 175 "\n",
155 "\n", 176 "\n",
156 "2022-09-19 15:33:48,122 INFO sqlalchemy.engine.Engine [no key 0.00095s] ()\n", 177 "2022-09-20 15:40:36,141 INFO sqlalchemy.engine.Engine [no key 0.00068s] ()\n",
157 "2022-09-19 15:33:48,123 INFO sqlalchemy.engine.Engine COMMIT\n" 178 "2022-09-20 15:40:36,142 INFO sqlalchemy.engine.Engine COMMIT\n"
158 ] 179 ]
159 } 180 }
160 ], 181 ],
161 "source": [ 182 "source": [
162 "from sqlalchemy import Column, MetaData, Table\n", 183 "from sqlalchemy import Column, Table\n",
163 "from sqlalchemy import DateTime, ForeignKey, Integer, Numeric, String\n", 184 "from sqlalchemy import DateTime, ForeignKey, Integer, Numeric, String\n",
164 "from sqlalchemy.orm import relationship\n", 185 "from sqlalchemy.orm import relationship\n",
165 "\n", 186 "\n",
166 "metadata = MetaData()\n",
167 "\n",
168 "from sqlalchemy.ext.declarative import declarative_base\n", 187 "from sqlalchemy.ext.declarative import declarative_base\n",
169 "\n", 188 "\n",
170 "Base = declarative_base()\n", 189 "Base = declarative_base()\n",
@@ -185,6 +204,12 @@
185 " self.code = code\n", 204 " self.code = code\n",
186 " self.description = description\n", 205 " self.description = description\n",
187 "\n", 206 "\n",
207 " def __repr__(self):\n",
208 " return (\n",
209 " f\"<ProductionType(production_type_id={self.production_type_id!r}, \"\n",
210 " f\"code={self.code!r}, description={self.description!r})>\"\n",
211 " )\n",
212 "\n",
188 " def __str__(self):\n", 213 " def __str__(self):\n",
189 " return self.code\n", 214 " return self.code\n",
190 "\n", 215 "\n",
@@ -204,6 +229,12 @@
204 " self.code = code\n", 229 " self.code = code\n",
205 " self.name = name\n", 230 " self.name = name\n",
206 "\n", 231 "\n",
232 " def __repr__(self):\n",
233 " return (\n",
234 " f\"<BiddingArea(bidding_area_id={self.bidding_area_id!r}, \"\n",
235 " f\"code={self.code!r}, name={self.name!r})>\"\n",
236 " )\n",
237 "\n",
207 " def __str__(self):\n", 238 " def __str__(self):\n",
208 " return self.code\n", 239 " return self.code\n",
209 "\n", 240 "\n",
@@ -229,6 +260,15 @@
229 " self.bidding_area = bidding_area # a 'BiddingArea' object\n", 260 " self.bidding_area = bidding_area # a 'BiddingArea' object\n",
230 " self.value = value\n", 261 " self.value = value\n",
231 "\n", 262 "\n",
263 " def __repr__(self):\n",
264 " return (\n",
265 " f\"<ProductionPlan(record_created_time={self.record_created_time!r}, \"\n",
266 " f\"start_time={self.start_time!r}, \"\n",
267 " f\"production_type={self.production_type!r}, \"\n",
268 " f\"bidding_area={self.bidding_area!r}, \"\n",
269 " f\"value={self.value!r})>\"\n",
270 " )\n",
271 "\n",
232 " def __str__(self):\n", 272 " def __str__(self):\n",
233 " return (\n", 273 " return (\n",
234 " f\"{self.record_created_time} {self.start_time} \"\n", 274 " f\"{self.record_created_time} {self.start_time} \"\n",
@@ -239,51 +279,217 @@
239 ] 279 ]
240 }, 280 },
241 { 281 {
282 "cell_type": "markdown",
283 "id": "ab7e9561-2780-4d85-843c-b217d15ee4c8",
284 "metadata": {},
285 "source": [
286 "# Reflection\n",
287 "\n",
288 "*Table* object can be instructed to load information about itself from the corresponding database schema object already existing within the database.\n",
289 "This process is called *reflection*.\n",
290 "\n",
291 "If the DB schema is already defined and maintained \"somewhere else\", it may be useful to \"reflect\" the schema instead of explicitly defining it."
292 ]
293 },
294 {
242 "cell_type": "code", 295 "cell_type": "code",
243 "execution_count": 3, 296 "execution_count": 14,
244 "id": "1f18d9c1-bd39-4195-b868-114c3296eac2", 297 "id": "70b0c5fa-35ae-44b5-b57b-2fa21b4f7da5",
245 "metadata": {}, 298 "metadata": {},
246 "outputs": [ 299 "outputs": [
247 { 300 {
248 "name": "stdout", 301 "name": "stdout",
249 "output_type": "stream", 302 "output_type": "stream",
250 "text": [ 303 "text": [
251 "2022-09-19 15:33:48,160 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", 304 "2022-09-20 15:40:36,151 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_types\")\n",
252 "2022-09-19 15:33:48,163 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", 305 "2022-09-20 15:40:36,152 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
253 "2022-09-19 15:33:48,164 INFO sqlalchemy.engine.Engine [generated in 0.00128s] ('NO1', 'Elspot NO1')\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",
254 "2022-09-19 15:33:48,166 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", 307 "2022-09-20 15:40:36,153 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n",
255 "2022-09-19 15:33:48,167 INFO sqlalchemy.engine.Engine [cached since 0.003926s ago] ('NO2', 'Elspot NO2')\n", 308 "2022-09-20 15:40:36,155 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_types\")\n",
256 "2022-09-19 15:33:48,168 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", 309 "2022-09-20 15:40:36,156 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
257 "2022-09-19 15:33:48,169 INFO sqlalchemy.engine.Engine [cached since 0.005792s ago] ('NO3', 'Elspot NO3')\n", 310 "2022-09-20 15:40:36,157 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"production_types\")\n",
258 "2022-09-19 15:33:48,170 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", 311 "2022-09-20 15:40:36,158 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
259 "2022-09-19 15:33:48,171 INFO sqlalchemy.engine.Engine [cached since 0.007965s ago] ('NO4', 'Elspot NO4')\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",
260 "2022-09-19 15:33:48,172 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", 313 "2022-09-20 15:40:36,160 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n",
261 "2022-09-19 15:33:48,172 INFO sqlalchemy.engine.Engine [cached since 0.009323s ago] ('NO5', 'Elspot NO5')\n", 314 "2022-09-20 15:40:36,161 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n",
262 "2022-09-19 15:33:48,174 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", 315 "2022-09-20 15:40:36,162 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
263 "2022-09-19 15:33:48,175 INFO sqlalchemy.engine.Engine [generated in 0.00066s] ('B19', 'Wind Onshore')\n", 316 "2022-09-20 15:40:36,163 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n",
264 "2022-09-19 15:33:48,176 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", 317 "2022-09-20 15:40:36,164 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
265 "2022-09-19 15:33:48,177 INFO sqlalchemy.engine.Engine [cached since 0.002644s ago] ('B10', 'Hydro-electric pure pumped storage head installation')\n", 318 "2022-09-20 15:40:36,165 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_types_1\")\n",
266 "2022-09-19 15:33:48,178 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", 319 "2022-09-20 15:40:36,166 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
267 "2022-09-19 15:33:48,178 INFO sqlalchemy.engine.Engine [cached since 0.004299s ago] ('B11', 'Hydro Run-of-river head installation')\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",
268 "2022-09-19 15:33:48,179 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", 321 "2022-09-20 15:40:36,169 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n",
269 "2022-09-19 15:33:48,180 INFO sqlalchemy.engine.Engine [cached since 0.005933s ago] ('B12', 'Hydro-electric storage head installation')\n", 322 "2022-09-20 15:40:36,171 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"bidding_areas\")\n",
270 "2022-09-19 15:33:48,181 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", 323 "2022-09-20 15:40:36,172 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
271 "2022-09-19 15:33:48,181 INFO sqlalchemy.engine.Engine [cached since 0.007409s ago] ('A04', 'Generation')\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",
272 "2022-09-19 15:33:48,183 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", 325 "2022-09-20 15:40:36,174 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n",
273 "2022-09-19 15:33:48,183 INFO sqlalchemy.engine.Engine [cached since 0.009187s ago] ('B37', 'Thermal unspecified')\n", 326 "2022-09-20 15:40:36,175 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"bidding_areas\")\n",
274 "2022-09-19 15:33:48,184 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", 327 "2022-09-20 15:40:36,176 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
275 "2022-09-19 15:33:48,184 INFO sqlalchemy.engine.Engine [cached since 0.01042s ago] ('B30', 'Wind unspecified')\n", 328 "2022-09-20 15:40:36,176 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"bidding_areas\")\n",
276 "2022-09-19 15:33:48,187 INFO sqlalchemy.engine.Engine INSERT INTO production_plans (record_created_time, start_time, bidding_area_id, production_type_id, value) VALUES (?, ?, ?, ?, ?)\n", 329 "2022-09-20 15:40:36,177 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
277 "2022-09-19 15:33:48,187 INFO sqlalchemy.engine.Engine [generated in 0.00072s] (('2022-09-19 15:33:48.159940', '2022-11-02 01:00:00.000000', 1, 6, 80.5), ('2022-09-19 15:33:48.160093', '2022-11-02 02:00:00.000000', 1, 6, 90.5))\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",
278 "2022-09-19 15:33:48,189 INFO sqlalchemy.engine.Engine COMMIT\n", 331 "2022-09-20 15:40:36,179 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n",
279 "2022-09-19 15:33:48,191 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", 332 "2022-09-20 15:40:36,181 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n",
280 "2022-09-19 15:33:48,194 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", 333 "2022-09-20 15:40:36,181 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",
335 "2022-09-20 15:40:36,183 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",
337 "2022-09-20 15:40:36,185 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",
339 "2022-09-20 15:40:36,186 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",
341 "2022-09-20 15:40:36,189 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",
343 "2022-09-20 15:40:36,191 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",
345 "2022-09-20 15:40:36,194 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",
347 "2022-09-20 15:40:36,196 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",
349 "2022-09-20 15:40:36,197 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",
351 "2022-09-20 15:40:36,199 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",
353 "2022-09-20 15:40:36,201 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",
355 "2022-09-20 15:40:36,202 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n"
356 ]
357 }
358 ],
359 "source": [
360 "from sqlalchemy.ext.declarative import declarative_base\n",
361 "\n",
362 "Base = declarative_base()\n",
363 "Base.metadata.bind = engine\n",
364 "\n",
365 "\n",
366 "class ProductionType(Base):\n",
367 " __tablename__ = \"production_types\"\n",
368 " __table_args__ = {\"autoload\": True}\n",
369 "\n",
370 " # override the existing 'description'...\n",
371 " description = Column(String(64))\n",
372 "\n",
373 " production_plans = relationship(\n",
374 " \"ProductionPlan\", back_populates=\"production_type\", lazy=\"dynamic\"\n",
375 " )\n",
376 "\n",
377 " def __init__(self, code, description):\n",
378 " self.code = code\n",
379 " self.description = description\n",
380 "\n",
381 " def __repr__(self):\n",
382 " return (\n",
383 " f\"<ProductionType(production_type_id={self.production_type_id!r}, \"\n",
384 " f\"code={self.code!r}, description={self.description!r})>\"\n",
385 " )\n",
386 "\n",
387 " def __str__(self):\n",
388 " return self.code\n",
389 "\n",
390 "\n",
391 "class BiddingArea(Base):\n",
392 " __tablename__ = \"bidding_areas\"\n",
393 " __table_args__ = {\"autoload\": True}\n",
394 "\n",
395 " production_plans = relationship(\n",
396 " \"ProductionPlan\", back_populates=\"bidding_area\", lazy=\"dynamic\"\n",
397 " )\n",
398 "\n",
399 " def __init__(self, code, name):\n",
400 " self.code = code\n",
401 " self.name = name\n",
402 "\n",
403 " def __repr__(self):\n",
404 " return (\n",
405 " f\"<BiddingArea(bidding_area_id={self.bidding_area_id!r}, \"\n",
406 " f\"code={self.code!r}, name={self.name!r})>\"\n",
407 " )\n",
408 "\n",
409 " def __str__(self):\n",
410 " return self.code\n",
411 "\n",
412 "\n",
413 "class ProductionPlan(Base):\n",
414 " __tablename__ = \"production_plans\"\n",
415 " __table_args__ = {\"autoload\": True}\n",
416 "\n",
417 " production_type = relationship(ProductionType, back_populates=\"production_plans\")\n",
418 " bidding_area = relationship(BiddingArea, back_populates=\"production_plans\")\n",
419 "\n",
420 " def __init__(self, record_created_time, start_time, production_type, bidding_area, value):\n",
421 " self.record_created_time = record_created_time\n",
422 " self.start_time = start_time\n",
423 " self.production_type = production_type # a 'ProductionType' object\n",
424 " self.bidding_area = bidding_area # a 'BiddingArea' object\n",
425 " self.value = value\n",
426 "\n",
427 " def __repr__(self):\n",
428 " return (\n",
429 " f\"<ProductionPlan(record_created_time={self.record_created_time!r}, \"\n",
430 " f\"start_time={self.start_time!r}, \"\n",
431 " f\"production_type={self.production_type!r}, \"\n",
432 " f\"bidding_area={self.bidding_area!r}, \"\n",
433 " f\"value={self.value!r})>\"\n",
434 " )\n",
435 "\n",
436 " def __str__(self):\n",
437 " return (\n",
438 " f\"{self.record_created_time} {self.start_time} \"\n",
439 " f\"{self.production_type} {self.bidding_area} {self.value}\"\n",
440 " )\n"
441 ]
442 },
443 {
444 "cell_type": "code",
445 "execution_count": 15,
446 "id": "1f18d9c1-bd39-4195-b868-114c3296eac2",
447 "metadata": {
448 "slideshow": {
449 "slide_type": "subslide"
450 }
451 },
452 "outputs": [
453 {
454 "name": "stdout",
455 "output_type": "stream",
456 "text": [
457 "2022-09-20 15:40:36,217 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",
459 "2022-09-20 15:40:36,219 INFO sqlalchemy.engine.Engine [generated in 0.00067s] ('NO1', 'Elspot NO1')\n",
460 "2022-09-20 15:40:36,221 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",
462 "2022-09-20 15:40:36,222 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",
464 "2022-09-20 15:40:36,224 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",
466 "2022-09-20 15:40:36,226 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",
468 "2022-09-20 15:40:36,228 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",
470 "2022-09-20 15:40:36,229 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",
472 "2022-09-20 15:40:36,231 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",
474 "2022-09-20 15:40:36,233 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",
476 "2022-09-20 15:40:36,234 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",
478 "2022-09-20 15:40:36,236 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",
480 "2022-09-20 15:40:36,237 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",
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",
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",
484 "2022-09-20 15:40:36,242 INFO sqlalchemy.engine.Engine COMMIT\n",
485 "2022-09-20 15:40:36,243 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",
281 "FROM production_plans \n", 487 "FROM production_plans \n",
282 "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",
283 "2022-09-19 15:33:48,195 INFO sqlalchemy.engine.Engine [generated in 0.00095s] ('2022-09-19 15:33:48.160093', '2022-11-02 02:00:00.000000', 1, 6)\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",
284 "2022-09-19 15:33:48,197 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-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",
285 "2022-09-19 15:33:48,198 INFO sqlalchemy.engine.Engine [generated in 0.00097s] (70.5, '2022-09-19 15:33:48.160093', '2022-11-02 02:00:00.000000', 1, 6)\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",
286 "2022-09-19 15:33:48,199 INFO sqlalchemy.engine.Engine COMMIT\n" 492 "2022-09-20 15:40:36,249 INFO sqlalchemy.engine.Engine COMMIT\n"
287 ] 493 ]
288 } 494 }
289 ], 495 ],
@@ -298,7 +504,7 @@
298 "session = Session()\n", 504 "session = Session()\n",
299 "\n", 505 "\n",
300 "bidding_area1 = BiddingArea(\"NO1\", \"Elspot NO1\")\n", 506 "bidding_area1 = BiddingArea(\"NO1\", \"Elspot NO1\")\n",
301 "session.add(bidding_area1)\n", 507 "session.add(bidding_area1),\n",
302 "\n", 508 "\n",
303 "session.add_all(\n", 509 "session.add_all(\n",
304 " [\n", 510 " [\n",
@@ -307,7 +513,7 @@
307 " BiddingArea(\"NO4\", \"Elspot NO4\"),\n", 513 " BiddingArea(\"NO4\", \"Elspot NO4\"),\n",
308 " BiddingArea(\"NO5\", \"Elspot NO5\"),\n", 514 " BiddingArea(\"NO5\", \"Elspot NO5\"),\n",
309 " ]\n", 515 " ]\n",
310 ")\n", 516 "),\n",
311 "\n", 517 "\n",
312 "production_type_B37 = ProductionType(\"B37\", \"Thermal unspecified\")\n", 518 "production_type_B37 = ProductionType(\"B37\", \"Thermal unspecified\")\n",
313 "production_type_B30 = ProductionType(\"B30\", \"Wind unspecified\")\n", 519 "production_type_B30 = ProductionType(\"B30\", \"Wind unspecified\")\n",
@@ -353,9 +559,20 @@
353 "\n", 559 "\n",
354 "session.commit()\n" 560 "session.commit()\n"
355 ] 561 ]
562 },
563 {
564 "cell_type": "markdown",
565 "id": "86336aaa-540c-466d-b5c1-f3fc179bd1e3",
566 "metadata": {},
567 "source": [
568 "# TODO\n",
569 "\n",
570 "- reflection\n"
571 ]
356 } 572 }
357 ], 573 ],
358 "metadata": { 574 "metadata": {
575 "celltoolbar": "Slideshow",
359 "kernelspec": { 576 "kernelspec": {
360 "display_name": "Python 3 (ipykernel)", 577 "display_name": "Python 3 (ipykernel)",
361 "language": "python", 578 "language": "python",
generated by cgit v1.3 (git 2.53.0) at 2026-09-25 09:43:25 +0000