Instructor: Hi guys and welcome back.
In this video, we're going to learn how
to connect to a SQLite database with Python.
We're going to use the SQLite Three Module
which comes with Python so there's nothing for us
to instal in order to connect to SQLite databases.
There's a three-step process to opening connection.
First, we have to import the module
because that contains everything we need
in order to actually connect.
Then we'll use the module to connect to the data file.
SQLite databases are always in data files
as opposed to you know, servers running somewhere
and so, if the data file doesn't already exist
when we're trying to connect then,
this connecting is gonna create one.
When we're connected,
we can execute SQLite queries against that connection.
So it's gonna look somewhat like this.
First, we import the module at the top then,
we open a connection with sqlite3.connect.
We give that the data file that we wanna connect to
and again, that creates data.db in this case,
if it didn't already exist
and then we use the connection
to execute queries with connection.execute.
Finally, we always want
to close the connection at the end of our programmes.
So that's something to keep track of.
Normally though, I would create the
connection in a function.
And often we want to delete
and re-create the data file and so on,
so having an easy-to-call function
that creates the data file can be convenient.
So these are something you can do
and you know, import SQLite
then create a function called createTable
and then connect there and create the table.
Really, it's not so much about opening
the connection in this function that is
the most important but
to have a function that creates the tables for you.
So, how many connections can you open?
This is a very frequent question
and the answer is you can really open
as many as you want and this is true for any RDBMS
but there are some limits
to how many connections the database server
is going to support.
So again, with SQLite, there is no server,
you're connecting directly to the file.
So, this connection limit doesn't apply
to SQLite but with things like Postgres
as we will learn later on,
there are connection limits that depend on
the RAM of the server.
We're going to learn more about that later so,
don't worry about it too much.
For SQLite, we can open as many
as we want but only one connection out
of all of the ones that are open,
can write to the database,
can modify the contents of the database at any one time.
So, if you have two connections,
one of them can be inserting new data into the database,
the other one if it wants
to insert data into the database at the same time,
it has to wait until the other one has finished.
So therefore, for single-user application
like most console apps that we're gonna
be writing in the early stages
of this course that are only used by a single-user
at anytime, having one connection for
the entire application lifetime is enough.
When you have many users
using an application at the same time,
then you may want to start adding more connections
but for now, it's not necessary.
So often for console apps,
we just create one connection at the top
of the file and then we use that connection in
the functions below it or whenever we need to.
So instead of creating a function to create the connection,
we just create the connection at the top.
Kind of like this.
We've got here, the connection equals sqlite3.connect
at the very top of the file
and then we can define multiple functions that all use
that connection because we know that none
of these functions are gonna run at the same time.
Therefore, none of them are going
to compete for using this connection to write data
or anything like that
to the database because this is a
single-user application in most cases at least.
Okay so, let's learn about transactions
because the term transaction is
pretty important in relational databases.
We can run multiple queries together in a transaction
and when you run queries in a transaction,
they still run in the order in
which you execute them but
when you put multiple queries in a transaction,
all of the queries in the transaction must succeed
or none will and so,
if you have multiple queries,
they all have to pass or succeed,
there have to be no errors or anything like that.
If there is an error in one query,
then all the queries in the transaction will be cancelled
or their, the changes that they apply
to the database will be reverted.
So therefore, transactions allow us
to group related queries together
so we're sure that they will happen together successfully.
So this concept of the transaction is
pretty important because again,
it allows us to be sure that multiple things
that we wanted to do all happened
and not one part happened
and the other didn't especially
when multiple queries are somehow related
or interdependent.
You can commit and rollback transactions.
So committing saves the database changes permanently,
so they'll be available
to read later on from another connection
or another transaction and rollback undoes the changes
so as if nothing happened.
So those are the two end states of a transaction.
When you get to the end of the transaction,
you either commit it to save the changes
or you roll it back to undo the changes
in case you've found something that shouldn't have happened.
Of course, the third option is you do neither of them
and the result is a rollback essentially,
that's the default.
If you use a transaction, you execute a bunch of queries
and then you don't commit,
nothing gets saved to the database so,
nothing happens really.
So, are we using transactions?
Yes, every time we call connection.execute,
a transaction is created for us.
Then we execute the query but we haven't been committing
or rolling back the transaction that gets created and so,
you'll see that if you execute the code
that we've shown earlier in this presentation,
creates a table or something like that
and then you open the database, the table won't be there.
We're gonna show that in a moment in the
SQLite Viewer that this doesn't really happen.
So, we must commit the transactions
in order for the data to be persisted
or saved permanently to the database.
We can do connection.commit after executing a query,
kind of like this.
Connection.execute, we create the table
and then connection.commit saves the stuff
to the database or we can use a context manager
to automatically handle committing for us and so,
this is a Python Construct,
this is the With statement, With connection.
Then connection.execute
and what happens with the context manager
is that it will execute the queries
for us in the connection.execute line
and when we get to the end of the context manager,
at the end of With connection,
then it's gonna commit for us automatically.
Remember that you can have multiple
connection.execute statements before committing
and then that just groups them all
together in the transaction.
If there is an error in any
of those statements that you're trying to execute,
then they will all be cancelled
as is the norm with transactions.
So, let's go and code this.
Let's jump into the code editor,
write this code that we've shown you
and that's gonna let us start using SQLite
as our database for this app.
I'll see you in the next video.