summaryrefslogtreecommitdiff
path: root/reveal.js/dataaccess.html
diff options
context:
space:
mode:
Diffstat (limited to 'reveal.js/dataaccess.html')
-rwxr-xr-xreveal.js/dataaccess.html360
1 files changed, 0 insertions, 360 deletions
diff --git a/reveal.js/dataaccess.html b/reveal.js/dataaccess.html
deleted file mode 100755
index 4bafccd..0000000
--- a/reveal.js/dataaccess.html
+++ /dev/null
@@ -1,360 +0,0 @@
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>
generated by cgit v1.3 (git 2.53.0) at 2026-09-25 09:43:42 +0000