Hi, Serdar Yegulalp here with Infoworld at IDG in this episode of Smart Python. I'll be giving
a brief overview of the sqlite3 database that's included with python a fast and easy way to store
structured data outside of an application and reuse it sqlite3 is a tiny but powerful database
system delivered as a single binary python has habitually included sqlite3 for many versions
now so it's a convenient and powerful way to build applications that need some kind
of persistent data store sqli3 itself is one of the most widely deployed databases in the world
there's a good chance the device you're watching this video on uses it in some way
it's also one of the most dependable and consistent such products it's not a toy project
sqlite as the name implies uses the sql standard for how data is stored and retrieved i won't go
into a lot of detail about how the standard works i've put a link below to where you can read more
about this but the basic idea is that you issue commands to the database in the form of strings
and you get back results that you can iterate through and perform operations on
and one nice thing about sqlite especially in python is that apart from the sql syntax
there is very little setup involved for an application other database products like
mysql require you to install the product and go through a setup process for each database
for sqlite the setup is just a few lines of python code the first thing we need to do is create our
database and connect to it and so i have written a function here that does exactly that it connects
to a database file in our project with the name database.db or if no such file exists it will
create it and prepared for use the database connection is an object and with it we create
another object that we use to send commands and receive data this object is called a cursor
and this function here makes the connection and the cursor and then returns both of them
for use elsewhere i made this into its own file because we're going to reuse these commands a
couple of times throughout this demo so it makes sense to break these things out on their own
now here we have some code to create the structures in the database that hold data
also known as the tables again if you're not familiar with database parlance the linked
article on sql can help by the way this code up top here this just erases any existing database
file beforehand it's just something i whipped up as part of this demo you won't normally do this
after we make our connection to the database and get back our connection and cursor
objects we then pass in a command that creates an empty table named users with two text columns name
and email the execute method for the cursor object sends the database the command we have
here to create the table with those parameters the commit method ensures the results of that
command are finalized in the database we need to commit operations so that we can do things like
group a bunch of operations together under one commit for the sake of efficiency
or so that we can checkpoint a complex operation at different stages along the way
the next thing we want to do is insert some data this we do as a separate command to execute but
note here how we have formatted this command we have two variables username and user email that we
want to pass along as part of our insert operation but we don't include them inline sqlite uses a
special syntax where we use a question mark in the statement to indicate a placeholder for a variable
and then we pass the variables themselves separately as a tuple this way the cursor
object can figure out how best to format those variables for the sake of consistency and security
if we're going to run a whole bunch of similar operations we can use the execute mini method
to take a single command and pass it a whole bunch of parameters to execute it with a turn in this
case we have a users list each item with a name in an email and we pass that along to execute many
and then finally when that's done we commit everything and we close our database connection
now to get data out of the database we use the cursor to pass in a select command
which selects as the name implies rows from the database and then returns an object containing
this information that we can iterate over the first of these commands just selects all of the
columns from the user's table and then displays the results row by row the second of these
extracts the name and email as named variables and also uses a different kind of query
it's looking specifically for users with my name serdar changing data in the database
follows a similar pattern we'll pass in a command to update data and again we use placeholders for
parts of the query where we want to pass in parameters in this case i'm changing my name
in the database to use my full name not just my first name and when we do this you'll notice this
has an effect on the data that we retrieve the query that we ran before that looked for my name
doesn't return anything anymore because it was looking for a precise match on my first name only
the key thing to keep in mind with all this is how the data that we've inserted has persisted across
multiple runs of these programs it's stored outside of our program so we can retrieve it
and use it at any time and store it in a highly organized way that is fast to access and modify
it's much harder to do that than by say for instance writing everything out to a text file
and then reading it back in every time i start our program that's okay for just a
couple of basic settings but when you have a lot of structured data it quickly becomes very
unwieldy and becomes very difficult to keep it updated if you're making changes finally the
demo that i've just provided here involves working with a database directly with more
complex applications you typically want to use a framework called an orm or object relations mapper
this is a piece of software that lets you abstract your database handling so that you don't have to
manually construct these select and insert queries to execute that is worth exploring in a separate
video though since there are a number of orms available for python that are widely used that's
it for this video if you liked it please leave a comment below don't forget to subscribe to the idg
tech talk channel on youtube and for more python tips be sure to follow us on facebook youtube and
infoworld.com