Python for the .NET developer Transcripts
Chapter: Database access and ORMs in Python
Lecture: Inserting data
0:00 Well, we have our database with our table.
0:02 Let's go see if there's any data in here.
0:06 select * from guitars. We just run that.
0:10 You can see zero rows, totally empty.
0:14 So we don't have any data yet, but we want to insert it
0:17 and that's the next thing that we're going to look at here.
0:19 We already have this data
0:21 and this is in our catalog service, right?
0:23 This is instead of having a real database.
0:25 So let's just go
0:26 and insert this data into the database
0:29 and then, get rid of it here
0:31 and we'll just load it out of the database.
0:32 Maybe we'll make a minor change
0:34 just so you can see what happens.
0:36 Let's go create a file here called data_loader.
0:40 It's not going to do much.
0:41 In general, it's just going to preload
0:44 the database the very first time
0:45 and then, it's never going to do anything again.
0:48 Let's go over here and say, def load_guitars_if_empty.
0:53 We'll have our guitars right there
0:54 and we can import this class.
0:58 Now, there's a small change that we have to make here.
1:01 We can try to call this function and see what we get.
1:04 It's probably not going be so great, so let's go down here.
1:07 We're doing create_tables. Actually, do the right import.
1:18 We'll say load guitars if empty.
1:19 If we try to run this, what happens? Boom, crash.
1:24 This right here, we no longer have a constructor, remember?
1:28 This constructor here, we're just now using the default one.
1:31 We're not using the one
1:33 whatever SQLAlchemyBase happens to provide.
1:35 We're not using the one that we wrote.
1:37 So we can't do it that way, but it turns out
1:39 we can almost do it that way.
1:41 Watch this, we come down here and say, name equals this
1:45 and just use keyword arguments, price, IMJ
1:50 and we're already setting style explicitly for some reason.
1:53 Apparently, we're doing that before.
1:54 We run it again, it'll still crash
1:56 but now we get to one more line, so the first line worked.
1:59 Let me just go and do the rest. Alright, that looks better.
2:04 Let's try to run it again. Hey, it runs.
2:06 That doesn't mean anything got imported.
2:08 That just means we're able to create this list.
2:11 Okay, everything is great
2:12 and also, let's change the price of the acoustic guitar
2:14 to 1,298 instead of 99.
2:18 So when we do the query, we see that number, we know
2:20 hey, this one came from the database.
2:23 Alright, well, this is really good.
2:24 The next thing that we need to do is to get our session
2:28 so we can create a context manager here.
2:30 We'll say with and let's do the import more explicit up here.
2:35 We're going to use this session factory
2:36 so we'll say session_factory.create_session as
2:41 maybe it should be session context or something like that.
2:44 So remember, down here, it's not a session itself.
2:47 We have a session that has a session property.
2:52 Now, the stuff that we do inside
2:53 this ctx, this with block here
2:56 this is going to be within the unit of work
2:58 and at end, we'll say ctx.session.commit.
3:03 OK, so we're going to do work here
3:06 and then, eventually, before we leave
3:07 we can commit it, assuming that we're happy.
3:10 What do we want to do?
3:11 We want to insert all those guitars, so for guitar in guitars
3:16 watch this, not too hard.
3:18 We're going to do a bunch of inserts to the database
3:20 ctx.session.add(guitar). Done.
3:24 That's it, that's all we have to do
3:26 is go and add the guitars and then, call commit.
3:29 I believe there might also be a bulk insert
3:30 but you use that much left often, so this is how you create
3:33 a single object and then, add it.
3:35 And we just happen to be doing it eight times or something.
3:39 Now the other thing we want to be real careful about here
3:41 is we only want to do this if there are no guitars.
3:44 So we'll say if ctx.session.query
3:48 jump ahead a little bit, we say query of guitar
3:51 count is greater than zero, print, not adding new data.
3:58 Let's maybe save this.
4:04 There are count guitars already, something like that.
4:09 And don't forget a return here. Alright so, we have our data.
4:14 Going to go do our tests.
4:16 I guess we could go do that the beginning
4:18 but then we would have to move stuff around.
4:21 I don't know, whatever.
4:22 Let's just go ahead and make it better. Here we go.
4:28 Alright, so we're going to start out this method.
4:33 Get in the session
4:34 and if we don't need to do any work, that's it, we're out.
4:36 Otherwise, we'll create the data
4:37 and we'll add it and commit it.
4:40 We're not doing anything with this yet, are we?
4:42 Yeah, we are.
4:43 We're actually calling it here. So let's go ahead and just run this and see what happens.
4:47 Remember. Currently, there are no rows.
4:53 Let's run this again.
4:56 Looks like it started, that's cool.
4:59 Run this one more time, what do we get?
5:01 Tada. There's all of our rows inserted
5:03 and there's our acoustic black for $1,298.
5:08 That's cool, let's run this again and see what happens.
5:13 Here we go, we're not adding new data
5:15 because there's already nine guitars.
5:17 So that's it, it's super simple.
5:19 We just create our session and session context.
5:22 We use the session from it
5:24 and we can do a query, get the count of items.
5:28 And if we need to insert some, we just go to session add
5:31 then finally, session commit. Done.