Adding a CMS to Your Pyramid Web App Transcripts
Chapter: Appendix: Modeling data with SQLAlchemy classes
Lecture: Modeling packages
0:00 It's time to bring SQLAlchemy into our application and start modeling things in the database, and actually using a database. How cool would that be?
0:10 The place to start, I think, is modeling the tables with classes in SQLAlchemy. That's not actually the order, the first thing that happens
0:19 in order when you execute the program. We have to do things like create the connection connect to the database, make sure the tables
0:25 are configured and match the stuff that we're modeling things like that. But conceptually, the primary thing we're doing is modeling
0:33 the database with these classes, so let's start there. Now, I personally like to have a separate dedicated section for the various classes.
0:42 So we're going to create a sub-package. So here we have our data and let's start by modeling package, we'll call it packages, something like that.
0:52 So here's how it works in SQLAlchemy. We have a class, we give it a name. Typically its name should be singular but, you know, however you want.
1:00 It represents a single entity in the database anyway. It's going to derive from something here which we have to define, do that in a moment.
1:12 And then into it, we're going to put a bunch of columns so it's going to be like an int. And then we'll have, say this be a name
1:22 this is going to be a string, and so on. The things that we put on the right here are special SQLAlchemy values and they serve two purposes.
1:31 At design time, they tell SQLAlchemy how to actually create the tables. At run time, they're effectively this integers, strings, and so on.
1:40 So lets start by importing SQLAlchemy. Sometimes you'll see this as sa to keep things short so maybe we'll do that.
1:51 Now notice it says this is not defined so let's install this. Cause, it's actually not defined. While we're doing that, we can go over here
1:59 and also put it in the setup. Alright, now let's try it again. Oh perfect, so that's great. So we want a column, and in this column we would like.
2:10 Actually, we were going to hijack the name thing and just make that the primary key. Remember the name of the package has to be unique on PyPI anyways.
2:19 So let's just make this an sa.string and let's say primary key is true. Okay, starts to feel database-y right?
2:29 The next thing, this is just something I like in databases is always knowing when a record was created.
2:35 It lets you look at things that were recently created or see them in order, things like that. So let's add a created date. A datetime.
2:46 And we're going to expand on these columns and make them a little bit nicer. But let's just get the basic structure in place first.
2:51 A summary, now let me try to tie these back. So if we go over to SQLAlchemy here's going to be the name or the ID. This is the summary, right there
3:03 that little tiny short thing. So we'll come over here this is the sa.column. And then, this part down here that is a big fat long description.
3:14 So we'll have a description as well. We're also going to come over here and we'll have this little home page. We're going to be able to click on that
3:24 so we need to model the home page. Also a string, and a docs URL some of them have the ability to say here are the documentation.
3:42 and a package URL. Also you'll see that these have authors and maintainers and then, here they have an author. So the author's Mike Bayer
3:55 and Mike Bayers's one of the maintainers as well but so is this person, okay? So we need to model it having a dedicated author
4:04 but then also maintainers. Now we could do this to a relationship or we could put it directly on there. Maybe we want to keep it even in case
4:13 they delete their account. So I'm going to put the author information here and we'll use a join to get to the maintainers.
4:22 Then finally, these all have a license. Now we're going to use, oh let's see if we find the license here. Here we go. It's MIT.
4:33 We're going to use a similar trick as we did here we would like to show just this simple information
4:39 just the name of the license and maybe a link to it as well. Which is just the same as the title basically. So what we'd like to do is we could
4:47 set this up in a way where we have to do a join on a license table because we're going to have a set of licenses.
4:54 But, if we make the ID also be the name and be unique why would you have two licenses with exactly the same name? We won't.
5:03 So, because the name would be unique we could use this and then this could actually both be just the string but also the relationship.
5:11 So that'll avoid one join and make our table a little bit faster. That'll be sweet. So one more thing down here
5:17 we're going to have releases and we're going to have maintainers. But the releases and the maintainers requires relationships.
5:27 So we're going to come back to that in just a little bit but right now, things are looking pretty good for our table.
5:34 If I go and start working with SQLAlchemy and saying here's my package table well one it needs a base class for this to work.
5:40 But on the other, it's going to create a table called Package as a singular. I personally don't like that
5:48 I'd prefer to have plural names and maybe lowercase. So we can control how this maps, so the database by putting a __tablename__ here.
5:59 And we'll say lowercase packages so on the table it's going to show up as lowercase packages. But when we have one of them
6:06 it's going to show up as a Package like a class would normally in Python. The other thing is sometimes nice when we're debugging this
6:14 you would like to see in PyCharm without actually expanding all the bits what package you got back, and so on. So we're going to add a __repr__.
6:26 And we'll just return something really simple here we'll just return. Just the ID, so it'll be package request package SQLAlchemy
6:38 and so on if we're looking at a debugger output or printing a list of these things. Something like that.
6:44 So that'll be a little bit helpful along the way. Alright so here's the basic concept of what we want to build.