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