Python-powered chat apps with Twilio and SendGrid Transcripts
Chapter: Appendix: Modeling data with SQLAlchemy classes
Lecture: Modeling packages
0:00 Let's start writing some code
0:01 for our SQLAlchemy data model.
0:04 And as usual, we're starting in the final
0:06 here's a copy of what we're starting with.
0:08 This is the same right now, but we'll be evolved
0:10 to whatever the final code is.
0:12 So, I've already set this up in PyCharm
0:14 and we can just open it up.
0:16 So, the one thing I like to do
0:17 is have all my data models and all the SQLAlchemy stuff
0:20 in its own little folder, here.
0:22 So, let's go and add a new directory called data.
0:26 Now, there's two or three things we have to do
0:28 to actually get started with SQLAlchemy.
0:31 We need to set up the connection to the database
0:33 and talk about what type of database is it.
0:36 Is it Postgres, is a SQLite, is it Microsoft SQL Server?
0:39 Things like that.
0:40 We need to model our classes which map Python classes
0:44 into the tables, right, basically create and map
0:47 the data over to the tables.
0:49 And then, we also need a base class
0:51 that's going to wire all that stuff together.
0:54 So, the way it works is we create a base class
0:56 everything that derived from a particular base class
0:59 gets mapped particular database.
1:01 You could have multiple base classes, multiple connections
1:04 and so on through that mechanism.
1:05 Now, conceptually, the best place to start I think
1:08 is to model the classes.
1:09 It's not actually the first thing that happens
1:11 in sort of an execution style.
1:13 What happens first when we run the code
1:15 but, it's conceptually what we want.
1:16 So, let's go over here, and model package.
1:19 Do you want to create a package class Package?
1:23 Now, for this to actually work
1:24 we're going to need some base class, here.
1:26 But, I don't want to really focus on that just yet
1:28 we'll get to that in a moment.
1:29 Let's stay focused on just this idea of modeling data
1:33 in a class with SQLAlchemy that then maps
1:35 to a database table.
1:38 So, the way it works is we're going to have fields here
1:40 and this is going to be like an int or a string.
1:43 You will have a created_date
1:46 which, is going to be a datetime.
1:48 We might have a description, which is a string, and so on.
1:52 Now, we don't actually set them to integers.
1:55 Instead, what we're going to set them to our descriptors
1:57 that come from SQLAlchemy.
1:59 Those will have two distinct purposes.
2:03 One, define what the database schema is.
2:06 So, we're going to set up some kind of column information
2:08 with type equals integer or something like that.
2:10 And SQLAlchemy will use that to generate
2:12 the database schema.
2:14 But at runtime, this will just be an integer
2:17 and the creator date will just be a datetime, and so on.
2:20 So, there's kind of this dual role thing going on
2:23 with the type definition here.
2:25 We're going to start by importing SQLAlchemy.
2:27 And notice that that is turning red
2:30 and PyCharm says, "you need to install this", and so on.
2:33 Let's go make this little more formal
2:34 and put SQLAlchemy down here's a thing
2:37 and PyCharms like "whoa, you also have to install it here".
2:40 So, go ahead and let it do that.
2:42 Well, that looks like it worked really well.
2:44 And if we go back here, everything should be good.
2:46 Now, one common thing you'll see people do
2:48 is import sqlalchemy as sa
2:51 because you say, "SQLAlchemy" a lot.
2:54 Either they'll do that or they'll just
2:56 from SQLAlchemy import either *
2:58 or all the stuff they need.
3:00 I preferred have a little namespace.
3:01 So, we're going to do, we want to come here
3:02 and say sa.Column.
3:05 And then, we have to say what type.
3:06 So, what I had written there would have been an integer.
3:08 And we can do things like say
3:10 this is the primary_key, true.
3:12 And if it's an integer
3:14 you can even say auto_increment, true
3:16 which, is pretty cool.
3:17 That way, it's just automatically managed in the database
3:20 you don't have to set it or manage it or anything like that.
3:23 However, I'm going to take this away
3:24 because of what we actually want to do
3:26 is use this as a string.
3:29 Think about the packages in PyPI.
3:32 They cannot have conflicting names.
3:34 You can't have two separate packages with the name Flask.
3:37 You can have two distinct packages with the name SQLAlchemy.
3:41 This has to be globally unique.
3:43 That sounds a lot like a primary key, doesn't it?
3:46 Let's just make the name itself be the primary key.
3:49 And then over here, we're going to do something similar
3:51 say, column, and that's going to be sa.DateTime.
3:55 I always like to know almost any database table I have
3:59 I like to know when was something inserted.
4:01 When was it created?
4:03 That way you can show new packages
4:04 show me the things created this week or whatever
4:07 order them by created descending, who knows.
4:09 Now, we're actually going to do more
4:10 with these columns, here.
4:11 But, let's just get the first pass basic model in place.
4:14 Well, it's going to have a summary.
4:16 It's going to be sa.Column(sa.String
4:22 we'll have a summary, also a description.
4:25 We're also going to have a homepage.
4:27 Sometimes this will be just the GitHub page.
4:28 Sometimes it's a read the docs page, you never know
4:31 but, something like that.
4:32 We'll also have a docs page or let's say a docs_url.
4:38 And some of the packages have a package_url.
4:42 This is just stuff I got from looking at the PyPI site.
4:46 So, let's review real quick for over here on say SQLAlchemy
4:50 here's the, the name.
4:53 And then what, we're going to have versions
4:54 that's tied back to the releases, and so on.
4:57 Here's the project description
4:58 we have maybe the homepage
5:01 we'll also have things like who's the maintainers
5:04 what license does it have, and so on.
5:07 So, let's keep going.
5:08 Now, here's the summary, by the way
5:10 and then, this is the description.
5:11 Okay, so this stuff is all coming together pretty well.
5:14 Notice over here, we have an author, who is Mike Bayer
5:20 and we have maintainers, also Mike Bayer, right there
5:22 and some other person.
5:25 So, we have this concept of the primary author
5:27 who may not even be a maintainer anymore
5:29 and then maintainers.
5:30 So, we're going to do a little bit of a mixture.
5:33 I'm going to say, author name
5:36 it's going to be one of these strings
5:37 just embed this in here
5:39 and it will do email
5:41 so, we can kind of keep that
5:42 even if they delete their account.
5:45 And then later, we're going to have maintainers
5:48 and we're also going to have releases.
5:50 These two things I don't want to talk about yet
5:51 because they involve relationships
5:53 and navigating hierarchies, and all that kind of stuff
5:55 we're going to focus on that as a separate topic.
5:58 The last thing we want to do is have a license, here.
6:01 And for the license
6:02 we want to link back to a rich license object.
6:05 However, we don't necessarily want to have to do a join
6:09 on some integer id to get back just
6:11 the name to show it there.
6:12 It would be cool if we could use
6:14 somehow use this trick.
6:16 And actually, we can, we can make the name of the license
6:20 the friendly name, be just the id, right.
6:22 You would not have two MIT licenses.
6:24 So, we'll just say this is an sa.Column(sa.String
6:30 which, is an Sa.string.
6:34 That's cool, right, okay, so here is our first pass
6:37 at creating a package.
6:40 So, I think this is pretty good.
6:41 It models pretty accurately what you see over here on PyPI.
6:45 There's a few things that we're omitting like metadata
6:47 and so on, but it's going to be good enough for demo app.
6:52 A couple more things before we move on here.
6:54 One, if I go and interact with this and try to save it into
6:59 create one of these packages and save it into the database
7:01 with SQLAlchemy, a couple of things.
7:03 One, it needs a base class.
7:04 We're going to do that next.
7:05 But, it's going to create a table called Package
7:09 which is singular.
7:11 Now, this should be singular, this class
7:13 it represents a single one of the packages in the database
7:17 when you work with it in Python
7:18 but, in the database, the table represents many packages
7:22 all of them, in fact.
7:23 So, let's go over here and use a little invention
7:26 in SQLAlchemy to change that table name
7:29 but, not the class name.
7:30 So, we'll come down here and say
7:31 __tablename__ = 'packages', like so
7:36 So, if we say it's packages
7:37 that's what the database is going to be called.
7:40 And we can always tell PyCharm
7:42 that, that's spelled correctly.
7:43 The other thing to do when we're doing
7:45 a little bit of debugging
7:46 or we get a set of results back
7:48 in Python, not in the database
7:49 and we just get like a list or we want to look at it
7:52 it's really nice to actually see
7:53 a little bit more information in the debugger
7:55 than just package, package, package
7:57 at address, address, address.
7:59 So, we can control that by having a __repr__
8:02 and just returning some string here
8:04 like package such and such like this.
8:10 I'll say self.id.
8:12 So, you know, if we get back, Flask and SQLAlchemy
8:16 we'd say, angle bracket package Flask
8:19 angle bracket package SQLAlchemy.
8:20 So, that's going to make our debugging life
8:22 a little bit easier as we go through this app.
8:25 Alright, so not finished yet
8:27 but, here's a really nice first pass at modeling packages.