summaryrefslogtreecommitdiff
path: root/reveal.js/sqlalchemy.html
diff options
context:
space:
mode:
Diffstat (limited to 'reveal.js/sqlalchemy.html')
-rwxr-xr-xreveal.js/sqlalchemy.html115
1 files changed, 66 insertions, 49 deletions
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-26 13:25:45 +0000