1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
|
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title>SQLAlchemy</title>
<meta name="author" content="Simeon Simeonov"/>
<meta name="mobile-web-app-capable" content="yes"/>
<meta name="mobile-web-app-status-bar-style" content="black-translucent"/>
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
<link rel="stylesheet" href="dist/reset.css"/>
<link rel="stylesheet" href="dist/reveal.css"/>
<!-- <link rel="stylesheet" href="dist/theme/black.css" id="theme"/> -->
<link rel="stylesheet" href="dist/theme/statnett.css" id="theme"/>
<!-- Theme used for syntax highlighting of code -->
<link rel="stylesheet" href="plugin/highlight/monokai.css" id="highlight-theme"/>
<!-- <link rel="stylesheet" href="plugin/highlight/zenburn.css" id="highlight-theme"/> -->
</head>
<body>
<div class="reveal">
<!-- Any section element inside of this container is displayed as a slide -->
<div class="slides">
<section>
<h2>SQLAlchemy</h2>
<h4>Data Engineering @ Statnett</h4>
</br>
<p><small>Simeon Simeonov</small></p>
</section>
<section>
<section id="fragments">
<h2>Agenda</h2>
</br>
<ul>
<span class="fragment"><li>SQLAlchemy - Design & overview</li></span>
<span class="fragment"><li>SQLAlchemy - A small practical example</li></span>
<span class="fragment"><li>Q & A</li></span>
</ul>
</section>
</section>
<section>
<h2>What is SQLAlchemy?</h2>
</br>
<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>
<p><em>SQLAlchemy</em> includes RDBMS-independent <em>SQL expression language</em> and an <em>object-relational mapper (ORM)</em>.</p>
</section>
<section>
<h2>Why use SQLAlchemy?</h2>
</br>
<ul>
<li><p>free software - free as in "freedom" (MIT licensed)</p></li>
<li><p>portability - the programming interface is independent of the type of RDBMS and connector used</p></li>
<li><p>security - no more SQL injections</p></li>
<li><p>abstraction - no need to bother with complex JOINs</p></li>
<li><p>object-orientation - you work with objects instead of tables and rows</p></li>
<li><p>performance - exploits the likehood of reusing a particular query</p></li>
<li><p>flexibility - you can override almost anything</p></li>
</ul>
</section>
<section>
<h2>Basic architecture</h2>
<p><em>SQLAlchemy</em> consists of several components, including the <em>ORM</em>.</p>
<ul>
<li><em>Engine</em>- manages the connection pool and the RDBMS-independent SQL dialect layer</li>
<li><em>MetaData</em> - used to collect and organize information about your table layout (schema)</li>
<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>
<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>
<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>
</ul>
<img src="images/sqlalchemy/sqla_arch.png"></img>
</section>
<section data-auto-animate>
<h3>Example</h3>
<p><em>SQLAlchemy</em> gives us the choice between <em>classical mapping</em> and the newer <em>declarative mapping</em></p>
<pre id="smallertext" data-id="code-animation">
<code class="python" data-trim data-line-numbers type="text/template">
# option 1: classical mapping
# explicitly defining Table objects and mapping them to pure Python base classes
from sqlalchemy import create_engine
# engine = create_engine("postgresql+psycopg2://user:zipassword@localhost/mydb" , echo=True)
# The string form of the URL is dialect+driver://user:password@host/dbname[?key=value..],
# engine = create_engine("sqlite:///library.db", echo=True)
engine = create_engine("sqlite:///:memory:", echo=True)
from sqlalchemy import Column, MetaData, Table
from sqlalchemy import DateTime, ForeignKey, Integer, Numeric, String
metadata = MetaData()
production_types_table = Table(
"production_types",
metadata,
Column("production_type_id", Integer, primary_key=True),
Column("code", String(3), nullable=False, unique=True),
Column("description", String), # Column("name", String(128)) is possible
)
bidding_areas_table = Table(
"bidding_areas",
metadata,
Column("bidding_area_id", Integer, primary_key=True),
Column("code", String(3), nullable=False, unique=True),
Column("name", String(32)),
)
production_plans_table = Table(
"production_plans",
metadata,
Column("record_created_time", DateTime(timezone=False), primary_key=True),
Column("start_time", DateTime(timezone=False), primary_key=True),
Column("bidding_area_id", Integer, ForeignKey("bidding_areas.bidding_area_id"), primary_key=True),
Column("production_type_id", Integer, ForeignKey("production_types.production_type_id"), primary_key=True),
Column("value", Numeric, nullable=False),
)
metadata.create_all(engine) # creates the tables
</code>
</pre>
</section>
<section data-auto-animate>
<h2>Example (cont...)</h2>
<h4 data-id="code-title">Use of <em>SQL expression language</em></h4>
<pre data-id="code-animation">
<code class="python" data-trim data-line-numbers type="text/template">
# option 1: classical mapping (continues)
# Using the SQL expression language (low level interface)
from sqlalchemy import text
insert_stmt = bidding_areas_table.insert(bind=engine)
type(insert_stmt)
# Out: <class 'sqlalchemy.sql.dml.Insert'>
print(insert_stmt)
# Out: INSERT INTO bidding_areas (bidding_area_id, code, name) VALUES (?, ?, ?)
compiled_stmt = insert_stmt.compile()
print(compiled_stmt.params)
# Out: {'bidding_area_id': None, 'code': None, 'name': None}
insert_stmt.execute(bidding_area_id=1, code="NO1", name="Elspot NO1") # insert a single entry
# ... or a list of entries
insert_stmt.execute(
[
{"bidding_area_id": 2, "code": "NO2", "name": "Elspot NO2"},
{"bidding_area_id": 3, "code": "NO3", "name": "Elspot NO3"},
{"bidding_area_id": 4, "code": "NO4", "name": "Elspot NO4"},
{"bidding_area_id": 5, "code": "NO5", "name": "Elspot NO5"},
{"bidding_area_id": 6, "code": "NO6", "name": "Elspot NO6"},
]
)
metadata.bind = engine # no need to explicitly bind the engine from now on
select_stmt = bidding_areas_table.select(bidding_areas_table.c.bidding_area_id==2)
result = select_stmt.execute()
result.fetchall()
# Out: [(2, 'NO2', 'Elspot NO2')]
del_stmt = bidding_areas_table.delete()
del_stmt.execute(whereclause=text("name='Elspot NO6'"))
del_stmt.execute() # delete NO6
</code>
</pre>
</section>
<section data-auto-animate>
<h2>Example (cont...)</h2>
<h4 data-id="code-title">Use of <em>classical mapping</em></h4>
<pre data-id="code-animation">
<code class="python" data-trim data-line-numbers type="text/template">
# option 1: classical mapping (continues)
# Defining regular base classes and mapping them to the Table objects
from sqlalchemy.orm import mapper
class ProductionType:
def __init__(self, code, description):
self.code = code
self.description = description
def __str__(self):
return self.code
class BiddingArea:
def __init__(self, code, name):
self.code = code
self.name = name
def __str__(self):
return self.code
mapper(ProductionType, production_types_table)
mapper(BiddingArea, bidding_areas_table)
</code>
</pre>
</section>
<section data-auto-animate>
<h2>Example (cont...)</h2>
<h4 data-id="code-title">Use of <em>classical mapping</em></h4>
<pre data-id="code-animation">
<code class="python" data-trim data-line-numbers type="text/template">
from sqlalchemy.orm import relationship
class ProductionPlan:
def __init__(self, record_created_time, start_time, production_type, bidding_area, value):
self.record_created_time = record_created_time
self.start_time = start_time
self.production_type = production_type
self.bidding_area = bidding_area
self.value = value
def __str__(self):
return (
f"{self.record_created_time} {self.start_time} "
f"{self.production_type} {self.bidding_area} {self.value}"
)
mapper(
ProductionPlan,
production_plans_table,
properties = {
"production_type": relationship(ProductionType, backref="production_plans"),
"bidding_area": relationship(BiddingArea, backref="production_plans"),
},
)
</code>
</pre>
</section>
<section data-auto-animate>
<h2>Example (cont...)</h2>
<h4 data-id="code-title">Doing the same thing the easy way with <em>declarative mapping</em></h4>
<pre data-id="code-animation">
<code class="python" data-trim data-line-numbers type="text/template">
# option 2: declarative mapping
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class ProductionType(Base):
__tablename__ = "production_types"
production_type_id = Column(Integer, primary_key=True)
code = Column(String(3), nullable=False, unique=True)
description = Column(String)
def __init__(self, code, description):
self.code = code
self.description = description
def __str__(self):
return self.code
class BiddingArea(Base):
__tablename__ = "bidding_areas"
bidding_area_id = Column(Integer, primary_key=True)
code = Column(String(3), nullable=False, unique=True)
name = Column(String(32))
def __init__(self, code, name):
self.code = code
self.name = name
def __str__(self):
return self.code
</code>
</pre>
</section>
<section data-auto-animate>
<h2>Example (cont...)</h2>
<h4 data-id="code-title">Doing the same thing the easy way with <em>declarative mapping</em></h4>
<pre data-id="code-animation">
<code class="python" data-trim data-line-numbers type="text/template">
# option 2: declarative mapping (continues)
from sqlalchemy.orm import relationship, backref
class ProductionPlan(Base):
__tablename__ = "production_plans"
record_created_time = Column(DateTime(timezone=False), primary_key=True)
start_time = Column(DateTime(timezone=False), primary_key=True)
bidding_area_id = Column(Integer, ForeignKey("bidding_areas.bidding_area_id"), primary_key=True)
production_type_id = Column(Integer, ForeignKey("production_types.production_type_id"), primary_key=True)
value = Column(Numeric, nullable=False)
# defining relationships.
# the defined attributes will reference 'ProductionType' and 'BiddingArea' objects
production_type = relationship(ProductionType, backref=backref("production_plans"))
bidding_area = relationship(BiddingArea, backref=backref("production_plans"))
def __init__(self, record_created_time, start_time, production_type, bidding_area, value):
self.record_created_time = record_created_time
self.start_time = start_time
self.production_type = production_type # a 'ProductionType' object
self.bidding_area = bidding_area # a 'BiddingArea' object
self.value = value
def __str__(self):
return (
f"{self.record_created_time} {self.start_time} "
f"{self.production_type} {self.bidding_area} {self.value}"
)
Base.metadata.create_all(engine) # create tables
</code>
</pre>
</section>
<section data-auto-animate>
<h2>Example (cont...)</h2>
<h4 data-id="code-title">Creating instances</h4>
<pre data-id="code-animation">
<code class="python" data-trim data-line-numbers type="text/template">
# adding some data...
import datetime
import decimal
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine) # bound session
session = Session()
bidding_area1 = BiddingArea("NO1", "Elspot NO1")
session.add(bidding_area1)
session.add_all(
[
BiddingArea("NO2", "Elspot NO2"),
BiddingArea("NO3", "Elspot NO3"),
BiddingArea("NO4", "Elspot NO4"),
BiddingArea("NO5", "Elspot NO5"),
]
)
production_type_B37 = ProductionType("B37", "Thermal unspecified")
production_type_B30 = ProductionType("B30", "Wind unspecified")
session.add_all(
[
ProductionType("B19", "Wind Onshore"),
ProductionType("B10", "Hydro-electric pure pumped storage head installation"),
ProductionType("B11", "Hydro Run-of-river head installation"),
ProductionType("B12", "Hydro-electric storage head installation"),
ProductionType("A04", "Generation"),
production_type_B37,
production_type_B30,
]
)
</code>
</pre>
</section>
<section data-auto-animate>
<h2>Example (cont...)</h2>
<h4 data-id="code-title">Creating instances</h4>
<pre data-id="code-animation">
<code class="python" data-trim data-line-numbers type="text/template">
# adding some production plans...
session.add(
ProductionPlan(
datetime.datetime.now(),
datetime.datetime(2022, 11, 2, 1, 0),
production_type_B37,
bidding_area1,
decimal.Decimal("80.5"),
)
)
production_plan2 = ProductionPlan(
datetime.datetime.now(),
datetime.datetime(2022, 11, 2, 2, 0),
production_type_B37,
bidding_area1,
decimal.Decimal("90.5"),
)
session.add(production_plan2)
session.flush() # execute pending operations
session.commit() # execute and commit pending operations
production_plan2.value = decimal.Decimal("70.5")
production_plan2 in session
# Out: True
session.commit()
</code>
</pre>
</section>
<section data-auto-animate>
<h2>Example (cont...)</h2>
<h4 data-id="code-title">Queries</h4>
<pre data-id="code-animation">
<code class="python" data-trim data-line-numbers type="text/template">
import pandas as pd
session.query(ProductionPlan).order_by(ProductionPlan.start_time) # returns a Query instance
session.query(ProductionPlan).order_by(ProductionPlan.start_time).all() # returns an object-list
# return all production plans where start_time after 2022-09-01 00:00
session.query(ProductionPlan).filter(ProductionPlan.start_time > datetime.datetime(2022, 9, 1, 0, 0)).all()
# return production plans with value > 80
query = session.query(ProductionPlan).filter(ProductionPlan.value > 80).order_by(ProductionPlan.start_time)
query.count() # returns 1
production_plan = query.first() # returns the first object (element)
production_plan = query.one() # raises NoResultFound exception or MultipleResultsFound in case elements != 1
# generate Pandas DataFrame from a query or entire table
df = pd.read_sql_table("my_table", con=session.get_bind()) # or con=engine
df = pd.read_sql_query(query.statement, engine)
# return production plans with production type 'B37'
session.query(ProductionPlan).filter(
ProductionPlan.production_type_id == ProductionType.production_type_id
).filter(ProductionType.code == "B37").all()
session.query(ProductionPlan).join(ProductionType).filter(ProductionType.code == "B37").all()
session.query(ProductionPlan).filter(ProductionPlan.production_type == production_type_B37).all()
session.query(
ProductionPlan
).from_statement(
text(
"SELECT pp.* FROM production_plans pp, production_types pt "
"WHERE pp.production_type_id = pt.production_type_id AND pt.code=:code"
)
).params(code="B37").all()
</code>
</pre>
</section>
<section>
<h1>Q & A</h1>
</section>
</div>
</div>
<script src="dist/reveal.js"></script>
<script src="dist/plugin/zoom.js"></script>
<script src="dist/plugin/notes.js"></script>
<script src="dist/plugin/search.js"></script>
<script src="dist/plugin/markdown.js"></script>
<script src="dist/plugin/highlight.js"></script>
<script>
// Also available as an ES module, see: https://revealjs.com/initialization/
// import Reveal from 'reveal.js';
// import RevealZoom from 'reveal.js/plugin/zoom';
// import RevealNotes from 'reveal.js/plugin/notes';
// import RevealSearch from 'reveal.js/plugin/search';
// import RevealMarkdown from 'reveal.js/plugin/markdown';
// import RevealHighlight from 'reveal.js/plugin/highlight';
Reveal.initialize({
controls: true,
progress: true,
center: true,
hash: true,
// Learn about plugins: https://revealjs.com/plugins/
plugins: [RevealZoom, RevealNotes, RevealSearch, RevealMarkdown, RevealHighlight],
});
</script>
</body>
</html>
|
