summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorSimeon Simeonov2022-09-20 09:10:35 +0200
committerSimeon Simeonov2022-09-20 09:10:35 +0200
commit0e50a48d04184aac1d940369590599697d0a874b (patch)
tree223a4d39d2bac39e8db7f881852b70206643e84d
parent4ef55e4d52e06d071354bd7f6f6ba0a86243fc0e (diff)
Add notebooks presentations folder and add sqlalchemy
Diffstat
-rw-r--r--notebooks/sqlalchemy/images/sqla_arch.pngbin0 -> 42731 bytes
-rw-r--r--notebooks/sqlalchemy/sqlalchemy.ipynb379
2 files changed, 379 insertions, 0 deletions
diff --git a/notebooks/sqlalchemy/images/sqla_arch.png b/notebooks/sqlalchemy/images/sqla_arch.png
new file mode 100644
index 0000000..a1c0958
--- /dev/null
+++ b/notebooks/sqlalchemy/images/sqla_arch.png
Binary files differ
diff --git a/notebooks/sqlalchemy/sqlalchemy.ipynb b/notebooks/sqlalchemy/sqlalchemy.ipynb
new file mode 100644
index 0000000..f2f65cc
--- /dev/null
+++ b/notebooks/sqlalchemy/sqlalchemy.ipynb
@@ -0,0 +1,379 @@
1{
2 "cells": [
3 {
4 "cell_type": "markdown",
5 "id": "f37249e7-a474-407f-ae2e-17def087bd72",
6 "metadata": {
7 "tags": []
8 },
9 "source": [
10 "# SQLAlchemy\n",
11 "\n",
12 "Simeon Simeonov @ Statnett\n",
13 "\n",
14 "\n",
15 "## Agenda\n",
16 "\n",
17 "- Design & overview\n",
18 "\n",
19 "- A small practical example\n"
20 ]
21 },
22 {
23 "cell_type": "markdown",
24 "id": "19d6fced-434e-4604-bcb7-b71fadfe6dcb",
25 "metadata": {},
26 "source": [
27 "# What is SQLAlchemy?\n",
28 "\n",
29 "SQLAlchemy is a Python library created by Mike Bayer to provide a high-level Pythonic interface to RDBMS such as PostgreSQL, SQLite, MySQL, Oracle, DB2.\n",
30 "\n",
31 "SQLAlchemy includes RDBMS-independent SQL expression language and an object-relational mapper (ORM).\n"
32 ]
33 },
34 {
35 "cell_type": "markdown",
36 "id": "fa5ad88f-d2ec-4d86-9982-73f7b9fe0e9b",
37 "metadata": {},
38 "source": [
39 "# Why use SQLAlchemy?\n",
40 "\n",
41 "- free software - free as in \"freedom\" (MIT licensed)\n",
42 "\n",
43 "- portability - the programming interface is independent of the type of RDBMS and connector used\n",
44 "\n",
45 "- security - no more SQL injections\n",
46 "\n",
47 "- abstraction - no need to bother with complex JOINs\n",
48 "\n",
49 "- object-orientation - you work with objects instead of tables and rows\n",
50 "\n",
51 "- performance - exploits the likehood of reusing a particular query\n",
52 "\n",
53 "- flexibility - you can override almost anything\n"
54 ]
55 },
56 {
57 "cell_type": "markdown",
58 "id": "d68798f0-de17-44f9-b677-d7ee06409a9c",
59 "metadata": {},
60 "source": [
61 "# Basic architecture\n",
62 "\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",
66 "\n",
67 "- MetaData - used to collect and organize information about your table layout (schema)\n",
68 "\n",
69 "- SQL expression language - 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)\n",
70 "\n",
71 "- ORM - 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)\n",
72 "\n",
73 "- Session - 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\n",
74 "\n",
75 "![title](images/sqla_arch.png)"
76 ]
77 },
78 {
79 "cell_type": "code",
80 "execution_count": 1,
81 "id": "d1ea9f51-e3de-45a8-8302-b4a89b5d1689",
82 "metadata": {},
83 "outputs": [],
84 "source": [
85 "from sqlalchemy import create_engine\n",
86 "\n",
87 "# engine = create_engine(\"postgresql+psycopg2://user:zipassword@localhost/mydb\" , echo=True)\n",
88 "# The string form of the URL is dialect+driver://user:password@host/d[?key=valuebname..],\n",
89 "# where dialect is a database name such as mysql, oracle, postgresql, etc.,\n",
90 "# and driver the name of a DBAPI, such as psycopg2, pyodbc, cx_oracle\n",
91 "# The echo flag is a shortcut to setting up SQLAlchemy logging,\n",
92 "# which is accomplished via Python’s standard logging module.\n",
93 "\n",
94 "# engine = create_engine(\"sqlite:///library.db\", echo=True)\n",
95 "engine = create_engine(\"sqlite:///:memory:\", echo=True)"
96 ]
97 },
98 {
99 "cell_type": "code",
100 "execution_count": 2,
101 "id": "3b4a777d-7d38-4149-8ab2-c876e2533766",
102 "metadata": {},
103 "outputs": [
104 {
105 "name": "stdout",
106 "output_type": "stream",
107 "text": [
108 "2022-09-19 15:33:48,105 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",
110 "2022-09-19 15:33:48,107 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",
112 "2022-09-19 15:33:48,108 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",
114 "2022-09-19 15:33:48,110 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",
116 "2022-09-19 15:33:48,112 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",
118 "2022-09-19 15:33:48,113 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",
120 "2022-09-19 15:33:48,115 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
121 "2022-09-19 15:33:48,117 INFO sqlalchemy.engine.Engine \n",
122 "CREATE TABLE production_types (\n",
123 "\tproduction_type_id INTEGER NOT NULL, \n",
124 "\tcode VARCHAR(3) NOT NULL, \n",
125 "\tdescription VARCHAR, \n",
126 "\tPRIMARY KEY (production_type_id), \n",
127 "\tUNIQUE (code)\n",
128 ")\n",
129 "\n",
130 "\n",
131 "2022-09-19 15:33:48,117 INFO sqlalchemy.engine.Engine [no key 0.00045s] ()\n",
132 "2022-09-19 15:33:48,119 INFO sqlalchemy.engine.Engine \n",
133 "CREATE TABLE bidding_areas (\n",
134 "\tbidding_area_id INTEGER NOT NULL, \n",
135 "\tcode VARCHAR(3) NOT NULL, \n",
136 "\tname VARCHAR(32), \n",
137 "\tPRIMARY KEY (bidding_area_id), \n",
138 "\tUNIQUE (code)\n",
139 ")\n",
140 "\n",
141 "\n",
142 "2022-09-19 15:33:48,119 INFO sqlalchemy.engine.Engine [no key 0.00051s] ()\n",
143 "2022-09-19 15:33:48,121 INFO sqlalchemy.engine.Engine \n",
144 "CREATE TABLE production_plans (\n",
145 "\trecord_created_time DATETIME NOT NULL, \n",
146 "\tstart_time DATETIME NOT NULL, \n",
147 "\tbidding_area_id INTEGER NOT NULL, \n",
148 "\tproduction_type_id INTEGER NOT NULL, \n",
149 "\tvalue NUMERIC NOT NULL, \n",
150 "\tPRIMARY KEY (record_created_time, start_time, bidding_area_id, production_type_id), \n",
151 "\tFOREIGN KEY(bidding_area_id) REFERENCES bidding_areas (bidding_area_id), \n",
152 "\tFOREIGN KEY(production_type_id) REFERENCES production_types (production_type_id)\n",
153 ")\n",
154 "\n",
155 "\n",
156 "2022-09-19 15:33:48,122 INFO sqlalchemy.engine.Engine [no key 0.00095s] ()\n",
157 "2022-09-19 15:33:48,123 INFO sqlalchemy.engine.Engine COMMIT\n"
158 ]
159 }
160 ],
161 "source": [
162 "from sqlalchemy import Column, MetaData, Table\n",
163 "from sqlalchemy import DateTime, ForeignKey, Integer, Numeric, String\n",
164 "from sqlalchemy.orm import relationship\n",
165 "\n",
166 "metadata = MetaData()\n",
167 "\n",
168 "from sqlalchemy.ext.declarative import declarative_base\n",
169 "\n",
170 "Base = declarative_base()\n",
171 "\n",
172 "\n",
173 "class ProductionType(Base):\n",
174 " __tablename__ = \"production_types\"\n",
175 "\n",
176 " production_type_id = Column(Integer, primary_key=True)\n",
177 " code = Column(String(3), nullable=False, unique=True)\n",
178 " description = Column(String)\n",
179 "\n",
180 " production_plans = relationship(\n",
181 " \"ProductionPlan\", back_populates=\"production_type\", lazy=\"dynamic\"\n",
182 " )\n",
183 "\n",
184 " def __init__(self, code, description):\n",
185 " self.code = code\n",
186 " self.description = description\n",
187 "\n",
188 " def __str__(self):\n",
189 " return self.code\n",
190 "\n",
191 "\n",
192 "class BiddingArea(Base):\n",
193 " __tablename__ = \"bidding_areas\"\n",
194 "\n",
195 " bidding_area_id = Column(Integer, primary_key=True)\n",
196 " code = Column(String(3), nullable=False, unique=True)\n",
197 " name = Column(String(32))\n",
198 "\n",
199 " production_plans = relationship(\n",
200 " \"ProductionPlan\", back_populates=\"bidding_area\", lazy=\"dynamic\"\n",
201 " )\n",
202 "\n",
203 " def __init__(self, code, name):\n",
204 " self.code = code\n",
205 " self.name = name\n",
206 "\n",
207 " def __str__(self):\n",
208 " return self.code\n",
209 "\n",
210 "\n",
211 "class ProductionPlan(Base):\n",
212 " __tablename__ = \"production_plans\"\n",
213 "\n",
214 " record_created_time = Column(DateTime(timezone=False), primary_key=True)\n",
215 " start_time = Column(DateTime(timezone=False), primary_key=True)\n",
216 " bidding_area_id = Column(Integer, ForeignKey(\"bidding_areas.bidding_area_id\"), primary_key=True)\n",
217 " production_type_id = Column(Integer, ForeignKey(\"production_types.production_type_id\"), primary_key=True)\n",
218 " value = Column(Numeric, nullable=False)\n",
219 "\n",
220 " # defining relationships.\n",
221 " # the defined attributes will reference 'ProductionType' and 'BiddingArea' objects\n",
222 " production_type = relationship(ProductionType, back_populates=\"production_plans\")\n",
223 " bidding_area = relationship(BiddingArea, back_populates=\"production_plans\")\n",
224 "\n",
225 " def __init__(self, record_created_time, start_time, production_type, bidding_area, value):\n",
226 " self.record_created_time = record_created_time\n",
227 " self.start_time = start_time\n",
228 " self.production_type = production_type # a 'ProductionType' object\n",
229 " self.bidding_area = bidding_area # a 'BiddingArea' object\n",
230 " self.value = value\n",
231 "\n",
232 " def __str__(self):\n",
233 " return (\n",
234 " f\"{self.record_created_time} {self.start_time} \"\n",
235 " f\"{self.production_type} {self.bidding_area} {self.value}\"\n",
236 " )\n",
237 "\n",
238 "Base.metadata.create_all(engine) # create tables\n"
239 ]
240 },
241 {
242 "cell_type": "code",
243 "execution_count": 3,
244 "id": "1f18d9c1-bd39-4195-b868-114c3296eac2",
245 "metadata": {},
246 "outputs": [
247 {
248 "name": "stdout",
249 "output_type": "stream",
250 "text": [
251 "2022-09-19 15:33:48,160 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
252 "2022-09-19 15:33:48,163 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n",
253 "2022-09-19 15:33:48,164 INFO sqlalchemy.engine.Engine [generated in 0.00128s] ('NO1', 'Elspot NO1')\n",
254 "2022-09-19 15:33:48,166 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n",
255 "2022-09-19 15:33:48,167 INFO sqlalchemy.engine.Engine [cached since 0.003926s ago] ('NO2', 'Elspot NO2')\n",
256 "2022-09-19 15:33:48,168 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n",
257 "2022-09-19 15:33:48,169 INFO sqlalchemy.engine.Engine [cached since 0.005792s ago] ('NO3', 'Elspot NO3')\n",
258 "2022-09-19 15:33:48,170 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n",
259 "2022-09-19 15:33:48,171 INFO sqlalchemy.engine.Engine [cached since 0.007965s ago] ('NO4', 'Elspot NO4')\n",
260 "2022-09-19 15:33:48,172 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n",
261 "2022-09-19 15:33:48,172 INFO sqlalchemy.engine.Engine [cached since 0.009323s ago] ('NO5', 'Elspot NO5')\n",
262 "2022-09-19 15:33:48,174 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n",
263 "2022-09-19 15:33:48,175 INFO sqlalchemy.engine.Engine [generated in 0.00066s] ('B19', 'Wind Onshore')\n",
264 "2022-09-19 15:33:48,176 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\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",
266 "2022-09-19 15:33:48,178 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\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",
268 "2022-09-19 15:33:48,179 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\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",
270 "2022-09-19 15:33:48,181 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n",
271 "2022-09-19 15:33:48,181 INFO sqlalchemy.engine.Engine [cached since 0.007409s ago] ('A04', 'Generation')\n",
272 "2022-09-19 15:33:48,183 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n",
273 "2022-09-19 15:33:48,183 INFO sqlalchemy.engine.Engine [cached since 0.009187s ago] ('B37', 'Thermal unspecified')\n",
274 "2022-09-19 15:33:48,184 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n",
275 "2022-09-19 15:33:48,184 INFO sqlalchemy.engine.Engine [cached since 0.01042s ago] ('B30', 'Wind unspecified')\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",
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",
278 "2022-09-19 15:33:48,189 INFO sqlalchemy.engine.Engine COMMIT\n",
279 "2022-09-19 15:33:48,191 INFO sqlalchemy.engine.Engine BEGIN (implicit)\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",
281 "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",
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",
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",
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",
286 "2022-09-19 15:33:48,199 INFO sqlalchemy.engine.Engine COMMIT\n"
287 ]
288 }
289 ],
290 "source": [
291 "# adding some data...\n",
292 "import datetime\n",
293 "import decimal\n",
294 "\n",
295 "from sqlalchemy.orm import sessionmaker\n",
296 "\n",
297 "Session = sessionmaker(bind=engine) # bound session\n",
298 "session = Session()\n",
299 "\n",
300 "bidding_area1 = BiddingArea(\"NO1\", \"Elspot NO1\")\n",
301 "session.add(bidding_area1)\n",
302 "\n",
303 "session.add_all(\n",
304 " [\n",
305 " BiddingArea(\"NO2\", \"Elspot NO2\"),\n",
306 " BiddingArea(\"NO3\", \"Elspot NO3\"),\n",
307 " BiddingArea(\"NO4\", \"Elspot NO4\"),\n",
308 " BiddingArea(\"NO5\", \"Elspot NO5\"),\n",
309 " ]\n",
310 ")\n",
311 "\n",
312 "production_type_B37 = ProductionType(\"B37\", \"Thermal unspecified\")\n",
313 "production_type_B30 = ProductionType(\"B30\", \"Wind unspecified\")\n",
314 "\n",
315 "session.add_all(\n",
316 " [\n",
317 " ProductionType(\"B19\", \"Wind Onshore\"),\n",
318 " ProductionType(\"B10\", \"Hydro-electric pure pumped storage head installation\"),\n",
319 " ProductionType(\"B11\", \"Hydro Run-of-river head installation\"),\n",
320 " ProductionType(\"B12\", \"Hydro-electric storage head installation\"),\n",
321 " ProductionType(\"A04\", \"Generation\"),\n",
322 " production_type_B37,\n",
323 " production_type_B30,\n",
324 " ]\n",
325 ")\n",
326 "\n",
327 "session.add(\n",
328 " ProductionPlan(\n",
329 " datetime.datetime.now(),\n",
330 " datetime.datetime(2022, 11, 2, 1, 0),\n",
331 " production_type_B37,\n",
332 " bidding_area1,\n",
333 " decimal.Decimal(\"80.5\"),\n",
334 " )\n",
335 ")\n",
336 "\n",
337 "production_plan2 = ProductionPlan(\n",
338 " datetime.datetime.now(),\n",
339 " datetime.datetime(2022, 11, 2, 2, 0),\n",
340 " production_type_B37,\n",
341 " bidding_area1,\n",
342 " decimal.Decimal(\"90.5\"),\n",
343 ")\n",
344 "\n",
345 "session.add(production_plan2)\n",
346 "\n",
347 "session.flush() # execute pending operations\n",
348 "session.commit() # execute and commit pending operations (implicit flush)\n",
349 "\n",
350 "production_plan2.value = decimal.Decimal(\"70.5\")\n",
351 "production_plan2 in session\n",
352 "# Out: True\n",
353 "\n",
354 "session.commit()\n"
355 ]
356 }
357 ],
358 "metadata": {
359 "kernelspec": {
360 "display_name": "Python 3 (ipykernel)",
361 "language": "python",
362 "name": "python3"
363 },
364 "language_info": {
365 "codemirror_mode": {
366 "name": "ipython",
367 "version": 3
368 },
369 "file_extension": ".py",
370 "mimetype": "text/x-python",
371 "name": "python",
372 "nbconvert_exporter": "python",
373 "pygments_lexer": "ipython3",
374 "version": "3.8.10"
375 }
376 },
377 "nbformat": 4,
378 "nbformat_minor": 5
379}
generated by cgit v1.3 (git 2.53.0) at 2026-09-25 09:43:53 +0000