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