1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
|
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>SQLAlchemy</title>
<meta name="author" content="Simeon Simeonov">
<meta name="apple-mobile-web-app-capable" content="yes">
<meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" href="dist/reset.css">
<link rel="stylesheet" href="dist/reveal.css">
<link rel="stylesheet" href="dist/theme/statnett.css" id="theme">
<!-- Theme used for syntax highlighting of code -->
<link rel="stylesheet" href="plugin/highlight/monokai.css" id="highlight-theme">
<!-- <link rel="stylesheet" href="plugin/highlight/zenburn.css" id="highlight-theme"> -->
</head>
<body>
<div class="reveal">
<!-- Any section element inside of this container is displayed as a slide -->
<div class="slides">
<section>
<h2>SQLAlchemy</h2>
<h4>Data Science @ Statnett</h4>
</br>
<p><small>Simeon Simeonov</small></p>
</section>
<section>
<section id="fragments">
<h2>Agenda</h2>
</br>
<ul>
<span class="fragment"><li>SQLAlchemy - Design & overview</li></span>
<span class="fragment"><li>SQLAlchemy - A small practical example</li></span>
<span class="fragment"><li>Q & A</li></span>
</ul>
</section>
</section>
<section>
<h2>What is SQLAlchemy?</h2>
</br>
<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>
<p><em>SQLAlchemy</em> includes RDBMS-independent <em>SQL expression language</em> and an <em>object-relational mapper (ORM)</em>.</p>
</section>
<section>
<h2>Why use SQLAlchemy?</h2>
</br>
<ul>
<li><p>free software - free as in "freedom" (MIT licensed)</p></li>
<li><p>portability - the programming interface is independent of the type of RDBMS and connector used</p></li>
<li><p>security - no more SQL injections</p></li>
<li><p>abstraction - no need to bother with complex JOINs</p></li>
<li><p>object-orientation - you work with objects instead of tables and rows</p></li>
<li><p>performance - exploits the likehood of reusing a particular query</p></li>
<li><p>flexibility - you can override almost anything</p></li>
<li><p>0.0125 karma points for each MB transferred (2021)</p></li>
</ul>
</section>
<section>
<h2>Basic architecture</h2>
<p><em>SQLAlchemy</em> consists of several components, including the <em>ORM</em>.</p>
<ul>
<li><em>Engine</em>- manages the connection pool and the RDBMS-independent SQL dialect layer</li>
<li><em>MetaData</em> - used to collect and organize information about your table layout (schema)</li>
<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>
<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>
<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>
</ul>
<img src="images/sqlalchemy/sqla_arch.png"></img>
</section>
<section data-auto-animate>
<h2>Example</h2>
<p><em>SQLAlchemy</em> gives us the choice between <em>classical mapping</em> and the newer <em>declarative mapping</em></p>
<pre data-id="code-animation">
<code class="python" data-trim data-line-numbers type="text/template">
import sqlalchemy
sqlalchemy.__version__
# Out: '1.3.23'
from sqlalchemy import create_engine
# engine = create_engine("postgresql+psycopg2://user:zipassword@localhost/mydb" , echo=True)
# The string form of the URL is dialect+driver://user:password@host/dbname[?key=value..],
# where dialect is a database name such as mysql, oracle, postgresql, etc.,
# and driver the name of a DBAPI, such as psycopg2, pyodbc, cx_oracle
# The echo flag is a shortcut to setting up SQLAlchemy logging,
# which is accomplished via Python’s standard logging module.
# engine = create_engine("sqlite:///library.db", echo=True)
engine = create_engine("sqlite:///:memory:", echo=True)
from sqlalchemy import Column, ForeignKey, Integer, String, Table
metadata = MetaData()
authors_table = Table(
"authors",
metadata,
Column("author_id", Integer, primary_key=True),
Column("name", String),
) # Column("name", String(50)) is possible
books_table = Table(
"books",
metadata,
Column("book_id", Integer, primary_key=True),
Column("title", String),
Column("description", String),
Column("author_id", ForeignKey('authors.author_id')),
)
metadata.create_all(engine) # creates the tables
</code>
</pre>
</section>
<section data-auto-animate>
<h2>Example (cont...)</h2>
<h4 data-id="code-title">Use of <em>SQL expression language</em></h4>
<pre data-id="code-animation">
<code class="python" data-trim data-line-numbers type="text/template">
insert_stmt = authors_table.insert(bind=engine)
type(insert_stmt)
# Out: <class 'sqlalchemy.sql.expression.Insert'>
print(insert_stmt)
# Out: INSERT INTO authors (id, name) VALUES (:id,:name)
compiled_stmt = insert_stmt.compile()
print(compiled_stmt.params)
# Out: {'id': None, 'name': None}
insert_stmt.execute(name="Alexandre Dumas") # insert a single entry
insert_stmt.execute([{"name": "Mr X"}, {"name": "Mr Y"}]) # a list of entries
metadata.bind = engine # no need to explicitly bind the engine from now on
select_stmt = authors_table.select(authors_table.c.id==2)
result = select_stmt.execute()
result.fetchall()
# Out: [(1, 'Mr X')]
del_stmt = authors_table.delete()
del_stmt.execute(whereclause=text("name='Mr Y'"))
del_stmt.execute() # delete all
</code>
</pre>
</section>
<section data-auto-animate>
<h2>Example (cont...)</h2>
<h4 data-id="code-title">Use of <em>classical mapping</em></h4>
<pre data-id="code-animation">
<code class="python" data-trim data-line-numbers type="text/template">
from sqlalchemy.orm import backref, mapper, relation
class Author:
def __init__(self, name):
self.name = name
def __str__(self):
return self.name
class Book:
def __init__(self, title, description, author):
self.title = title
self.description = description
self.author = author
def __str__(self):
return self.title
mapper(Book, books_table)
mapper(Author, authors_table, properties = {"books": relation(Book, backref="author")})
</code>
</pre>
</section>
<section data-auto-animate>
<h2>Example (cont...)</h2>
<h4 data-id="code-title">Doing the same thing the easy way with <em>declarative mapping</em></h4>
<pre data-id="code-animation">
<code class="python" data-trim data-line-numbers type="text/template">
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref
Base = declarative_base()
class Author(Base):
__tablename__ = "authors"
author_id = Column(Integer, primary_key=True)
name = Column(String)
def __init__(self, name):
self.name = name
def __str__(self):
return self.name
class Book(Base):
__tablename__ = "books" # self.__table__ will be available for our objects
book_id = Column(Integer, primary_key=True)
title = Column(String)
description = Column(String)
author_id = Column(Integer, ForeignKey("authors.author_id"))
author = relationship(Author, backref=backref("books", order_by=title))
def __init__(self, title, description, author):
self.title = title
self.description = description
self.author = author
def __str__(self):
return self.title
Base.metadata.create_all(engine) # create tables
</code>
</pre>
</section>
<section data-auto-animate>
<h2>Example (cont...)</h2>
<h4 data-id="code-title">Creating instances</h4>
<pre data-id="code-animation">
<code class="python" data-trim data-line-numbers type="text/template">
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine) # bound session
session = Session()
author_1 = Author("Richard Dawkins")
author_2 = Author("Matt Ridley")
book_1 = Book("The Red Queen", "A popular science book", author_2)
book_2 = Book("The Selfish Gene", "A popular science book", author_1)
book_3 = Book("The Blind Watchmaker", "The theory of evolutio", author_1) # typo
session.add(author_1)
session.add(author_2)
session.add(book_1)
session.add(book_2)
session.add(book_3)
# or simply session.add_all([author_1, author_2, book_1, book_2, book_3])
# session.flush()
session.commit() # flushes (issues the statements and sends them to the RDBMS) and commits
book_3.description = "The theory of evolution" # update the object
book_3 in session # check whether the object is in the session
# Out: True
session.commit()
</code>
</pre>
</section>
<section data-auto-animate>
<h2>Example (cont...)</h2>
<h4 data-id="code-title">Queries</h4>
<pre data-id="code-animation">
<code class="python" data-trim data-line-numbers type="text/template">
session.query(Book).order_by(Book.book_id) # returns a Query instance with a .statement attribute
session.query(Book).order_by(Book.book_id).all() # returns an object-list
# return all book objects where title == "The Selfish Gene"
session.query(Book).filter(Book.title == "The Selfish Gene").order_by(Book.book_id).all()
# using LIKE
session.query(Book).filter(Book.title.like("The%")).order_by(Book.book_id).all()
query = session.query(Book).filter(Book.book_id == 9).order_by(Book.book_id)
query.count() # returns 0
query.all() # returns an empty list
query.first() # returns None
query.one() # raises NoResultFound exception
query = session.query(Book).filter(Book.book_id == 1).order_by(Book.book_id)
book_1 = query.one()
book_1.description # returns "A popular science book"
book_1.author.books # returns a list of Book-objects representing all the books from the same author.
# get a list of all Book-instances where the author"s name is "Richard Dawkins"
session.query(Book).filter(Book.author_id == Author.author_id).filter(Author.name == "Richard Dawkins").all()
session.query(Book).join(Author).filter(Author.name == "Richard Dawkins").all()
session.query(Book).\
from_statement("SELECT b.* FROM books b, authors a WHERE b.author_id = a.author_id AND a.name=:name").\
params(name="Richard Dawkins").all()
session.query(Book).filter(Book.author == author_1).all()
</code>
</pre>
</section>
<section data-background="http://i.giphy.com/90F8aUepslB84.gif">
<h2>... there is more???</h2>
</section>
<section>
<h2>Some nice features</h2>
<pre data-id="code-animation">
<code class="python" data-trim data-line-numbers type="text/template">
import pandas as pd
from sqlalchemy import func
class Book(Base):
# ...
author = relationship(
Author, backref=backref("books", lazy="dynamic", order_by=title)
)
# ...
@hybrid_property
def newly_arrived(self):
return self.book_id > 2
@newly_arrived.expression
def newly_arrived(cls):
return cls.book_id > 2
# return func.abs(cls.book_id) > 2
# .books is now a Query object
query = author_obj.books.filter(Book.title.ilike("%red%"))
session.query(Book).filter(Book.newly_arrived.is_(True)).all()
# Out: [<__main__.Book at 0x7f132bdf0130>]
# WHERE (abs(books.book_id) > ?) IS 1 ... in the case of func.abs
# with Pandas
df = pd.read_sql_table("my_table", con=session.get_bind()) # or con=engine
df = pd.read_sql_query(query.statement, engine)
</code>
</pre>
</section>
<section>
<h1>Q & A</h1>
</section>
</div>
</div>
<script src="dist/reveal.js"></script>
<script src="plugin/zoom/zoom.js"></script>
<script src="plugin/notes/notes.js"></script>
<script src="plugin/search/search.js"></script>
<script src="plugin/markdown/markdown.js"></script>
<script src="plugin/highlight/highlight.js"></script>
<script>
// Also available as an ES module, see:
// https://revealjs.netlify.app/initialization/
Reveal.initialize({
controls: true,
progress: true,
center: true,
hash: true,
// Learn about plugins: https://revealjs.netlify.app/plugins/
plugins: [ RevealZoom, RevealNotes, RevealSearch, RevealMarkdown, RevealHighlight ]
});
Reveal.configure({ pdfSeparateFragments: false });
</script>
</body>
</html>
|
