#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.
