-: Hi guys and welcome back.
In this video we're going to look at how to
connect to SQLite using Python.
We've already seen the presentation that covers
the theory behind doing this.
Now we're just going to write the code for it.
Just as a reminder, we've got here our ab.py
with our two functions. One to prompt
for a new entry to the user, and one to display
entries given as a parameter.
And then we've got our menu that just asks the
user over and over for their options
and also gets the entries from the database
to display in here.
The prompt new entry adds entries to the database as well.
So we've got our two functions add entry
and get entries in database.py that avenue entry
into this entries list or get the entries
form the entries list and return it.
Now we're going to modify these functions so that
instead of a list, we've got a SQLite table.
So the first thing we're going to do
as discussed in the presentation is we are going
to go ahead and create a connection.
So we do connection equal SQLite three.connect
and the file is going to be data.db.
Once we do this, we no longer need
the entries list, but we do need
to import SQLite three instead.
So now we've got that.
Let's save.
Notice that any underlines and stuff should disappear
at this point. If you get any errors
like SQLite three module not found or something
like that and then ask away in the course Q&A
for this because SQLite three should come with Python.
There should be nothing that you have to instal
at this point.
Now notice that our functions do complain that
our entries list doesn't exist and that's because
we now have to change this to use the connection
and data.db for the table.
So that we don't have to go into data.db
ourselves with the SQLite viewer, I'm also
going to create a third function to create
the table that we need.
So we'll have a create table function and in here
we're going to do connection.execute and we will
create table entries
and we will give the two columns that
we need the content and the date.
So content text and date text.
Remember from the presentation that this is
how we execute a query using Python.
Now that we're connected to SQLite three
we can go ahead and run the create table function.
However, it's important to remember that we need
to commit the transaction that this runs in
as we've learned.
So it's important to put this in the context
manager like that
or alternatively, you can go back
and do a connection commit.
Either of those is fine, I do prefer the context
manager approach though, so I'm going
to go for that.
Now remember if you run the code at this point
it's not going to work because we've got rid
of the entries list.
We need to learn a little bit more before we can
add content to these functions that uses a database.
So over the next few videos we're going to learn
how to insert data
and how to retrieve data
and were going to fill in these functions as we go along.
But I'm sure you can imagine that this is going
to be fairly straight forward.
There's going to be one query that runs in here
to add data to the database
and there's going to be one query that runs in here
to retrieve data from the database.
But we just have to learn a few more things before
we delve into that.
So, thank you for watching.
I'll see you in the next video.
Let's get to it.