#100DaysOfCode in Python Transcripts
Chapter: Days 91-93: Database access with SQLAlchemy
Lecture: Concepts: SQLAlchemy
Login or
purchase this course
to watch this video and the rest of the course contents.
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.