#100DaysOfCode in Python Transcripts
Chapter: Days 91-93: Database access with SQLAlchemy
Lecture: Concepts: SQLAlchemy
0:00 Let's quickly review some of the concepts that we learned.
0:03 We saw everything started with our model base
0:05 and we got that by calling declarative base
0:07 that gave us a type back
0:10 which then we could use to derive from.
0:12 So we get this base class,
0:13 and then we derive all of our various entities from it.
0:17 In this particular example
0:18 when we're looking at an online record store
0:20 with albums, tracks, purchases, users, and so on.
0:23 We'd create an album, track, and purchase
0:24 all deriving from our SQLAlchemy base that we create.
0:28 When we want to model one of these classes,
0:30 we want to use the class to model some data.
0:33 We set the dunder table name,
0:35 pick out the various columns we need.
0:36 So here we have a primary key auto-incrementing id.
0:39 We have a name, year, price.
0:42 We saw that we can put uniqueness constraints.
0:44 We can put indexes to make queries on that data
0:47 or ordering by that data super, super fast.
0:49 And we can even set up relationships.
0:51 But like I said, we're not going into relationships.
0:53 We've already spent a lot of time on discussing SQLAlchemy.
0:56 It's time for you to jump in and write some code.
0:59 Once we've modeled all of the classes,
1:01 then we need to actually create the database connection
1:04 and make sure the database is in sync
1:06 with what we define the classes to be.
1:08 Here we're going to create a connection string
1:10 which is just a sqlite:///.
1:12 Put it in a file.
1:14 We'll create an engine based on that connection string.
1:16 We're going to create,
1:17 go to the metadata for the SQLAlchemy base
1:19 and call create all.
1:21 Pass at the engine so it knows how to do that.
1:23 Then finally we're going to create a session factory
1:25 by calling the session maker, giving it the engine.
1:28 We'll being using that for our unit of work
1:30 for all the queries and transactions and so on
1:33 throughout the rest of our app.
1:34 If we want to create a query and pull back a single record,
1:37 here we'd create the session.
1:39 We say, "query of the type".
1:41 So we're going to query the account table,
1:42 say, "filter emails this.filter".
1:45 Password hash is that.
1:46 Now this double filter is basically an and.
1:49 So, here we're doing a query
1:50 where the email is what we specify,
1:52 and the passwordhash is what we specify.
1:54 Or we're going to get nothing.
1:55 And then, we can just get one back.
1:57 So we can either say one or first
1:59 and then we're going to return the account
2:01 that we got back here.
2:02 What does that look like in the database?
2:04 It's select star from account
2:05 where account email is some parameter,
2:08 and account.passwordhash at some other parameter.
2:10 And the params happen to be my Gmail address,
2:13 and some random text I threw in there.
2:16 Finally, you might be familiar
2:17 with the SQL query language but not SQLAlchemy.
2:20 And wonder how do these things map over?
2:23 So equals, simple that's a double equal.
2:26 Not equal, that's also kind of simple.
2:28 Not equal goes in the middle here,
2:30 and then it gets a little interesting.
2:32 If you want to do a like query that's like a substring,
2:34 I want all the names that contain the substring ed.
2:37 That's a .like('%ed%).
2:41 So the percents are like wild cards, can match anything.
2:43 Long as ed is in there somewhere we'll get that as a match.
2:46 N, so I want all the users whose name
2:48 is either Ed, Wendy, or Jack.
2:50 You and put the little tilde
2:51 in front of this whole thing and say, not in.
2:52 You can say null is None.
2:54 And is just multiple filters.
2:56 Or is a little more complex, but there's an or operator
2:59 that let's you pass a tuple along.
3:01 Or actually just multiple parameters
3:03 and that will turn those all into an or.
3:04 So you can see the link for all of these
3:06 and there's more as well over at the SQLAlchemy website.
3:10 Alright so that's SQLAlchemy.
3:12 I hope you really enjoy it.
3:13 It's really a great way to build professional,
3:16 data driven applications.