Jose: Hi guys and welcome back.
In this video we're going to be working with
Python and SQLite to retrieve data from our data base.
We've already looked at the presentation
and we know how the select statement kind of works.
We're now going to go in here and
replace this get entries function with our new one.
So, as we know, we can create cursors
to hold the result sets,
but we've also learned that
connection execute does that for us automatically.
So, what do we have to do?
Well, lets start at the beginning.
The first thing is that we don't need
the context manager when were doing get entries
because we're not changing the data base at all.
So therefore there is nothing to commit or roll back.
So we don't need that in here.
The next step is creating the cursor or getting the results.
We know that we can do connection.execute
and then something like select star from entries
in order to run the query and we know that
connection.execute automatically creates a cursor for us.
What we haven't discussed is that it actually
returns that cursor so you can do something like this,
cursor equal connection.execute select star from entries.
If you do this,
connection and execute creates the cursor,
uses it to run your query,
loads the results into the cursor
and then gives it back to you
so that you can assign it to a variable,
so it's pretty handy.
The other option, instead of this,
was to do something like this,
cursor equal connection.cursor
and then cursor.execute,
select star from entries.
Again you can do either this
or this one.
You don't need to do both
I believe this ones a bit shorter,
and but this one is a little bit clearer of what's going on.
Totally up to you which one you want to use.
I'm gonna go ahead and keep the connection.execute one
because I feel like when you are signing into a variable
it's pretty clear that it is giving you a cursor back.
So now that we've got the cursor
and we've executed the query,
it is pointing at the very first row of results
returned by the data base.
So we just need to use the results.
We can use the cursor to either,
fetch one row and load it
and give it to us as a variable
so we can do something like cursor.fetchone,
and that is going to essentially give us the first row
and move the pointing arrow to the second row.
So if we do cursor.fetchone again,
that is going to give us the second row
and move the arrow to the third row.
Of course doing this gives us the value back,
we need to either print it,
or assign it to a variable, or return it, etc.
So we're not going to be doing that because
what we want to do here is we want to
allow the caller of this function,
which is app.py,
to then iterate over it in a for-loop.
So we don't want to be getting individual rows.
Alternatively you can do fetchall
and that gets all of the results from the cursor
puts them into a list
and then you can either return that
or put it into a variable, etc.
So maybe this is what we want to do, return that.
That is going to, again, get all the results,
put them in a list and give them back to the caller.
Which means that get entries
down here would become that list,
then it would get passed to view entries.
So entries would become that list
and then we can iterate over the entries and entries
printing out each piece of information.
But as we learned earlier on
a cursor is precisely for
iterating over the results.
So we actually can just return the cursor,
we don't need to worry about fetching the rows,
it can do that for us as it goes over it in the for-loop
so it's smart enough to do that for us.
As you iterate over a cursor,
which is what this would be,
each entry in the for-loop is one row in the results.
It's important to know that each row in the results actually
is going to be a tuple,
it's not going to be a dictionally.
We're going to have to change this
so that instead of accessing the date and the content
we access index one and zero.
Remember that the content is the
first column in our data base,
so therefore, the select statement is going to retrieve it
in that order.
Entry zero is the content, entry one is the date.
Going back to the database.py,
there is a further improvement,
and I'm making air quotes here, I know you can't see me,
but you could just return this like that.
I think that when you do something like this, it's shorter,
but its also become a little less obvious that
you're actually getting a cursor back
so I would actually advise against doing this.
I think you should either do this,
to make it clear that you're getting back a cursor,
or you should create your cursor yourself
and then return the cursor.
This is a perfectly valid option,
I think this is clear enough
so I'm going to stick with that.
We can make SQlite give us a dictionary like result
instead of a tuple when we are executing a select statement
and so that we can go back to our for-loop
and access the dates
and the content as we have before.
So that's something we can do
and all you have to do in order to do that is
to tell the connection that
when it is getting rows from the database,
to not use the default method of getting rows
and instead use a different way of doing them.
In order to do that
we have to tell SQlite how to construct each row.
By default SQlite is going to construct a row
just by getting a tuple, a python tuple,
and putting each column of data into the tuple.
That's why we end up with a cursor of tuples.
But, if you wanted to get a cursor off something
like a dictionally,
then you can do connection.row underscore
factory equal SQlite3.row with a capital 'R'.
Now whenever SQlite3 is getting rows from your table
it's going to use a SQlite row instead of a tuple
as your holder of each row.
That means that when we print them out,
we can now, once again, access the date and the content.
Totally optional,
if you don't need to access the date on the content,
or you don't want to access the string properties of a row
and you're happy enough just using the indices
like zero and one, then avoid this.
Its going to be a little bit slower
and it's going to be a little bit more confusing generally,
so if you don't need the named axis,
just don't do this and you'll be good to go.
All right, lets run our code and see what happens.
We've got here the app.py,
now we can view the entries that are there already,
the ones we had in our database before.
And so you can see that
we've added some earlier on
but we can add the new ones as well.
And so what have we learned today,
today we've learned
how to use SQlite3.row
to get named access to row fields.
And the date is the second of January, 2020.
Its actually not but, you know,
I can't think of any other dates at the moment,
I can't remember today's date, so
and this is what we've got.
Now we can go ahead and view the entries
and you'll see that they come back
in the order that we entered them
and this one is there now, as well.
So congratulations,
we have created our first app that uses SQlite
to store and retrieve data.
So we started on this journey
of being able to use databases from our Python programmes.
There is so much more we can do
and were going to be learning a lot
of what you can do throughout this course.
So I hope you guys are as excited as I am
to go through the rest of this course content.
Thank you for watching,
and ill see you in the next video.