Jose: Hi guys!
And welcome back!
In this video,
we're going to learn about,
what is a cursor?
This is a pretty important concept
because there's a few different types of cursors,
when you use databases.
You've got SQLite cursors,
and database cursors.
So it's important to know the difference between them,
as well as understand what they're for.
So what is a cursor?
A cursor is a structure
that allows us to traverse a result set.
Very esoteric description there,
maybe not all that helpful.
Database cursors
are a thing in databases in RDBMS
that we can use to tell the database
that we want this result from the database,
or this amount of data.
But we wanna get that data one piece at a time.
And so it allows us to, you know,
tell the database we want to get this table of data
but only give me the first 100 rows.
And then we can ask it for the next 100 rows and so on.
So it allows us to traverse the results,
loading the results as we go along.
SQLite cursors, though are a different thing.
In, SQLite cursors,
we load all the results.
But, they're there to sort of help us
go over them a bit more easily.
So this is essentially what a cursor looks like.
This is a table that we've loaded using SQLite.
And the cursor is the little arrow.
So we can use the cursor
to sort of go through the results,
one by one, or 10 by 10.
However we want,
we can sort of go through them like that.
Okay!
So it's very important to distinguish
between database cursors
that are things in the RDBMS,
which SQLite does not have,
or at least we're not gonna use them.
That allow us to load results bit by bit,
so that we don't fetch the entire table
which could potentially be millions of rows
at once from the database.
So there's a difference between that
and the SQLite cursor
which is only there to help our code,
navigate the results.
It doesn't do anything to help us,
you know, not have to load all the results at once.
We're going to understand more
about what this means,
later on when we learn more about database cursors.
But for now,
just bear with me on this one.
So for SQLite cursors,
they represent that arrow,
that pointer that we saw a moment ago.
They help us iterate over the results
or go over them one by one.
Therefore, using them in for loops like this,
sort of makes sense,
when you have a cursor.
You get, you know,
you create your cursor with connection.cursor
then you use that cursor
to execute a query such as says, SELECT.
And then you can use the cursor
that has loaded that data
in a for loop like this.
And that gives you each row one by one.
So essentially, you can think of this for loop
as that arrow that is giving you each row,
or pointing to each row, one at a time.
So when we're doing something like this,
a SELECT and then an iteration.
A cursor makes sense.
It's sort of useful,
we can iterate over the cursor.
We don't have to, you know,
do anything else,
like build a list out of the results
or anything like that.
So that is interesting.
But when we're not iterating over the cursor,
such as, if we try to do an INSERT,
then SQLite cursor
seem kind of pointless, right?
Here in this code,
we are creating our cursor,
And then we're executing an INSERT.
And we're never iterating over the cursor.
So, this arrow,
is really not pointing towards anything,
because an INSERT query
doesn't even return anything useful to us.
And so in this case,
the cursor does seem a bit pointless.
And some people suggest that
this is really just a small problem in SQLite.
This cursor shouldn't really exist in all cases.
I believe that the reason why we use cursors
for both selecting and inserting in SQLite
is just for consistency.
To make your code more consistent.
Every query uses a cursor,
and that's it.
Otherwise, you'd need some queries using a cursor
or some not using a cursor.
It could be a bit confusing,
especially for new coders.
So this is just the way it's done in SQLite.
However, you may have noticed earlier on
in a previous presentation
that we didn't use a cursor.
And that's because when we do connection.execute
that creates a cursor for us.
And that saves us some typing.
And it makes a bit more sense
when doing things like inserting.
We've seen this earlier on,
here for example,
we're not creating a cursor
with connection.cursor
We're just doing connection.execute
And sqlite3 behind the scenes
automatically creates a cursor for us,
and executes the query using the cursor.
But we don't have to do that ourselves.
And again, it makes a bit more sense
when doing things like CREATE TABLE
or inserting data.
You can create the cursor yourself.
And I would normally recommend doing that.
So it's a bit clearer
when you want to use the cursor.
And when you don't want to use the cursor,
I would normally recommend you do this
so that it's a bit clearer,
that you are not getting a cursor
that is not gonna be used.
All right!
So I hope this helps understand
what cursors are for.
Gives you a little bit of information.
It's not going to be an obvious
until you start using them a bit more.
But I hope this helps.
And let's go and write some of this code in the next video.
I'll see you there.