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