-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathindex.html
538 lines (495 loc) · 22.2 KB
/
index.html
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
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>SQLAlchemy Core: An Introduction</title>
<meta name="description" content="A framework for easily creating beautiful presentations using HTML">
<meta name="author" content="Hakim El Hattab">
<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, maximum-scale=1.0, user-scalable=no">
<link rel="stylesheet" href="css/reveal.min.css">
<link rel="stylesheet" href="css/theme/default.css" id="theme">
<!-- For syntax highlighting -->
<link rel="stylesheet" href="lib/css/zenburn.css">
<!-- If the query includes 'print-pdf', include the PDF print sheet -->
<script>
if( window.location.search.match( /print-pdf/gi ) ) {
var link = document.createElement( 'link' );
link.rel = 'stylesheet';
link.type = 'text/css';
link.href = 'css/print/pdf.css';
document.getElementsByTagName( 'head' )[0].appendChild( link );
}
</script>
<!--[if lt IE 9]>
<script src="lib/js/html5shiv.js"></script>
<![endif]-->
</head>
<body>
<div class="reveal">
<!-- Any section element inside of this container is displayed as a slide -->
<div class="slides">
<section>
<h1>SQLAlchemy Core</h1>
<h2>An Introduction</h2>
<p>
<small><a href="http://jasonamyers.com">Jason Myers</a> / <a href="http://twitter.com/jasonamyers">@jasonamyers</a></small>
</p>
<br>
<p>
<small>Background by maul555</small>
</p>
<aside class="notes">Welcome, How do I describe SQLAlchemy? Well?</aside>
</section>
<section>
<img src="Awesome.gif">
</img>
<aside class="notes">It has a ton of very, very useful functions and while not everything is not a nail it solves a ton of common problems for me. Let’s talk about about how its put together.</aside>
</section>
<section>
<section>
<h2>Differences between Core and ORM</h2>
<aside class="notes"></aside>
</section>
<section>
<h2>ORM - Domain Model</h2>
<pre><code data-trim class="language-python">
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
password = Column(String)
</code></pre>
<aside class="notes">Focused around business objects and models</aside>
</section>
<section>
<h2>Core - Schema-centric Model</h2>
<pre><code data-trim class="language-python">
from sqlalchemy import Table, Column, Integer, String, MetaData
metadata = MetaData()
users = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('fullname', String),
)
</code></pre>
<aside class="notes">Focused around the database</aside>
</section>
</section>
<section>
<section>
<h2>Structure</h2>
<img src="stackablecats.jpg"></img>
<p><small>Copyright © 2014 Mochimochi Land</small></p>
<aside class="notes">The base of SQLAlchemy is the dialect and it’s handshake with the actual drivers via connection pools, the dialect along with schema introspection and typing enables us to use a python friendly SQL Expression language. All of these components are considered the Core of SQL Alchemy. Then we have a very rich ORM built on top of that foundation. If you TL;DR that ... it’s like GIT</aside>
</section>
<section>
<h2>Structure</h2>
<img src="layers.png"></img>
<aside class="notes">The base of SQLAlchemy is the dialect and it’s handshake with the actual drivers via connection pools, the dialect along with schema introspection and typing enables us to use a python friendly SQL Expression language. All of these components are considered the Core of SQL Alchemy. Then we have a very rich ORM built on top of that foundation. If you TL;DR that ... it’s like GIT</aside>
</section>
<section>
<img src="idea-01.jpg"></img>
<aside class="notes"></aside>
</section>
</section>
<section>
<section>
<h2>Installing</h2>
<p>pip install sqlalchemy</p>
<p class="fragment">pip install flask-sqlalchemy</p>
<p class="fragment">bin/paster create -t pyramid_alchemy tutorial</p>
<aside class="notes">In many cases you’ll just pip install SQLAlchemy. but if you’re using flask I recommend using the variant just for it and pyramid has a scaffold for SQLAlchemy as well</aside>
</section>
<section>
<h2>Initializing</h2>
<pre><code data-trim class="language-python">
import sqlalchemy
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')
</code></pre>
<aside class="notes">The first thing we need to do is setup an engine and give it the connection information. In this case, we're actually using an in memory SQLite database</aside>
</section>
</section>
<section>
<section>
<h2>Defining a Table</h2>
<pre><code data-trim class="language-python">
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
metadata = MetaData()
actors = Table('actors', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('fullname', String),
Column('body_count', Integer)
)
roles = Table('roles', metadata,
Column('id', Integer, primary_key=True),
Column('actor_id', None, ForeignKey('actors.id')),
Column('character_name', String, nullable=False)
)
</code></pre>
<aside class="notes">It’s not like a normal class definition, but a more list a list of table columns. The metadata is used as a catalog to provide a mapping between the actual tables and our representation of them.</aside>
</section>
<section>
<h2>Create the tables</h2>
<pre><code data-trim class="language-python">
metadata.create_all(engine)
</code></pre>
<aside class="notes">We take the MetaData object we created early, and bind it to the engine as see below. This will create our tables in the actually database. This is conditional by default, will not attempt to recreate tables already present in the target database. NOTE: It doesn't check to make sure they are the same just that an existing table is already present.</aside>
</section>
<section>
<h2>Table Objects</h2>
<pre><code data-trim class="language-python">
actors.columns.items()
[
('id', Column('id', Integer(), table=actors, primary_key=True...)),
('name', Column('name', String(), table=actors)),
('fullname', Column('fullname', String(), table=actors)),
('body_count', Column('body_count', Integer(), table=actors))
]
</code></pre>
<aside class="notes">We can look at the tables attributes, and use this to reference parts of them later. In this example, we're looking at the columns of the table. This can also be abbreviated as just actors.c.items()</aside>
</section>
<section>
<h2>Opening a connection</h2>
<pre><code data-trim class="language-python">
conn = engine.connect()
</code></pre>
<aside class="notes">Next we're going to open a connection to that engine so we can make queries etc.</aside>
</section>
</section>
<section>
<section>
<h2>Single Insert</h2>
<pre><code data-trim class="language-python">
ins = actors.insert().\
values(name='Graham', fullname='Graham Chapman', body_count=3)
result = conn.execute(ins)
result.inserted_primary_key
[1]
</code></pre>
<aside class="notes">First we're going to build an insert statement for putting a single record in our actors table. Then, we're excute that statement and store the result. Next, we'll get the primary key of that record back</aside>
</section>
<section>
<h2>Looking at what was executed</h2>
<pre><code data-trim class="language-python">
print str(ins)
ins.compile().params
INSERT INTO actors (name, fullname, body_count) VALUES (:name, :fullname, :body_count)
{'body_count': 3, 'fullname': 'Graham Chapman', 'name': 'Graham'}
</code></pre>
<aside class="notes">Now, we're going to print the SQL statement as build via the dialect we choose above, and then print the params from that statement</aside>
</section>
<section>
<h2>Multiple Insert</h2>
<pre><code data-trim class="language-python">
results = conn.execute(roles.insert(), [
{'actor_id': 1, 'character_name' : 'King Arthur'},
{'actor_id': 1, 'character_name' : 'Voice of God'},
{'actor_id': 2, 'character_name' : 'Sir Lancelot'},
{'actor_id': 2, 'character_name' : 'Black Knight'},
{'actor_id': 3, 'character_name' : 'Patsy'},
{'actor_id': 3, 'character_name' : 'Sir Bors'},
])
results.rowcount
6
</code></pre>
<aside class="notes">We can also insert multiple records like above. Transactions can be manually started, but by default wrap each execute.</aside>
</section>
<section>
<h2>Update</h2>
<pre><code data-trim class="language-python">
stmt = actors.update().where(actors.c.name == 'Graham').values(name='Gram')
result = conn.execute(stmt)
result.rowcount
1
</code></pre>
<aside class="notes">When looking at updating data we often want to do it to just a particular record, or a certain group of records. The use of the .where() clause let's us do just that. Then we supply a list of values to update.</aside>
</section>
<section>
<h2>Delete</h2>
<pre><code data-trim class="language-python">
result = conn.execute(actors.delete().where(actors.c.name == 'Terry'))
result.rowcount
1
</code></pre>
<aside class="notes">Very similar to update just it will remove the record.</aside>
</section>
<section>
<h2>Selecting</h2>
<pre><code data-trim class="language-python">
s = select([actors.c.name, actors.c.fullname])
result = conn.execute(s)
for row in result:
print row
(u'Graham', u'Graham Chapman')
(u'John', u'John Cleese')
(u'Terry', u'Terry Gilliam')
</code></pre>
<aside class="notes">So unlike the other statements that were clauses off of the table object, this time we're going to use a new select stmt and operate on the table that way. You can use a clause off the table object; however, this is more commonly used as you'll normally be joining data and doing other things. Also notice how I specified the columns.</aside>
</section>
<section>
<h2>Ordering</h2>
<pre><code data-trim class="language-python">
stmt = select([actors.c.name]).order_by(actors.c.name.desc())
conn.execute(stmt).fetchall()
[(u'Terry',), (u'John',), (u'Graham',)]
</code></pre>
<aside class="notes">Here we're sorting by the name in descending alphabetical order. .asc() will do the accending order</aside>
</section>
<section>
<h2>Limiting</h2>
<pre><code data-trim class="language-python">
stmt = select([actors.c.name, actors.c.fullname]).limit(1).offset(1)
conn.execute(stmt).first()
(u'John', u'John Cleese')
</code></pre>
<aside class="notes">Here we are limiting the query to return only one record and offset it by 1. So it should be the second record in the table. Notice the use of the ``.first()`` clause on the statement execution. This gives us back the first result instead of the list we had been dealing with previously.</aside>
</section>
</section>
<section>
<section>
<h2>Count</h2>
<pre><code data-trim class="language-python">
from sqlalchemy.sql import func
stmt = select([func.count(actors)])
conn.execute(stmt).scalar()
2
</code></pre>
<aside class="notes">Here we are limiting the query to return only one record and offset it by 1. So it should be the second record in the table. Notice the use of the .first() clause on the statement execution. This gives us back the first result instead of the list we had been dealing with previously.</aside>
</section>
<section>
<h2>Sum</h2>
<pre><code data-trim class="language-python">
stmt = select([func.count(actors), func.sum(actors.c.body_count)])
conn.execute(stmt).first()
(2, 5)
</code></pre>
<aside class="notes">Here we are building a select statement that counts the actors in our table, and sums their body counts</aside>
</section>
</section>
<section>
<section>
<h2>Joins</h2>
<pre><code data-trim class="language-python">
s = select([actors, roles]).where(actors.c.id == roles.c.actor_id)
for row in conn.execute(s):
print row
(1, u'Graham', u'Graham Chapman', 1, 1, u'King Arthur')
(1, u'Graham', u'Graham Chapman', 2, 1, u'Voice of God')
(2, u'John', u'John Cleese', 3, 2, u'Sir Lancelot')
(2, u'John', u'John Cleese', 4, 2, u'Black Knight')
(3, u'Terry', u'Terry Gilliam', 5, 3, u'Patsy')
(3, u'Terry', u'Terry Gilliam', 6, 3, u'Sir Bors')
</code></pre>
<aside class="notes">Now let's join the actors and the roles tables on the ID of the actor.</aside>
</section>
<section>
<h2>Grouping</h2>
<pre><code data-trim class="language-python">
stmt = select([actors.c.name, func.count(roles.c.id)]).\
select_from(actors.join(roles)).\
group_by(actors.c.name)
conn.execute(stmt).fetchall()
[(u'Graham', 2), (u'John', 2), (u'Terry', 2)]
</code></pre>
<aside class="notes">We can also use the .join() clause on the table object to join to tables, and then group them to get a count of roles by actor.</aside>
</section>
<section>
<h2>Filtering</h2>
<pre><code data-trim class="language-python">
from sqlalchemy.sql import and_, or_, not_
stmt = select([actors.c.name, roles.c.character_name]).\
where(
and_(
actors.c.name.like('Gra%'),
roles.c.character_name.like('Vo%'),
actors.c.id == roles.c.actor_id
)
)
conn.execute(stmt).fetchall()
[(u'Graham', u'Voice of God')]
</code></pre>
<aside class="notes">First, let's pull in some additional parts of the sqlalchemy.sql module. NOTE: we're only using and_() in this example, but they all work the same way and can be nested. Next we're going to use the .where() clause and nest an and_() cause inside of it.</aside>
</section>
<section>
<h2>And So on...</h2>
<aside class="notes">This is really just the beginning of the types of querying that can be done with sqlalchemy, but I want to move on to two other areas of sqlalchemy awesomeness.</aside>
</section>
</section>
<section>
<section data-background="dialect.jpg">
<aside class="notes">in addition to column types, the dialects define sql commands, their parameters loosely they are validated by the database driver and by sending the actual command to the DB.</aside>
</section>
<section>
<h2>Common Dialects</h2>
<ul>
<li>Informix</li>
<li>MS SQL</li>
<li>Oracle</li>
<li>Postgres</li>
<li>SQLite</li>
<li>Custom</li>
</ul>
<aside class="notes">Most common systems are covered, and customizing them is fairly easy. For example at work we use redshift, and we’ve been writing a sqlalchemy_redshift dialect. Which you can download on github. So let’s query some data...</aside>
</section>
<section>
<h2>But what if...</h2>
<pre><code data-trim class="language-python">
class UnloadFromSelect(Executable, ClauseElement):
def __init__(self, select, bucket, access_key, secret_key):
self.select = select
self.bucket = bucket
self.access_key = access_key
self.secret_key = secret_key
@compiles(UnloadFromSelect)
def visit_unload_from_select(element, compiler, **kw):
return "unload ('%(query)s') to '%(bucket)s'
credentials 'aws_access_key_id=%(access_key)s;
aws_secret_access_key=%(secret_key)s' delimiter ','
addquotes allowoverwrite" % {
'query': compiler.process(element.select,
unload_select=True, literal_binds=True),
'bucket': element.bucket,
'access_key': element.access_key,
'secret_key': element.secret_key,
}
</code></pre>
<aside class="notes">Here is an example of a single statement for a custom dialect. It establishes an unload from command as seen in Amazon Redshift</aside>
</section>
<section>
<h2>Example Statement</h2>
<pre><code data-trim class="language-python">
unload = UnloadFromSelect(
select([fields]),
'/'.join(['s3:/', BUCKET, filename]),
ACCESS_KEY,
SECRET_KEY
)
</code></pre>
<aside class="notes">Example Usage</aside>
</section>
<section>
<h2>Example Usage</h2>
<pre><code data-trim class="language-python">
unload (
'select * from venue where venueid in (
select venueid from venue order by venueid desc limit 10)'
)
to 's3://mybucket/venue_pipe_'
credentials 'aws_access_key_id=ACCESS_KEY;
aws_secret_access_key=SECRET_KEY';
</code></pre>
<aside class="notes">Generated Statement</aside>
</section>
</section>
<section>
<h2>Dynamic Table Introspection</h2>
<pre><code data-trim class="language-python">
def build_table(engine, table_name):
return Table(table_name, metadata, autoload=True, autoload_with=engine)
</code></pre>
<aside class="notes">This introspection makes a table from a given engine and metadata collection.</aside>
</section>
<section>
<section>
<h2>Chaining</h2>
<pre><code data-trim class="language-python">
s = select(
[
t.c.race,
t.c.factor,
func.sum(g.t.c.value).label('summed')
], t.c.race > 0
).where(
and_(
t.c.type == 'POVERTY',
t.c.value != 0
)
).group_by(
t.c.race,
t.c.factor
).order_by(
t.c.race,
t.c.factor)
</code></pre>
<aside class="notes"></aside>
</section>
<section>
<h2>Conditionals</h2>
<pre><code data-trim class="language-python">
s = select(
[
table.c.discharge_year,
func.count(1).label(
'patient_discharges'),
table.c.zip_code,
], table.c.discharge_year.in_(years)
).group_by(table.c.discharge_year)
s = s.where(table.c.hospital_name == provider)
if 'total_charges' not in unavailable_fields:
s = s.column(
func.sum(table.c.total_charges
).label('patient_charges')
)
</code></pre>
<aside class="notes">What’s great is we can do this a step at a time and add conditionals to create kind of a query builder. Some datasets have total_charges if they do we add those to the result.</aside>
</section>
<section>
<pre><code data-trim class="language-python">
s = s.group_by(table.c.zip_code)
s = s.order_by('discharges DESC')
cases = conn.execute(s).fetchall()
</code></pre>
<aside class="notes"></aside>
</section>
</section>
<section data-background="questions.jpg">
<h1>questions</h1>
<h2>Thank you</h2>
<p>
<small><a href="http://jasonamyers.com">Jason Myers</a> / <a href="http://twitter.com/jasonamyers">@jasonamyers</a></small>
</p>
<p>Slides and IPython Notebook on <a href="http://bit.ly/pycon2014slides">http://bit.ly/pycon2014slides</a></p>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<aside class="notes"></aside>
</section>
</div>
</div>
<script src="lib/js/head.min.js"></script>
<script src="js/reveal.min.js"></script>
<script>
// Full list of configuration options available here:
// https://github.com/hakimel/reveal.js#configuration
Reveal.initialize({
controls: true,
progress: true,
history: true,
center: true,
theme: Reveal.getQueryHash().theme, // available themes are in /css/theme
transition: Reveal.getQueryHash().transition || 'default', // default/cube/page/concave/zoom/linear/fade/none
// Parallax scrolling
parallaxBackgroundImage: 'background.jpg',
parallaxBackgroundSize: '5760px 3240px',
// Optional libraries used to extend on reveal.js
dependencies: [
{ src: 'lib/js/classList.js', condition: function() { return !document.body.classList; } },
{ src: 'plugin/markdown/marked.js', condition: function() { return !!document.querySelector( '[data-markdown]' ); } },
{ src: 'plugin/markdown/markdown.js', condition: function() { return !!document.querySelector( '[data-markdown]' ); } },
{ src: 'plugin/highlight/highlight.js', async: true, callback: function() { hljs.initHighlightingOnLoad(); } },
{ src: 'plugin/zoom-js/zoom.js', async: true, condition: function() { return !!document.body.classList; } },
{ src: 'plugin/notes/notes.js', async: true, condition: function() { return !!document.body.classList; } }
]
});
</script>
</body>
</html>