#100DaysOfCode in Python Transcripts
Chapter: Days 88-90: Home Inventory App
Lecture: SQLite3 database usage
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
As I've said before, we're using SQLite3 for our database for this app. This here is how you actually open your connection
0:08
to the app and to the database and then do something and close it off. So we've got our connection. We've covered this in SQLite3 before
0:19
but I'll give you a quick run through. We connect to the database, okay? We create the cursor which allows us to actually write over the database.
0:26
We then execute some sort of code. We commit it and then we close it. Now as we know from our main menu, there are quite a few things
0:35
that are going to need this connection, okay? There's going to need, we need to add a room, we need to view the room, we need to calculate totals,
0:42
we need to add inventory. There's a lot of things there that will need this connection. Including just listing out the names of the rooms, right?
0:50
So you can't really have this much code in a function every single time you need to connect to the database.
1:00
It's not Pythonic and it's just a waste of code, right? So what I've done is for this app, I'll leave this up on the screen. I've created a generator.
1:09
Let me paste it in here. Alright, so this is called access_db, this function. And it's wrapped in context manager,
1:20
so in order to use it, you would have to from contextlib, import contextmanager Whoops. contextmanager, okay? And what this is allows us to do is
1:34
it allows us to generate a cursor, okay? So the first thing it's going to do, it's going to try. It's going to connect to our database.
1:44
It then creates the cursor. Again, we're using this lineup here, okay? And then it's going to yield that cursor, okay? And what does that mean?
1:52
It means it's going to pass back that cursor to whatever called this function, okay? So it yields this cursor out. Now, just a quick demonstration
2:04
of what one of these functions looks like. This is the list rooms function that we saw previously, okay?
2:13
Now what it does is it says with access_db as cursor, okay? So it's calling our access database function here. And it's assigning that as cursor, okay?
2:30
So when it yields cursor, whatever is yielded by this generator is assigned to cursor, okay? So that way we're able to use this cursor here
2:40
that's generated by this try statement. We're able to use it here and so it's going to do cursor.execute and run our SQLite query.
2:53
And then once everything in this with statement is complete, we move back up here into this, into our generator, and we finish it off.
3:06
It's just finally, okay? So this finally is dependent on this yield coming back, okay? So this returns a cursor, this yields a cursor
3:15
but this here, this function is generated doesn't complete until whatever was using this yielded cursor completes, okay? So, yielded cursor into here.
3:28
Into this with statement. This with statement uses the SQL. Runs this quick for loop with this list to create it and then once it's closed off,
3:40
we go back here. We commit the change and then we close it. And now you can see, we don't need to have this specific code, the connection,
3:49
the cursor, the commit or the close. We don't have to have that in every single function that calls or that needs to talk to our database.
3:59
All we really want from this database call, is the cursor. This cursor is what's important and that's what will change from function to function.
4:10
So by putting all of the unnecessary duplicate code into its' own function up here, we're being more Pythonic and we're going to then only return
4:22
what we need and the yield what we need to the functions that need to talk to the database. Okay, so that's a quick, quick overview
4:30
of how we're using a generator in this specific code base, in this app to talk to our SQLite3 database.