summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorSimeon Simeonov2021-03-16 00:32:48 +0100
committerSimeon Simeonov2021-03-16 00:32:48 +0100
commitf5b94931274107ae70587a4c1ac0c3756b7f16ab (patch)
treec505b28fc11543dcd97bfa284c62a3df2172b5c0
parent9c6854707d2f3fd0866e82560ce95d1a87e2d794 (diff)
Move reveal.js/sqlalchemy.html to reveal.js/dataaccess.html and make a new reveal.js/sqlalchemy.html
Diffstat
-rwxr-xr-xreveal.js/dataaccess.html360
-rw-r--r--reveal.js/dist/theme/statnett.css297
-rwxr-xr-xreveal.js/sqlalchemy.html115
3 files changed, 723 insertions, 49 deletions
diff --git a/reveal.js/dataaccess.html b/reveal.js/dataaccess.html
new file mode 100755
index 0000000..4bafccd
--- /dev/null
+++ b/reveal.js/dataaccess.html
@@ -0,0 +1,360 @@
1<!doctype html>
2<html lang="en">
3 <head>
4 <meta charset="utf-8">
5 <title>SQLAlchemy &amp; Odin Data Access</title>
6 <meta name="author" content="Simeon Simeonov">
7 <meta name="apple-mobile-web-app-capable" content="yes">
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">
10
11 <link rel="stylesheet" href="dist/reset.css">
12 <link rel="stylesheet" href="dist/reveal.css">
13
14 <link rel="stylesheet" href="dist/theme/fifty.css" id="theme">
15
16 <!-- 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/zenburn.css" id="highlight-theme"> -->
19 </head>
20 <body>
21 <div class="reveal">
22
23 <!-- Any section element inside of this container is displayed as a slide -->
24 <div class="slides">
25
26 <section>
27 <h2>SQLAlchemy &amp; Odin Data Access</h2>
28 <h4>Fifty Data Science</h4>
29 </br>
30 <p><small>Simeon Simeonov</small></p>
31 </section>
32
33 <section>
34
35 <section id="fragments">
36 <h2>Agenda</h2>
37 </br>
38 <ul>
39 <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>Odin Data Access - Overview and examples</li></span>
42 <span class="fragment"><li>Q &amp; A</li></span>
43 </ul>
44 </section>
45
46 </section>
47
48 <section>
49 <h2>What is SQLAlchemy?</h2>
50 </br>
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>
52 <p><em>SQLAlchemy</em> includes RDBMS-independent SQL expression language and an <em>object-relational mapper (ORM)</em>.</p>
53 </section>
54
55 <section>
56 <h2>Why use SQLAlchemy?</h2>
57 </br>
58 <ul>
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>
61 <li><p>abstraction - no need to bother with complex JOINs</p></li>
62 <li><p>object-orientation - you work with objects and NOT tables and rows</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>
65 </ul>
66 </section>
67
68 <section>
69 <h2>Basic architecture</h2>
70 <p><em>SQLAlchemy</em> consists of several components, including the the <em>ORM</em>.</p>
71 <ul>
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>
74 <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>
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>
76 <li><em>ORM</em> - provides a convenient way to add database persistence to your Python objects withoutrequiring you to design your objects around the database, or the database around the objects (high-level interface)</li>
77 </ul>
78 <img src="images/sqlalchemy/sqla_arch.png"></img>
79 </section>
80
81 <section data-auto-animate>
82 <h2>Example</h2>
83 <p><em>SQLAlchemy</em> gives us the choice between <em>classical mapping</em> and the newer <em>declarative mapping</em></p>
84 <pre data-id="code-animation">
85 <code class="python" data-trim data-line-numbers type="text/template">
86 import sqlalchemy
87 sqlalchemy.__version__
88
89 from sqlalchemy import create_engine
90
91 # engine = create_engine("postgresql+psycopg2://user:zipassword@localhost/mydb" , echo=True)
92 # The string form of the URL is dialect+driver://user:password@host/dbname[?key=value..],
93 # where dialect is a database name such as mysql, oracle, postgresql, etc.,
94 # and driver the name of a DBAPI, such as psycopg2, pyodbc, cx_oracle
95 # The echo flag is a shortcut to setting up SQLAlchemy logging,
96 # which is accomplished via Python’s standard logging module.
97 # With it enabled, we’ll see all the generated SQL produced.
98 # engine = create_engine("sqlite:///library.db", echo=True)
99 engine = create_engine("sqlite:///:memory:", echo=True)
100
101 metadata = MetaData()
102
103 from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
104
105 authors_table = Table(
106 "authors",
107 metadata,
108 Column("id", Integer, primary_key=True),
109 Column("name", String),
110 ) # Column("name", String(50)) is possible
111
112 books_table = Table(
113 "books",
114 metadata,
115 Column("id", Integer, primary_key=True),
116 Column("title", String),
117 Column("description", String),
118 Column("author_id", ForeignKey('authors.id')),
119 )
120
121 metadata.create_all(engine) # creates the tables
122 </code>
123 </pre>
124 </section>
125
126 <section data-auto-animate>
127 <h2>Example (cont...)</h2>
128 <h4 data-id="code-title">Use of <em>SQL expression language</em></h4>
129 <pre data-id="code-animation">
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
137 compiled_stmt = insert_stmt.compile()
138 print(compiled_stmt.params)
139 # Out: {'id': None, 'name': None}
140
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
144 metadata.bind = engine # no need to explicitly bind the engine from now on
145 select_stmt = authors_table.select(authors_table.c.id==2)
146 result = select_stmt.execute()
147 result.fetchall()
148 # Out: [(1, u'Mr X')]
149
150 del_stmt = authors_table.delete()
151 del_stmt.execute(whereclause=text("name='Mr Y'"))
152 del_stmt.execute() # delete all
153 </code>
154 </pre>
155 </section>
156
157 <section data-auto-animate>
158 <h2>Example (cont...)</h2>
159 <h4 data-id="code-title">Use of <em>classical mapping</em></h4>
160 <pre data-id="code-animation">
161 <code class="python" data-trim data-line-numbers type="text/template">
162 from sqlalchemy.orm import mapper
163 from sqlalchemy.orm import relationship, backref
164
165 class Author:
166 def __init__(self, name):
167 self.name = name
168
169 def __str__(self):
170 return self.name
171
172
173 class Book:
174 def __init__(self, title, description, author):
175 self.title = title
176 self.description = description
177 self.author = author
178
179 def __str__(self):
180 return self.title
181
182 mapper(Book, books_table)
183 mapper(Author, authors_table, properties = {"books": relation(Book, backref="author")})
184 </code>
185 </pre>
186 </section>
187
188 <section data-auto-animate>
189 <h2>Example (cont...)</h2>
190 <h4 data-id="code-title">Doing the same thing the easy way with <em>declarative mapping</em></h4>
191 <pre data-id="code-animation">
192 <code class="python" data-trim data-line-numbers type="text/template">
193 from sqlalchemy.ext.declarative import declarative_base
194 from sqlalchemy.orm import relationship, backref
195
196 Base = declarative_base()
197
198 class Author(Base):
199 __tablename__ = "authors"
200
201 id = Column(Integer, primary_key=True)
202 name = Column(String)
203
204 def __init__(self, name):
205 self.name = name
206
207 def __str__(self):
208 return self.name
209
210
211 class Book(Base):
212 __tablename__ = "books" # self.__table__ will be available for our objects
213
214 id = Column(Integer, primary_key=True)
215 title = Column(String)
216 description = Column(String)
217 author_id = Column(Integer, ForeignKey("authors.id"))
218 author = relationship(Author, backref=backref("books", order_by=title))
219
220 def __init__(self, title, description, author):
221 self.title = title
222 self.description = description
223 self.author = author
224
225 def __str__(self):
226 return self.title
227
228 Base.metadata.create_all(engine)# create tables
229 </code>
230 </pre>
231 </section>
232
233 <section data-auto-animate>
234 <h2>Example (cont...)</h2>
235 <h4 data-id="code-title">Creating instances</h4>
236 <pre data-id="code-animation">
237 <code class="python" data-trim data-line-numbers type="text/template">
238 from sqlalchemy.orm import sessionmaker
239
240 Session = sessionmaker(bind=engine) # bound session
241 session = Session()
242
243 author_1 = Author("Richard Dawkins")
244 author_2 = Author("Matt Ridley")
245
246 book_1 = Book("The Red Queen", "A popular science book", author_2)
247 book_2 = Book("The Selfish Gene", "A popular science book", author_1)
248 book_3 = Book("The Blind Watchmaker", "The theory of evolutio", author_1) # typo
249
250 session.add(author_1)
251 session.add(author_2)
252 session.add(book_1)
253 session.add(book_2)
254 session.add(book_3) # or simply session.add_all([author_1, author_2, book_1, book_2, book_3])
255
256 # session.flush()
257 session.commit() # flushes (issues the statements and sends them to the RDBMS) and commits
258
259 book_3.description = "The theory of evolution" # update the object
260 book_3 in session # check whether the object is in the session
261 # Out: True
262
263 session.commit()
264 </code>
265 </pre>
266 </section>
267
268 <section data-auto-animate>
269 <h2>Example (cont...)</h2>
270 <h4 data-id="code-title">Queries</h4>
271 <pre data-id="code-animation">
272 <code class="python" data-trim data-line-numbers type="text/template">
273 session.query(Book).order_by(Book.id) # returns a Query instance with a .statement attribute
274 session.query(Book).order_by(Book.id).all() # returns an object-list
275
276 # return all book objects where title == "The Selfish Gene"
277 session.query(Book).filter(Book.title == "The Selfish Gene").order_by(Book.id).all()
278
279 # using LIKE
280 session.query(Book).filter(Book.title.like("The%")).order_by(Book.id).all()
281
282 query = session.query(Book).filter(Book.id == 9).order_by(Book.id)
283 query.count() # returns 0L
284 query.all() # returns an empty list
285 query.first() # returns None
286 query.one() # raises NoResultFound exception
287
288 query = session.query(Book).filter(Book.id == 1).order_by(Book.id)
289 book_1 = query.one()
290 book_1.description # returns "A popular science book"
291 book_1.author.books # returns a list of Book-objects representing all the books from the same author.
292
293 # get a list of all Book-instances where the author"s name is "Richard Dawkins"
294 session.query(Book).filter(Book.author_id == Author.id).filter(Author.name == "Richard Dawkins").all()
295 session.query(Book).join(Author).filter(Author.name == "Richard Dawkins").all()
296 session.query(Book).\
297 from_statement("SELECT b.* FROM books b, authors a WHERE b.author_id = a.id AND a.name=:name").\
298 params(name="Richard Dawkins").all()
299 </code>
300 </pre>
301 </section>
302
303 <section data-background="http://i.giphy.com/90F8aUepslB84.gif">
304 <h2>... simply amazing!</h2>
305 </section>
306
307 <section>
308 <h2>Odin Data Access</h2>
309 <p><a href="https://gitlab.fifty.eu/odin/data-science/odin-data-access">https://gitlab.fifty.eu/odin/data-science/odin-data-access</a></p>
310 <p>Provides the <em>odin_data_access</em> module with some of the following entities:</p>
311 <ul>
312 <li><em>session</em> module for creating Session objects in a Fifty environment via <em>get_session</em> and <em>get_session_from_env</em></li>
313 <li><em>DBBase</em> - a tiny base-class that can be used as a mixin for mapping</li>
314 <li><em>DBTableAccessor</em> - tiny wrapper for <em>Session</em> and <em>Table</em> as well a "facade" for <em>Query</em> and <em>pandas</em></li>
315 </ul>
316 <p>The <em>DBTableAccessor</em>:</p>
317 <ul>
318 <li>represents a single RDBMS-table that is introspected during instance initialization (<em>__init__</em>)</li>
319 <li>uses a mix of <em>ORM</em> and <em>SQL expression language</em> functionality</li>
320 <li>contins a mapper for mapping custom classes to its corresponding <em>Table</em></li>
321 <span class="fragment"><li>used by Batman and Chuck Norris (source: incomplete Google search)</li></span>
322 </ul>
323 </section>
324
325 <section>
326 <h1>Demo</h1>
327 <p>Now watch this well-rehearsed demo...</p>
328 </section>
329
330 <section>
331 <h1>Q &amp; A</h1>
332 </section>
333
334 </div>
335 </div>
336
337 <script src="dist/reveal.js"></script>
338 <script src="plugin/zoom/zoom.js"></script>
339 <script src="plugin/notes/notes.js"></script>
340 <script src="plugin/search/search.js"></script>
341 <script src="plugin/markdown/markdown.js"></script>
342 <script src="plugin/highlight/highlight.js"></script>
343 <script>
344
345 // Also available as an ES module, see:
346 // https://revealjs.netlify.app/initialization/
347 Reveal.initialize({
348 controls: true,
349 progress: true,
350 center: true,
351 hash: true,
352
353 // Learn about plugins: https://revealjs.netlify.app/plugins/
354 plugins: [ RevealZoom, RevealNotes, RevealSearch, RevealMarkdown, RevealHighlight ]
355 });
356
357 </script>
358
359 </body>
360</html>
diff --git a/reveal.js/dist/theme/statnett.css b/reveal.js/dist/theme/statnett.css
new file mode 100644
index 0000000..5f5ab00
--- /dev/null
+++ b/reveal.js/dist/theme/statnett.css
@@ -0,0 +1,297 @@
1/**
2 * A simple theme for reveal.js presentations, similar
3 * to the default theme. The accent color is brown.
4 *
5 * This theme is Copyright (C) 2012-2013 Owen Versteeg, http://owenversteeg.com - it is MIT licensed.
6 */
7.reveal a {
8 line-height: 1.3em; }
9
10section.has-dark-background, section.has-dark-background h1, section.has-dark-background h2, section.has-dark-background h3, section.has-dark-background h4, section.has-dark-background h5, section.has-dark-background h6 {
11 color: #fff; }
12
13/*********************************************
14 * GLOBAL STYLES
15 *********************************************/
16:root {
17 --background-color: #F0F1EB;
18 --main-font: Palatino Linotype, Book Antiqua, Palatino, FreeSerif, serif;
19 --main-font-size: 40px;
20 --main-color: #000;
21 --block-margin: 20px;
22 --heading-margin: 0 0 20px 0;
23 --heading-font: Palatino Linotype, Book Antiqua, Palatino, FreeSerif, serif;
24 --heading-color: #383D3D;
25 --heading-line-height: 1.2;
26 --heading-letter-spacing: normal;
27 --heading-text-transform: none;
28 --heading-text-shadow: none;
29 --heading-font-weight: normal;
30 --heading1-text-shadow: none;
31 --heading1-size: 3.77em;
32 --heading2-size: 2.11em;
33 --heading3-size: 1.55em;
34 --heading4-size: 1em;
35 --code-font: monospace;
36 --link-color: #51483D;
37 --link-color-hover: #8b7c69;
38 --selection-background-color: #26351C;
39 --selection-color: #fff; }
40
41.reveal-viewport {
42 background: #F0F1EB;
43 background-color: #F0F1EB; }
44
45.reveal {
46 font-family: "Palatino Linotype", "Book Antiqua", Palatino, FreeSerif, serif;
47 font-size: 22px;
48 font-weight: normal;
49 color: #000; }
50
51.reveal ::selection {
52 color: #fff;
53 background: #26351C;
54 text-shadow: none; }
55
56.reveal ::-moz-selection {
57 color: #fff;
58 background: #26351C;
59 text-shadow: none; }
60
61.reveal .slides section,
62.reveal .slides section > section {
63 line-height: 1.3;
64 font-weight: inherit; }
65
66/*********************************************
67 * HEADERS
68 *********************************************/
69.reveal h1,
70.reveal h2,
71.reveal h3,
72.reveal h4,
73.reveal h5,
74.reveal h6 {
75 margin: 0 0 20px 0;
76 color: #383D3D;
77 font-family: "Palatino Linotype", "Book Antiqua", Palatino, FreeSerif, serif;
78 font-weight: normal;
79 line-height: 1.2;
80 letter-spacing: normal;
81 text-transform: none;
82 text-shadow: none;
83 word-wrap: break-word; }
84
85.reveal h1 {
86 font-size: 3.77em; }
87
88.reveal h2 {
89 font-size: 2.11em; }
90
91.reveal h3 {
92 font-size: 1.55em; }
93
94.reveal h4 {
95 font-size: 1em; }
96
97.reveal h1 {
98 text-shadow: none; }
99
100/*********************************************
101 * OTHER
102 *********************************************/
103.reveal p {
104 margin: 20px 0;
105 line-height: 1.3; }
106
107/* Remove trailing margins after titles */
108.reveal h1:last-child,
109.reveal h2:last-child,
110.reveal h3:last-child,
111.reveal h4:last-child,
112.reveal h5:last-child,
113.reveal h6:last-child {
114 margin-bottom: 0; }
115
116/* Ensure certain elements are never larger than the slide itself */
117.reveal img,
118.reveal video,
119.reveal iframe {
120 max-width: 96%;
121 max-height: 96%; }
122
123.reveal strong,
124.reveal b {
125 font-weight: bold; }
126
127.reveal em {
128 font-style: italic; }
129
130.reveal ol,
131.reveal dl,
132.reveal ul {
133 display: inline-block;
134 text-align: left;
135 margin: 0 0 0 1em; }
136
137.reveal ol {
138 list-style-type: decimal; }
139
140.reveal ul {
141 list-style-type: disc; }
142
143.reveal ul ul {
144 list-style-type: square; }
145
146.reveal ul ul ul {
147 list-style-type: circle; }
148
149.reveal ul ul,
150.reveal ul ol,
151.reveal ol ol,
152.reveal ol ul {
153 display: block;
154 margin-left: 40px; }
155
156.reveal dt {
157 font-weight: bold; }
158
159.reveal dd {
160 margin-left: 40px; }
161
162.reveal blockquote {
163 display: block;
164 position: relative;
165 width: 70%;
166 margin: 20px auto;
167 padding: 5px;
168 font-style: italic;
169 background: rgba(255, 255, 255, 0.05);
170 box-shadow: 0px 0px 2px rgba(0, 0, 0, 0.2); }
171
172.reveal blockquote p:first-child,
173.reveal blockquote p:last-child {
174 display: inline-block; }
175
176.reveal q {
177 font-style: italic; }
178
179.reveal pre {
180 display: block;
181 position: relative;
182 width: 95%;
183 margin: 20px auto;
184 text-align: left;
185 font-size: 0.60em;
186 font-family: monospace;
187 line-height: 1.1em;
188 word-wrap: break-word; }
189 /* box-shadow: 0px 5px 15px rgba(0, 0, 0, 0.15); } */
190
191.reveal code {
192 font-family: monospace;
193 text-transform: none; }
194
195.reveal pre code {
196 display: block;
197 padding: 4px;
198 overflow: auto;
199 max-height: 520px;
200 word-wrap: normal; }
201
202.reveal table {
203 margin: auto;
204 border-collapse: collapse;
205 border-spacing: 0; }
206
207.reveal table th {
208 font-weight: bold; }
209
210.reveal table th,
211.reveal table td {
212 text-align: left;
213 padding: 0.2em 0.5em 0.2em 0.5em;
214 border-bottom: 1px solid; }
215
216.reveal table th[align="center"],
217.reveal table td[align="center"] {
218 text-align: center; }
219
220.reveal table th[align="right"],
221.reveal table td[align="right"] {
222 text-align: right; }
223
224.reveal table tbody tr:last-child th,
225.reveal table tbody tr:last-child td {
226 border-bottom: none; }
227
228.reveal sup {
229 vertical-align: super;
230 font-size: smaller; }
231
232.reveal sub {
233 vertical-align: sub;
234 font-size: smaller; }
235
236.reveal small {
237 display: inline-block;
238 font-size: 0.6em;
239 line-height: 1.2em;
240 vertical-align: top; }
241
242.reveal small * {
243 vertical-align: top; }
244
245.reveal img {
246 margin: 20px 0; }
247
248/*********************************************
249 * LINKS
250 *********************************************/
251.reveal a {
252 color: #51483D;
253 text-decoration: none;
254 transition: color .15s ease; }
255
256.reveal a:hover {
257 color: #8b7c69;
258 text-shadow: none;
259 border: none; }
260
261.reveal .roll span:after {
262 color: #fff;
263 background: #25211c; }
264
265/*********************************************
266 * Frame helper
267 *********************************************/
268.reveal .r-frame {
269 border: 4px solid #000;
270 box-shadow: 0 0 10px rgba(0, 0, 0, 0.15); }
271
272.reveal a .r-frame {
273 transition: all .15s linear; }
274
275.reveal a:hover .r-frame {
276 border-color: #51483D;
277 box-shadow: 0 0 20px rgba(0, 0, 0, 0.55); }
278
279/*********************************************
280 * NAVIGATION CONTROLS
281 *********************************************/
282.reveal .controls {
283 color: #51483D; }
284
285/*********************************************
286 * PROGRESS BAR
287 *********************************************/
288.reveal .progress {
289 background: rgba(0, 0, 0, 0.2);
290 color: #51483D; }
291
292/*********************************************
293 * PRINT BACKGROUND
294 *********************************************/
295@media print {
296 .backgrounds {
297 background-color: #F0F1EB; } }
diff --git a/reveal.js/sqlalchemy.html b/reveal.js/sqlalchemy.html
index 4bafccd..578fc13 100755
--- a/reveal.js/sqlalchemy.html
+++ b/reveal.js/sqlalchemy.html
@@ -2,7 +2,7 @@
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 &amp; Odin Data Access</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">
@@ -11,7 +11,7 @@
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/fifty.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">
@@ -24,8 +24,8 @@
24 <div class="slides"> 24 <div class="slides">
25 25
26 <section> 26 <section>
27 <h2>SQLAlchemy &amp; Odin Data Access</h2> 27 <h2>SQLAlchemy</h2>
28 <h4>Fifty Data Science</h4> 28 <h4>Data Science @ Statnett</h4>
29 </br> 29 </br>
30 <p><small>Simeon Simeonov</small></p> 30 <p><small>Simeon Simeonov</small></p>
31 </section> 31 </section>
@@ -38,7 +38,6 @@
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>Odin Data Access - Overview and examples</li></span>
42 <span class="fragment"><li>Q &amp; A</li></span> 41 <span class="fragment"><li>Q &amp; A</li></span>
43 </ul> 42 </ul>
44 </section> 43 </section>
@@ -56,18 +55,20 @@
56 <h2>Why use SQLAlchemy?</h2> 55 <h2>Why use SQLAlchemy?</h2>
57 </br> 56 </br>
58 <ul> 57 <ul>
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 and NOT tables and rows</p></li> 62 <li><p>object-orientation - you work with objects and NOT 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 <li><p>0.0125 karma points for each MB transferred (2021)</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 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>
@@ -98,24 +99,24 @@
98 # engine = create_engine("sqlite:///library.db", echo=True) 99 # engine = create_engine("sqlite:///library.db", echo=True)
99 engine = create_engine("sqlite:///:memory:", echo=True) 100 engine = create_engine("sqlite:///:memory:", echo=True)
100 101
101 metadata = MetaData() 102 from sqlalchemy import Column, ForeignKey, Integer, String, Table
102 103
103 from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey 104 metadata = MetaData()
104 105
105 authors_table = Table( 106 authors_table = Table(
106 "authors", 107 "authors",
107 metadata, 108 metadata,
108 Column("id", Integer, primary_key=True), 109 Column("author_id", Integer, primary_key=True),
109 Column("name", String), 110 Column("name", String),
110 ) # Column("name", String(50)) is possible 111 ) # Column("name", String(50)) is possible
111 112
112 books_table = Table( 113 books_table = Table(
113 "books", 114 "books",
114 metadata, 115 metadata,
115 Column("id", Integer, primary_key=True), 116 Column("book_id", Integer, primary_key=True),
116 Column("title", String), 117 Column("title", String),
117 Column("description", String), 118 Column("description", String),
118 Column("author_id", ForeignKey('authors.id')), 119 Column("author_id", ForeignKey('authors.author_id')),
119 ) 120 )
120 121
121 metadata.create_all(engine) # creates the tables 122 metadata.create_all(engine) # creates the tables
@@ -139,13 +140,13 @@
139 # Out: {'id': None, 'name': None} 140 # Out: {'id': None, 'name': None}
140 141
141 insert_stmt.execute(name="Alexandre Dumas") # insert a single entry 142 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 insert_stmt.execute([{"name": "Mr X"}, {"name": "Mr Y"}]) # a list of entries
143 144
144 metadata.bind = engine # no need to explicitly bind the engine from now on 145 metadata.bind = engine # no need to explicitly bind the engine from now on
145 select_stmt = authors_table.select(authors_table.c.id==2) 146 select_stmt = authors_table.select(authors_table.c.id==2)
146 result = select_stmt.execute() 147 result = select_stmt.execute()
147 result.fetchall() 148 result.fetchall()
148 # Out: [(1, u'Mr X')] 149 # Out: [(1, 'Mr X')]
149 150
150 del_stmt = authors_table.delete() 151 del_stmt = authors_table.delete()
151 del_stmt.execute(whereclause=text("name='Mr Y'")) 152 del_stmt.execute(whereclause=text("name='Mr Y'"))
@@ -159,8 +160,7 @@
159 <h4 data-id="code-title">Use of <em>classical mapping</em></h4> 160 <h4 data-id="code-title">Use of <em>classical mapping</em></h4>
160 <pre data-id="code-animation"> 161 <pre data-id="code-animation">
161 <code class="python" data-trim data-line-numbers type="text/template"> 162 <code class="python" data-trim data-line-numbers type="text/template">
162 from sqlalchemy.orm import mapper 163 from sqlalchemy.orm import backref, mapper, relation
163 from sqlalchemy.orm import relationship, backref
164 164
165 class Author: 165 class Author:
166 def __init__(self, name): 166 def __init__(self, name):
@@ -198,7 +198,7 @@
198 class Author(Base): 198 class Author(Base):
199 __tablename__ = "authors" 199 __tablename__ = "authors"
200 200
201 id = Column(Integer, primary_key=True) 201 author_id = Column(Integer, primary_key=True)
202 name = Column(String) 202 name = Column(String)
203 203
204 def __init__(self, name): 204 def __init__(self, name):
@@ -211,10 +211,10 @@
211 class Book(Base): 211 class Book(Base):
212 __tablename__ = "books" # self.__table__ will be available for our objects 212 __tablename__ = "books" # self.__table__ will be available for our objects
213 213
214 id = Column(Integer, primary_key=True) 214 book_id = Column(Integer, primary_key=True)
215 title = Column(String) 215 title = Column(String)
216 description = Column(String) 216 description = Column(String)
217 author_id = Column(Integer, ForeignKey("authors.id")) 217 author_id = Column(Integer, ForeignKey("authors.author_id"))
218 author = relationship(Author, backref=backref("books", order_by=title)) 218 author = relationship(Author, backref=backref("books", order_by=title))
219 219
220 def __init__(self, title, description, author): 220 def __init__(self, title, description, author):
@@ -225,7 +225,7 @@
225 def __str__(self): 225 def __str__(self):
226 return self.title 226 return self.title
227 227
228 Base.metadata.create_all(engine)# create tables 228 Base.metadata.create_all(engine) # create tables
229 </code> 229 </code>
230 </pre> 230 </pre>
231 </section> 231 </section>
@@ -251,7 +251,8 @@
251 session.add(author_2) 251 session.add(author_2)
252 session.add(book_1) 252 session.add(book_1)
253 session.add(book_2) 253 session.add(book_2)
254 session.add(book_3) # or simply session.add_all([author_1, author_2, book_1, book_2, book_3]) 254 session.add(book_3)
255 # or simply session.add_all([author_1, author_2, book_1, book_2, book_3])
255 256
256 # session.flush() 257 # session.flush()
257 session.commit() # flushes (issues the statements and sends them to the RDBMS) and commits 258 session.commit() # flushes (issues the statements and sends them to the RDBMS) and commits
@@ -270,61 +271,77 @@
270 <h4 data-id="code-title">Queries</h4> 271 <h4 data-id="code-title">Queries</h4>
271 <pre data-id="code-animation"> 272 <pre data-id="code-animation">
272 <code class="python" data-trim data-line-numbers type="text/template"> 273 <code class="python" data-trim data-line-numbers type="text/template">
273 session.query(Book).order_by(Book.id) # returns a Query instance with a .statement attribute 274 session.query(Book).order_by(Book.book_id) # returns a Query instance with a .statement attribute
274 session.query(Book).order_by(Book.id).all() # returns an object-list 275 session.query(Book).order_by(Book.book_id).all() # returns an object-list
275 276
276 # return all book objects where title == "The Selfish Gene" 277 # return all book objects where title == "The Selfish Gene"
277 session.query(Book).filter(Book.title == "The Selfish Gene").order_by(Book.id).all() 278 session.query(Book).filter(Book.title == "The Selfish Gene").order_by(Book.book_id).all()
278 279
279 # using LIKE 280 # using LIKE
280 session.query(Book).filter(Book.title.like("The%")).order_by(Book.id).all() 281 session.query(Book).filter(Book.title.like("The%")).order_by(Book.book_id).all()
281 282
282 query = session.query(Book).filter(Book.id == 9).order_by(Book.id) 283 query = session.query(Book).filter(Book.book_id == 9).order_by(Book.book_id)
283 query.count() # returns 0L 284 query.count() # returns 0
284 query.all() # returns an empty list 285 query.all() # returns an empty list
285 query.first() # returns None 286 query.first() # returns None
286 query.one() # raises NoResultFound exception 287 query.one() # raises NoResultFound exception
287 288
288 query = session.query(Book).filter(Book.id == 1).order_by(Book.id) 289 query = session.query(Book).filter(Book.book_id == 1).order_by(Book.book_id)
289 book_1 = query.one() 290 book_1 = query.one()
290 book_1.description # returns "A popular science book" 291 book_1.description # returns "A popular science book"
291 book_1.author.books # returns a list of Book-objects representing all the books from the same author. 292 book_1.author.books # returns a list of Book-objects representing all the books from the same author.
292 293
293 # get a list of all Book-instances where the author"s name is "Richard Dawkins" 294 # get a list of all Book-instances where the author"s name is "Richard Dawkins"
294 session.query(Book).filter(Book.author_id == Author.id).filter(Author.name == "Richard Dawkins").all() 295 session.query(Book).filter(Book.author_id == Author.author_id).filter(Author.name == "Richard Dawkins").all()
295 session.query(Book).join(Author).filter(Author.name == "Richard Dawkins").all() 296 session.query(Book).join(Author).filter(Author.name == "Richard Dawkins").all()
296 session.query(Book).\ 297 session.query(Book).\
297 from_statement("SELECT b.* FROM books b, authors a WHERE b.author_id = a.id AND a.name=:name").\ 298 from_statement("SELECT b.* FROM books b, authors a WHERE b.author_id = a.author_id AND a.name=:name").\
298 params(name="Richard Dawkins").all() 299 params(name="Richard Dawkins").all()
299 </code> 300 </code>
300 </pre> 301 </pre>
301 </section> 302 </section>
302 303
303 <section data-background="http://i.giphy.com/90F8aUepslB84.gif"> 304 <section data-background="http://i.giphy.com/90F8aUepslB84.gif">
304 <h2>... simply amazing!</h2> 305 <h2>... there is more???</h2>
305 </section> 306 </section>
306 307
307 <section> 308 <section>
308 <h2>Odin Data Access</h2> 309 <h2>Some nice features</h2>
309 <p><a href="https://gitlab.fifty.eu/odin/data-science/odin-data-access">https://gitlab.fifty.eu/odin/data-science/odin-data-access</a></p> 310 <pre data-id="code-animation">
310 <p>Provides the <em>odin_data_access</em> module with some of the following entities:</p> 311 <code class="python" data-trim data-line-numbers type="text/template">
311 <ul> 312 import pandas as pd
312 <li><em>session</em> module for creating Session objects in a Fifty environment via <em>get_session</em> and <em>get_session_from_env</em></li>
313 <li><em>DBBase</em> - a tiny base-class that can be used as a mixin for mapping</li>
314 <li><em>DBTableAccessor</em> - tiny wrapper for <em>Session</em> and <em>Table</em> as well a "facade" for <em>Query</em> and <em>pandas</em></li>
315 </ul>
316 <p>The <em>DBTableAccessor</em>:</p>
317 <ul>
318 <li>represents a single RDBMS-table that is introspected during instance initialization (<em>__init__</em>)</li>
319 <li>uses a mix of <em>ORM</em> and <em>SQL expression language</em> functionality</li>
320 <li>contins a mapper for mapping custom classes to its corresponding <em>Table</em></li>
321 <span class="fragment"><li>used by Batman and Chuck Norris (source: incomplete Google search)</li></span>
322 </ul>
323 </section>
324 313
325 <section> 314 from sqlalchemy import func
326 <h1>Demo</h1> 315
327 <p>Now watch this well-rehearsed demo...</p> 316 class Book(Base):
317 # ...
318 author = relationship(
319 Author, backref=backref("books", lazy="dynamic", order_by=title)
320 )
321 # ...
322
323 @hybrid_property
324 def newly_arrived(self):
325 return self.book_id > 2
326
327 @newly_arrived.expression
328 def newly_arrived(cls):
329 return cls.book_id > 2
330 # return func.abs(cls.book_id) > 2
331
332 # .books is now a Query object
333 query = author_obj.books.filter(Book.title.ilike("%red%"))
334
335 session.query(Book).filter(Book.newly_arrived.is_(True)).all()
336 # Out: [<__main__.Book at 0x7f132bdf0130>]
337 # WHERE (abs(books.book_id) > ?) IS 1 ... in the case of func.abs
338
339 df = pd.read_sql_table("my_table", con=session.get_bind()) # or con=engine
340
341 df = pd.read_sql_query(query.statement, engine)
342
343 </code>
344 </pre>
328 </section> 345 </section>
329 346
330 <section> 347 <section>
generated by cgit v1.3 (git 2.53.0) at 2026-09-25 09:44:11 +0000