Python for the .NET developer Transcripts
Chapter: Database access and ORMs in Python
Lecture: From in-memory classes to SQLAlchmey entities

Login or purchase this course to watch this video and the rest of the course contents.
0:00 It's time to get started with SQLAlchemy
0:01 and Python's ORM version of our website.
0:05 So, the first thing that I think is, makes sense
0:08 is to take this class and convert it from a standard class
0:12 over into a specific SQLALchemy entity.
0:16 Right, right now all we're doing over in our services
0:18 is we're just creating one.
0:20 This is like a standard Python object.
0:22 Nothing special about it.
0:24 So, how do we convert this to a SQLAlchemy class?
0:27 Well, first thing we have to use a slightly different
0:30 way of defining fields.
0:31 We are going to define static fields.
0:33 And the way that you do that over here
0:35 is you would just say things like
0:37 name, lets just say equals empty string.
0:41 Now, if we do that, we still have self.name.
0:46 This is a bit of difference between Python and C#.
0:49 In C#, you either have a field which is static
0:54 or it's an instance one.
0:56 In Python, the instance ones are just instance things.
0:59 But, the static ones have a static copy
1:02 and then also, when you create instances
1:04 they get their own copy.
1:06 It's a little bit weird, but that's how it works.
1:09 So, what we are going to do is, we're not going to define these as instance versions.
1:12 We are going to put them up here.
1:13 Like so. Alright, with that gone, we don't need
1:17 or with that moved, we don't need this anymore.
1:19 But let's go ahead and be super specific
1:21 that these are still strings. The way they're set up now
1:24 yeah everything's going to know it's a string or float
1:26 but you know what?
1:27 In a moment, we're all going to change things
1:29 so they're not.
1:31 Now, the next thing that we need to do is
1:32 we need to create a bass class, so
1:37 like this. We need to derive from it. Like so.
1:42 Now, that would be totally normal looking
1:44 if this is how we're defining the class.
1:46 But, SQLAlchemy's ORM will, at runtime, create a type
1:51 which is the bass class, but then the way SQLAlchemy
1:54 knows what classes and tables to create is
1:57 it goes and says well, what objects derive from my bass class?
1:59 Those are the ones apparently I'm in control of.
2:02 So, we can't create the class this way
2:04 we're going to do something a little bit differently.
2:06 We're going to go over here and say import SQLAlchemy.
2:12 Apparently we don't have that in our project, right?
2:14 So let's go through the process to put that there.
2:20 And then we'll go down here and pip install.
2:25 Okay, great! So we got that installed.
2:28 We're going to go to sqlalchemy.ext.declarative.
2:30 So, we're going to import that
2:31 and then here, this is the funky runtime bit.
2:34 Say declarative_base. Just like that.
2:37 And this is going to create a new type
2:38 just as if it was the way I had typed before.
2:41 But, it's a special runtime class that we can derive from.
2:45 Okay, so now this is what we want to do.
2:48 We got to go over here and change these into columns.
2:52 Though we're going to go over here and say import sqlalchemy as sa
2:55 we use a little alias here so we can type less.
3:00 So we are going to say this is a column in the database.
3:03 And it is an integer. It's a primary key
3:09 and it's an auto-incrementing primary key for us.
3:11 Actually, sorry this is a, We're going to need this.
3:16 Now let's go to the name. Going to be an sa.Column(sa.String).
3:21 And that's that. Now we'll put the same over here, for style.
3:26 Image going to be a float.
3:29 Now, we might want to do queries against these things.
3:32 Remember, we're doing a query for one style or the other.
3:35 So you would, like an index on style of course
3:38 that'll make it much faster if you have a lot of data.
3:40 So we just say index=True.
3:42 Then, when SQLAlchemy creates the table
3:44 it's going to add an index there.
3:46 Similarly, we're ordering by price
3:49 that's faster with an index as well.
3:50 So we can go like this. Probably we won't ask by name.
3:54 Unlikely, very unlikely we ask by image.
3:57 And I guess we could say show us the ones that have no image
4:00 but probably not. But I think that this is what we need.
4:04 Now, if I run it like this it's going to create the
4:06 table and the database called that.
4:09 I don't want it to be called capital G singular Guitar.
4:13 I want it to be lower case guitars.
4:15 So we can go over here and add another field.
4:18 Table name, __tablename__ = 'guitars'.
4:22 And this is what's actually going to be created
4:24 in the database. Okay, this is it.
4:27 This is how we define entities.
4:28 If we wanted, like, something else we'd have
4:30 a class user, which is a SqlAlchemyBase. And so on.
4:37 If we're going to just leave all of these entities and classes
4:40 in one giant file, we could do it, then this is fine.
4:44 We'll just put our SqlAlchemyBase at the top
4:46 and we just roll with it. But, I hate that style.
4:49 I want a class in its own file.
4:51 And, in order to make that work well in Python
4:55 what we need to do is go over here and have another file
4:58 sqlalchemybase like this. This is going to look silly, but here we go.
5:02 We're going to take this and put it there.
5:04 And we're going to take that and put it there.
5:07 This is the entire implementation of the SqlAlchemyBase.
5:10 And so, why am I doing this?
5:11 In Python, you can't have circular import dependencies.
5:16 Okay, so like, if I put it here I need the thing
5:19 that creates the guitar.
5:20 To, like, there's a few situations where this might
5:23 have to be imported in multiple places and so on.
5:26 So we just need to import that up at the top, like so.
5:30 There we go. Now, our guitar is ready to be saved into the database
5:34 Used for queries and actually generate the table
5:36 that it represents with indexes and primary keys and all that.