#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
0:02 for our database for this app.
0:05 This here is how you actually open your connection
0:07 to the app and to the database
0:10 and then do something and close it off.
0:14 So we've got our connection.
0:16 We've covered this in SQLite3 before
0:18 but I'll give you a quick run through.
0:20 We connect to the database, okay?
0:22 We create the cursor which allows us
0:23 to actually write over the database.
0:25 We then execute some sort of code.
0:28 We commit it and then we close it.
0:31 Now as we know from our main menu,
0:33 there are quite a few things
0:34 that are going to need this connection, okay?
0:36 There's going to need, we need to add a room,
0:38 we need to view the room, we need to calculate totals,
0:41 we need to add inventory.
0:43 There's a lot of things there
0:44 that will need this connection.
0:46 Including just listing out the names of the rooms, right?
0:49 So you can't really have this much code
0:53 in a function every single time you need
0:56 to connect to the database.
0:59 It's not Pythonic and it's just a waste of code, right?
1:03 So what I've done is for this app,
1:05 I'll leave this up on the screen.
1:06 I've created a generator.
1:08 Let me paste it in here.
1:13 Alright, so this is called access_db, this function.
1:17 And it's wrapped in context manager,
1:19 so in order to use it, you would have
1:21 to from contextlib, import contextmanager
1:28 Whoops.
1:29 contextmanager, okay?
1:32 And what this is allows us to do is
1:33 it allows us to generate a cursor, okay?
1:37 So the first thing it's going to do, it's going to try.
1:39 It's going to connect to our database.
1:43 It then creates the cursor.
1:45 Again, we're using this lineup here, okay?
1:47 And then it's going to yield that cursor, okay?
1:50 And what does that mean?
1:51 It means it's going to pass back that cursor
1:55 to whatever called this function, okay?
1:58 So it yields this cursor out.
2:00 Now, just a quick demonstration
2:03 of what one of these functions looks like.
2:06 This is the list rooms function
2:09 that we saw previously, okay?
2:12 Now what it does is it says with access_db as cursor, okay?
2:18 So it's calling our access database function here.
2:25 And it's assigning that as cursor, okay?
2:29 So when it yields cursor, whatever is yielded
2:32 by this generator is assigned to cursor, okay?
2:37 So that way we're able to use this cursor here
2:39 that's generated by this try statement.
2:44 We're able to use it here and so it's going
2:46 to do cursor.execute and run our SQLite query.
2:52 And then once everything in this with statement is complete,
2:58 we move back up here into this,
3:01 into our generator, and we finish it off.
3:05 It's just finally, okay?
3:06 So this finally is dependent on
3:08 this yield coming back, okay?
3:11 So this returns a cursor, this yields a cursor
3:14 but this here, this function is generated
3:17 doesn't complete until whatever was
3:20 using this yielded cursor completes, okay?
3:25 So, yielded cursor into here.
3:27 Into this with statement.
3:29 This with statement uses the SQL.
3:32 Runs this quick for loop with this list
3:34 to create it and then once it's closed off,
3:39 we go back here.
3:41 We commit the change and then we close it.
3:44 And now you can see, we don't need to have
3:46 this specific code, the connection,
3:48 the cursor, the commit or the close.
3:51 We don't have to have that in every single function
3:55 that calls or that needs to talk to our database.
3:58 All we really want from this
4:01 database call, is the cursor.
4:04 This cursor is what's important
4:06 and that's what will change from function to function.
4:09 So by putting all of the unnecessary duplicate code
4:14 into its' own function up here,
4:16 we're being more Pythonic
4:18 and we're going to then only return
4:21 what we need and the yield what we need
4:23 to the functions that need to talk to the database.
4:26 Okay, so that's a quick, quick overview
4:29 of how we're using a generator
4:31 in this specific code base,
4:33 in this app to talk to our SQLite3 database.