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