summaryrefslogtreecommitdiff
path: root/reveal.js/sqlalchemy.html
diff options
context:
space:
mode:
authorSimeon Simeonov2021-02-25 12:04:05 +0100
committerSimeon Simeonov2021-02-25 12:04:05 +0100
commitdecc91569d1971c0cac0fb1c3a5f556a5b53cd7d (patch)
treee4c30f5d319d0057db199ea7d04c49ac8b5549af /reveal.js/sqlalchemy.html
parent97e8b1c6ed35e84097f035a056d1c938b9942d11 (diff)
Add reveal.js/sqlalchemy.html and reveal.js/dist/theme/fifty.css
Diffstat (limited to 'reveal.js/sqlalchemy.html')
-rwxr-xr-xreveal.js/sqlalchemy.html361
1 files changed, 361 insertions, 0 deletions
diff --git a/reveal.js/sqlalchemy.html b/reveal.js/sqlalchemy.html
new file mode 100755
index 0000000..956538f
--- /dev/null
+++ b/reveal.js/sqlalchemy.html
@@ -0,0 +1,361 @@
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 SQL expression language and the ORM.</p>
71 <p>In order to enable these components, <em>SQLAlchemy</em> also provides an <em>Engine</em> class and <em>MetaData</em> class.</p>
72 <ul>
73 <li><em>Engine</em>- manages the <em>SQLAlchemy</em> connection pool and the database-independent SQL dialect layer</li>
74 <li><em>MetaData</em> - used to collect and organize information about your table layout (schema)</li>
75 <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>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>
77 <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>
78 </ul>
79 <img src="images/sqlalchemy/sqla_arch.png"></img>
80 </section>
81
82 <section data-auto-animate>
83 <h2>Example</h2>
84 <p><em>SQLAlchemy</em> gives us the choice between <em>classical mapping</em> and the newer <em>declarative mapping</em></p>
85 <pre data-id="code-animation">
86 <code class="python" data-trim data-line-numbers type="text/template">
87 import sqlalchemy
88 sqlalchemy.__version__
89
90 from sqlalchemy import create_engine
91
92 # engine = create_engine("postgresql+psycopg2://user:zipassword@localhost/mydb" , echo=True)
93 # The string form of the URL is dialect+driver://user:password@host/dbname[?key=value..],
94 # where dialect is a database name such as mysql, oracle, postgresql, etc.,
95 # and driver the name of a DBAPI, such as psycopg2, pyodbc, cx_oracle
96 # The echo flag is a shortcut to setting up SQLAlchemy logging,
97 # which is accomplished via Python’s standard logging module.
98 # With it enabled, we’ll see all the generated SQL produced.
99 # engine = create_engine("sqlite:///library.db", echo=True)
100 engine = create_engine("sqlite:///:memory:", echo=True)
101
102 metadata = MetaData()
103
104 from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
105
106 authors_table = Table(
107 "authors",
108 metadata,
109 Column("id", Integer, primary_key=True),
110 Column("name", String),
111 ) # Column("name", String(50)) is possible
112
113 books_table = Table(
114 "books",
115 metadata,
116 Column("id", Integer, primary_key=True),
117 Column("title", String),
118 Column("description", String),
119 Column("author_id", ForeignKey('authors.id')),
120 )
121
122 metadata.create_all(engine) # creates the tables
123 </code>
124 </pre>
125 </section>
126
127 <section data-auto-animate>
128 <h2>Example (cont...)</h2>
129 <h4 data-id="code-title">Use of <em>SQL expression language</em></h4>
130 <pre data-id="code-animation">
131 <code class="python" data-trim data-line-numbers type="text/template">
132 insert_stmt = authors_table.insert(bind=engine)
133 type(insert_stmt)
134 # Out: &lt;class 'sqlalchemy.sql.expression.Insert'&gt;
135 print(insert_stmt)
136 # Out: INSERT INTO authors (id, name) VALUES (:id,:name)
137
138 compiled_stmt = insert_stmt.compile()
139 print(compiled_stmt.params)
140 # Out: {'id': None, 'name': None}
141
142 insert_stmt.execute(name="Alexandre Dumas") # insert a single entry
143 insert_stmt.execute([{"name": 'Mr X'},{'name': 'Mr Y'}]) # a list of entries
144
145 metadata.bind = engine # no need to explicitly bind the engine from now on
146 select_stmt = authors_table.select(authors_table.c.id==2)
147 result = select_stmt.execute()
148 result.fetchall()
149 # Out: [(1, u'Mr X')]
150
151 del_stmt = authors_table.delete()
152 del_stmt.execute(whereclause=text("name='Mr Y'"))
153 del_stmt.execute() # delete all
154 </code>
155 </pre>
156 </section>
157
158 <section data-auto-animate>
159 <h2>Example (cont...)</h2>
160 <h4 data-id="code-title">Use of <em>classical mapping</em></h4>
161 <pre data-id="code-animation">
162 <code class="python" data-trim data-line-numbers type="text/template">
163 from sqlalchemy.orm import mapper
164 from sqlalchemy.orm import relationship, backref
165
166 class Author:
167 def __init__(self, name):
168 self.name = name
169
170 def __str__(self):
171 return self.name
172
173
174 class Book:
175 def __init__(self, title, description, author):
176 self.title = title
177 self.description = description
178 self.author = author
179
180 def __str__(self):
181 return self.title
182
183 mapper(Book, books_table)
184 mapper(Author, authors_table, properties = {"books": relation(Book, backref="author")})
185 </code>
186 </pre>
187 </section>
188
189 <section data-auto-animate>
190 <h2>Example (cont...)</h2>
191 <h4 data-id="code-title">Doing the same thing the easy way with <em>declarative mapping</em></h4>
192 <pre data-id="code-animation">
193 <code class="python" data-trim data-line-numbers type="text/template">
194 from sqlalchemy.ext.declarative import declarative_base
195 from sqlalchemy.orm import relationship, backref
196
197 Base = declarative_base()
198
199 class Author(Base):
200 __tablename__ = "authors"
201
202 id = Column(Integer, primary_key=True)
203 name = Column(String)
204
205 def __init__(self, name):
206 self.name = name
207
208 def __str__(self):
209 return self.name
210
211
212 class Book(Base):
213 __tablename__ = "books" # self.__table__ will be available for our objects
214
215 id = Column(Integer, primary_key=True)
216 title = Column(String)
217 description = Column(String)
218 author_id = Column(Integer, ForeignKey("authors.id"))
219 author = relationship(Author, backref=backref("books", order_by=title))
220
221 def __init__(self, title, description, author):
222 self.title = title
223 self.description = description
224 self.author = author
225
226 def __str__(self):
227 return self.title
228
229 Base.metadata.create_all(engine)# create tables
230 </code>
231 </pre>
232 </section>
233
234 <section data-auto-animate>
235 <h2>Example (cont...)</h2>
236 <h4 data-id="code-title">Creating instances</h4>
237 <pre data-id="code-animation">
238 <code class="python" data-trim data-line-numbers type="text/template">
239 from sqlalchemy.orm import sessionmaker
240
241 Session = sessionmaker(bind=engine) # bound session
242 session = Session()
243
244 author_1 = Author("Richard Dawkins")
245 author_2 = Author("Matt Ridley")
246
247 book_1 = Book("The Red Queen", "A popular science book", author_2)
248 book_2 = Book("The Selfish Gene", "A popular science book", author_1)
249 book_3 = Book("The Blind Watchmaker", "The theory of evolutio", author_1) # typo
250
251 session.add(author_1)
252 session.add(author_2)
253 session.add(book_1)
254 session.add(book_2)
255 session.add(book_3) # or simply session.add_all([author_1, author_2, book_1, book_2, book_3])
256
257 # session.flush()
258 session.commit() # flushes (issues the statements and sends them to the RDBMS) and commits
259
260 book_3.description = "The theory of evolution" # update the object
261 book_3 in session # check whether the object is in the session
262 # Out: True
263
264 session.commit()
265 </code>
266 </pre>
267 </section>
268
269 <section data-auto-animate>
270 <h2>Example (cont...)</h2>
271 <h4 data-id="code-title">Queries</h4>
272 <pre data-id="code-animation">
273 <code class="python" data-trim data-line-numbers type="text/template">
274 session.query(Book).order_by(Book.id) # returns a Query instance with a .statement attribute
275 session.query(Book).order_by(Book.id).all() # returns an object-list
276
277 # return all book objects where title == "The Selfish Gene"
278 session.query(Book).filter(Book.title == "The Selfish Gene").order_by(Book.id).all()
279
280 # using LIKE
281 session.query(Book).filter(Book.title.like("The%")).order_by(Book.id).all()
282
283 query = session.query(Book).filter(Book.id == 9).order_by(Book.id)
284 query.count() # returns 0L
285 query.all() # returns an empty list
286 query.first() # returns None
287 query.one() # raises NoResultFound exception
288
289 query = session.query(Book).filter(Book.id == 1).order_by(Book.id)
290 book_1 = query.one()
291 book_1.description # returns "A popular science book"
292 book_1.author.books # returns a list of Book-objects representing all the books from the same author.
293
294 # get a list of all Book-instances where the author"s name is "Richard Dawkins"
295 session.query(Book).filter(Book.author_id == Author.id).filter(Author.name == "Richard Dawkins").all()
296 session.query(Book).join(Author).filter(Author.name == "Richard Dawkins").all()
297 session.query(Book).\
298 from_statement("SELECT b.* FROM books b, authors a WHERE b.author_id = a.id AND a.name=:name").\
299 params(name="Richard Dawkins").all()
300 </code>
301 </pre>
302 </section>
303
304 <section data-background="http://i.giphy.com/90F8aUepslB84.gif">
305 <h2>... simply amazing!</h2>
306 </section>
307
308 <section>
309 <h2>Odin Data Access</h2>
310 <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>
311 <p>Provides the <em>odin_data_access</em> module with some of the following entities:</p>
312 <ul>
313 <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>
314 <li><em>DBBase</em> - a tiny base-class that can be used as a mixin for mapping</li>
315 <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>
316 </ul>
317 <p>The <em>DBTableAccessor</em>:</p>
318 <ul>
319 <li>represents a single RDBMS-table that is introspected during instance initialization (<em>__init__</em>)</li>
320 <li>uses a mix of <em>ORM</em> and <em>SQL expression language</em> functionality</li>
321 <li>contins a mapper for mapping custom classes to its corresponding <em>Table</em></li>
322 <span class="fragment"><li>used by Batman and Chuck Norris (source: incomplete Google search)</li></span>
323 </ul>
324 </section>
325
326 <section>
327 <h1>Demo</h1>
328 <p>Now watch this well-rehearsed demo...</p>
329 </section>
330
331 <section>
332 <h1>Q &amp; A</h1>
333 </section>
334
335 </div>
336 </div>
337
338 <script src="dist/reveal.js"></script>
339 <script src="plugin/zoom/zoom.js"></script>
340 <script src="plugin/notes/notes.js"></script>
341 <script src="plugin/search/search.js"></script>
342 <script src="plugin/markdown/markdown.js"></script>
343 <script src="plugin/highlight/highlight.js"></script>
344 <script>
345
346 // Also available as an ES module, see:
347 // https://revealjs.netlify.app/initialization/
348 Reveal.initialize({
349 controls: true,
350 progress: true,
351 center: true,
352 hash: true,
353
354 // Learn about plugins: https://revealjs.netlify.app/plugins/
355 plugins: [ RevealZoom, RevealNotes, RevealSearch, RevealMarkdown, RevealHighlight ]
356 });
357
358 </script>
359
360 </body>
361</html>
generated by cgit v1.3 (git 2.53.0) at 2026-09-26 12:26:49 +0000