-: Hi guys and welcome back.
In this video we're going to be inserting data
into SQLite database using Python.
A few things to remember, we can use the
connection to execute a query.
We don't need to create a cursor.
We can use the context manager as we saw here
to automatically commit the
changes when we're done.
And the third thing is we need to be able
to tell the query what data we want to
insert in the database.
So I'm going to get rid of this here.
And now we're going to connection.execute
and now we need to start writing our query.
So it's going to be insert into the entries table
because that's what we called it when we created
the table up there.
And now, we're going to go direct into values
since we are going to put data into both columns.
Now in here we're going to enter two strings.
For example, this is some test content, and
we can add 01-01-2020 as another one.
Remember to close the brackets at the end as
well.
And for good measure let's put a semicolon
which sometimes you don't need to put in,
but I recommend that you do.
So now we've got this here.
Remember the context manager with connection
to automatically commit this query as otherwise
you won't be able to see the contents anywhere.
So we've got that. Lets save this.
So now we can go into app.py and run this code.
And remember, as long as we don't execute
the get entries function which relies on this
variable that no longer exists.
Then this code will run.
The thing we must do first though is we have
to import the create tables function at the top
and we have to make sure to run that.
So we'll do create tables just like that.
It is called create tables. Just create table.
So, my bad. Create table right there.
Okay, so lets run this code.
I'm going to open a terminal in here
and we're going to do Python 3.8 app.py.
So now, if we open the file viewer,
we should have data.db created
inside our directory.
That's because when we connect through
the database and try to do something with it
its going to create that file for us and now
it contains the entries table.
So I can go ahead and add a new entry for today.
And it doesn't actually matter what I enter,
because the values that we're entering are hard
coded in this query.
We're not using the entry content and
the entry date and parameters at all,
so there we go.
Now, of course if I type two we're going to
get an error name entries is not defined
because we're relying on this variable that
doesn't exist.
But, because we used this context manager,
the result of executing this query will
already have been saved into the data base.
So now what we can do is open the SQLite
viewer and have a look to see if all
that worked.
So I've opened the SQLite viewer and
you can see that I've got my entries table
that we created.
If I go on browse the date from the
entries table, you can see that we get
this is is some test content
and 01-01-2020.
It looks a little bit bizarre there there
almost looks like underscores don't worry
about that. It's all correct. They are dashes
as we entered in here.
So, now that we've got this, we know that
we can insert data, the connection works,
the table has been created and so forth.
A couple of things that we want to take
care of is to make sure that if we run
this app again, we don't get this error.
Table entries already exists.
We know how to fix this error though
we add if not exists into there.
By the way, I'm going to close this viewer,
we don't really need it anymore.
So create table if not exists.
Notice how Python is automatically formatting
these strings into separate lines.
That's just to keep the line from being
too long.
If you don't like that you can keep it
all in one line in your code.
Now lets start working with these parameters
to enter the data that they use or actually
typed in.
Now when we're using SQLite, we have two
ways of doing things.
The first way is the sort of easy way
that a lot of Python developers want to
start doing at the beginning.
Which is to put the entry content in here
and the entry date in here.
Entry date there we go.
Make this an f string.
If we do this, then naturally the
entry content is going to get interpolated
into this set of quotation marks and the
entry date is going to go in there.
We're going to write into the database.
You'll see as soon as I run this
I'm going to just clear the output
and run it again.
Add the new entry today I've learned
how to avoid SQL injection attacks.
More information on SQL injection attacks in a minute.
I've mistyped that but that's okay.
Now lets go back to SQLite viewer and refresh
and you can see that we get the data that
we typed.
So, therefore this works. Right.
But, as I was saying about SQL injection attacks,
if you do this, you are at a big risk and it's
definitely a bad idea to do this.
We're going to learn more about SQL injection
attacks at the end of this section.
But essentially you are opening up to the
user typing SQL code as the content of
the entry and the SQL code will run when
typed in here.
So there's more detail at the end of this
section as I mentioned, but we want to avoid
this.
Instead, what we do normally using SQLite
is we put a question mark and then another
question mark.
So here we've got two question marks,
two things that we're going to add to
our table.
We can now remove the f string.
When we do this, when we put in the
question marks there, we need to tell SQLite
what we want to replace these question marks by.
So it's similar to doing string interpolation,
but SQLite does it.
When SQLite does it, it protects us against
this SQL injection attack.
So the way we now have to do this
is we add the comma afterwards for the
second argument and we're going to pass in
the entry content and the entry date.
So this is important here. We've got the
connection execute method here and the first
argument to that is the query we want to run
with the question marks as arguments.
Then after the comma as a second argument
to the connection executes. We're passing a
tuple of the arguments that we want to pass
into the query.
So entry content will become the
first question mark, entry date
will become the second question mark,
and everyone's happy.
So this is the way to do it.
And that's really everything.
Our database.py function was simple
and encapsulated. It's logic was hidden
from other parts of the app so we really
have to change nothing at all inside app.py
to make this work as you've seen.
So try running the app a few times insert
some data, open your database viewer, have
a look around and see what happens.
Then we will move on to selecting data
and retrieving data from the database in
the next couple of videos and we will
complete this.
All right, thanks guys for watching.
I'll see you in the next video.