query

Python SQLite Tutorial: Complete Overview - Creating a Database, Table, and Running Queries



Sharing buttons:

hey there how's it going everybody in

this video we're going to be learning

how to work with SQL light now as filo

light is extremely useful when you need

some database functionality and don't

want to spin up a full-fledged database

like my sequel or Postgres so you can

use SQL Lite for small to medium sized

applications where your database is just

going to live on disk or you can use it

for testing and prototyping out an

application and if you have the need to

move up to a larger database then you

can later port that over and SQL Lite is

actually part of the standard library so

there's no need to even install anything

and we can just start working with it

right out of the box and it's extremely

easy to use because your database can

just be a simple file or it can even be

an in-memory database that just lives in

RAM now one thing I want to point out is

that this video is just going to focus

on using SQL Lite and not how to program

the SQL language itself so I'm going to

assume that anyone watching this video

has some basic knowledge of SQL and if

you don't know SQL then I do have a

short series on getting started with the

basics okay so let's go ahead and get

started so first of all I have some

sample code here so that we have

something to work with when creating our

SQL Lite database now this sample code

is from my object-oriented series and if

you don't know how to use classes or

what this code is doing then don't

really worry about that you don't have

to understand it for this video it's

just going to be something that we can

add to our database and actually have

two files here in our current directory

one of these is the employee PI module

that contains our employee class that

we're looking at right now and the other

file here is called SQLite demo and

that's where we're going to learn how to

use SQLite so if I open up that SQLite

demo then we just have a blank script

here so right now let's not worry about

the employee class and instead just jump

right into working with SQL Lite let's

say that we want to create an

application where we have employees and

we want to be able to add update and

delete employees from our database as

well as being able to grab employee

information from that database so in

order to prototype out this application

let's use SQL Lite so this is in the

standard library so we can just go ahead

and import this without installing

anything so we can say import SQL Lite 3

and there's only one L there and now we

need a connection object that represents

our data

so I'll go ahead and create a variable

here named Khan and set that equal to

SQL light 3 dot Connect now within the

connect method here we can either pass

in a file where we want to store our

data or we can even make an end memory

database do an in-memory database we'll

have a string here and say : memory and

another : so that's how you would do an

in-memory database but for our example

we're going to instead use a file name

and for this file name we're going to

call this employee dot DB so now with

just these two lines of code if I go

ahead and run this then I know that the

files in my directory here are a little

small for you to see but it did create

this employee dot DB file here in our

directory now I want to point out that

if you're following along with me and

you're using sublime text like I'm using

then I believe sublime text hides these

dot DB files by default and I have

unhidden that on my machine so if you

don't see that then you might not want

to actually check the file system

because that files most likely there

even if you don't see it within sublime

text

now that dot DB file isn't something

you'll be able to open up and understand

it'll look just kind of like gibberish

if we look at the contents of that file

but sequel Lite knows what to do with it

and that's all that matters so when we

run that connect method it creates that

file even if it doesn't exist and if it

does exist then it just connects so I

can go ahead and run this code again and

you can see that we didn't get any

errors anything even though that

employee dot DB file already exists ok

so now that we have a connection let's

create a cursor which allows us to

execute some SQL commands so to create a

cursor then we can just create a

variable here and I'm just going to call

this variable C and I'm going to set

this equal to Khan for our connection

dot cursor now that we have a cursor we

can start running SQL commands using the

execute method so we know that we want

an employee's table so let's go ahead

and create that so let's create an

employee table that holds an employee's

first-name lastname and pay so to do

this we'll do C for the cursor that we

created C

dot execute and then the SQL command

that we want to run so we'll say create

now one thing to point out here is that

I'm wrapping this SQL and three quotes

on each side and if you're not familiar

that is called a docstring and it's

going to allow us to write a string

that's multiple lines without any

special breaks or anything like that

some people like to do this differently

than using the doc strings but this is

how they do it in the Python

documentation so I'd say that it's fun

and when I'll write a statement that's

just a single line then I'm just going

to use the regular quotes for a string

and not the doc string so just in case

you are wondering what I was doing there

so anyways the command that we want to

run is create table and we want to

create an employee's table and now let's

put in some parentheses here and specify

our columns so our first column we want

to be the first name so we'll just call

that column first and now we want to

give this column a data type so SQL Lite

has different data types than what you

might be used to with other databases

and there's not very many of them here

and I have the data types page pulled up

here in the documentation and you can

see here under section 2 that there are

basically five different ones to work

with here so we have null integer real

text and blob so for our first name

column we're going to use text and for

the last name column we're going to use

text and then for the pay we could

choose either real which is a floating

point value or an integer and I think

I'm going to go ahead and just use an

integer even though that doesn't give us

decimal point values so with that said

let's go ahead and set these so we want

first equal to text and then we want a

last for the last name we want to set

that as a txt also and then we also want

a pay column and to pay let's set that

as an integer now I'm going to go ahead

and actually indent this over to be even

with that and save that so now that we

have our SQL statement to create our

table now let's do a couple more things

before actually running this and what I

want to do here

as I want to do a con for the connection

not commit now realize there that I'm

doing a connection not commit and not

the C for the cursor so this commits the

current transaction and it's easy to

forget this step a lot of people leave

that out so if you aren't seeing

something in the database that you think

should be there and make sure that

you're committing your changes and also

at the end here it's a good practice to

close the connection to the database and

we can do that with a con dot close so

now let's go ahead and execute all of

this code and we didn't get any errors

when we ran that so that's good so that

created our employees table and that

will be obvious if we try to run this

code again

because if I try to run this code again

now you can see that this time we did

get an error and if we look at the error

here it's saying that the table

employees already exist so that's good

so with that little bit of code we're

already able to interact with the

database we didn't have to install

anything or start any servers or

anything like that

so let's comment out that line where we

create our employees table and start

adding some data to this database so now

that we have that table let's add an

employee to that database and for now

let's just go ahead and type this in

instead of using our sample employee

class that I showed you earlier so above

our commit statement here let's say C

for our cursor C not execute now I'm

just going to use regular single quotes

because this will fit on one line I'm

just going to say insert into employees

values and now I want to fill in those

that first column last column and pay

column so I'll do the name as Cori and a

comma last name as Schaffer and for pay

I'll just make up 50,000 there so now if

I run this code then we didn't get any

error so that's good and to the best of

our knowledge that data was inserted

into our employee database but let's

find out let's go ahead and find out by

querying the database for that employee

so to do this we're going to execute a

select statement so I'll comment

out this insert statement here and now

let's create our select statement so at

first I'm just going to hard-code in of

what we're looking for and I'll do that

by saying C dot execute and now we're

going to type in our sequel command so

I'll so I'll say select star from

employees and I'll put in a where clause

here to find that employee so I'll say

where last equals Schaeffer and save

that now that select statement is going

to provide some results that we can

iterate through so to iterate through

that query result then we can use a few

different methods here so we have C dot

fetch one and what that will do is it

will get the next row and our results

and only return that row and if there's

no more rows available then it just

returns none so we have fetch one we

also have fetch many and this takes an

argument of a number so say you said

fetch many five so what that will do is

it will return that number of rows as a

list and if there are no more rows

available then it will just return an

empty list and lastly we have fetch all

that doesn't take any arguments and what

that will do is it will get the

remaining rows that are left and return

those as a list and if there's no more

rows then it will return an empty list

so for our example here let's just say

fetch one because it should only be one

result and actually let's go ahead and

print out that fetch one and save that

so now if we run this then we can see

that it returned our one entry that we

inserted into the database and if it

can't find any results so for example if

I change this last name here to Smith

instead and rerun that then you can see

that fetch one just returned none

because it didn't find any results so

now I'm going to go ahead and change

that back to the way that we had it and

rerun that so you can see that fetch one

just gave us that one result that one

row if we instead did a fetch all which

we talked about earlier now if I run

that

we still only have one result but now

it's within a list so now is add one

more employee by rerunning our insert

statement here with some different

values so instead I'll say Mary and I'll

keep the last name the same and I'll

just put 70,000 there now I believe that

when we run our select statement here

that it would do an auto commit of our

insert above but just to be explicit

let's go ahead and put a commit here so

I'll do a con not commit and then after

we commit that insert then it will run

this select and since both of these

employees have the same last name and it

should get both of those with that

select so if I run this and now you can

see that our list from fetch all now has

those two entries that have found okay

and just so we don't insert any more

values into our database right now I'm

going to comment out this insert

statement here so right now we have

typed in the values that we are

searching for directly into our select

statements but the way you'll most

likely be using this in Python is that

you'll have some Python variables and

you want to put the value from those

variables into your query so to see an

example of this let's start using that

employee class that we looked at before

and like I said if you don't know what

this code is doing then don't worry too

much about it this class just allows us

to create employees and when we create

an employee it comes in and it sets the

first name last name and pay and the

email and full name use those variables

to create those attributes but we aren't

going to use those in this example so

now let's switch back to our sequel Lite

demo here and I'm going to import that

employee class and I can do this because

that employee module is in the same

directory as the script that we're

currently in so I can just say from

employee import that employee class and

I'm going to spelled that there so I'll

take that out so now above my insert

statement here I can create a couple of

instances of this class so for example I

can say employee 1 is equal to employee

and then I can pass in values for

first last and pay so I'll just say John

Doe and for the pay I'll say eighty

thousand and now let me copy this and

make a second instance of this class so

I'll call this employee to and this will

be Jane Doe and we'll do 90 there and we

can access those first last and pay

attributes by saying so we could do a

print and I'll do the first one here

I'll do print employee dot first and

that will get the first name attribute

of that instance and to get the others I

could say dot last and hey so if I run

that then you can see that it did print

out the first name last name and pay of

that instance and the extra print

statement there is just the results of

our select query and we still have that

print statement in down there so

currently these two new employees that

we created or just Python objects and we

haven't inserted them into our database

yet so how would we do this so first

let's add John Doe to the database now

you might be tempted to do this using

stream formatting and let me show you an

example of what I mean here I'm going to

take out these print statements real

quick and I'm going to uncomment out

this insert statement so let's say that

with this insert statement we instead

want to insert all those values from

employee one and like I said you'll

probably be tempted to use string

formatting now you probably see me use

string more formatting and my videos

before but if you're not familiar with

it then basically we're using braces as

placeholders so instead of hard-coding

these values in i'll put braces instead

for each of these and then we can use

the format method to populate those

placeholders so for the first

placeholder we want we want employee one

dot first for the second one we want

employee one last and for that last

placeholder we want employee one dot pay

now if you have seen this before and if

you use string formatting a lot this is

actually a bad practice when using

databases in python and that this is the

case for just about any database that

you decide to use if you are accepting

any values from

user so say from like a website or

something like that for example then

this is vulnerable to SQL injection

attacks and basically all that means is

that there are values that I could set

these variables equal to that could

break the entire database and the reason

for that is because it's not properly

escaped so let me show you the correct

way to do this and there's actually two

different ways and I'll show you both

ways here so the first way to do this is

instead of using our regular brace

placeholders here we can instead use

question marks and this is a DB API

placeholder and you also no longer need

the quotes there to specify that it's a

string because it will know that by the

values that we pass in so I'm just going

to do three question marks here as our

placeholders and now I'm going to first

I'm going to go ahead and copy these

values and now I'm going to totally get

rid of the dot format and instead what

we're going to do is we're going to pass

in another argument to this execute

method so I'll just put in a comma there

and for the second argument I'll pass in

a tuple of all the values so I'll put a

tuple passing all those values now one

thing that I do want to note here is

that even if you're only passing in one

value into a placeholder you still need

to put it within a tuple which can look

a little strange and I'll show you how

that looks when we run our select

statement in just a second so I'm not

going to run this quite yet I'm going to

show you the second way that we can use

the second proper way to use these

placeholders so I'm going to do another

insert statement for our second employee

here Jane Doe and this second way of

doing the proper placeholders is my

personal favorite so instead of these

question marks we're instead going to

put a colon and a name describing the

placeholder so for example I'll do colon

first and colon last and then colon pay

and now we're still passing in a second

argument to the execute method but

instead of a tuple it's going to be a

dictionary and the dictionary keys are

going to be the names of each of these

placeholders in our SQL and the values

will be what

we want those placeholders to be so in

this example this would be like I said

this will be a dictionary now

and now our keys will be all of these

values that we want to fill in and the

values of those dictionaries will be

what we want those keys to be equal to

or what we want those placeholders to be

equal to so first so let me go ahead and

copy this a couple of times so now I

also want this to fill in that last

placeholder and we want that to be not

last and we want to fill in that pay

placeholder so we'll do that pay and now

we wanted to insert the second employee

here so instead of employee one this is

going to be employed - now this line is

getting a little long here so if you

wanted to break this up onto another

line you could I think I'm just going to

leave it the way it is for now now even

though this one is longer the reason

that I like this method of doing the

placeholders is because when you only

have one placeholder value I think it's

a lot more readable and we'll look at

that when we run our select ment

statements but for now let's go ahead

and run this code and get these

employees added to our database I'm

going to go ahead and run that and we're

still printing out the Select statement

from before but now I'm going to go

ahead and comment out these insert

statements so now for this select

statement instead of searching for the

last name of Schafer

let's also run a select statement that

searches for the last name of DOE and

we'll go ahead and use both methods of

using placeholders just like we did just

so that we can get the hang of how to

use both of those and how they both look

so instead of hard-coding in Schaefer

here I'll instead use the question mark

placeholder that we use before so I'll

say last equals question mark and now

the value that I want for that will just

be the string Schafer and like I said

this is one value so we still have to

make it a tuple so we have to put a

comma here within those parentheses to

turn that into a tuple now that's why I

said that the question mark approach

looks a little strange with one value

because you still have to put it inside

a tuple and that comma is needed or else

you'll get in

err and I've just always thought that it

looks a little strange okay so using the

other approach so I'm going to go ahead

and copy this these lines here and we'll

do another select statement below but

now we're going to use that other

placeholder approach and we'll do

another select searching for the last

name of dough so to do that we can say

where last is equal to : last and now

the way that we fill out this second

argument is it's a dictionary and we

want to say that use this key last which

code is going to fill in that

placeholder and we say that we want to

search for the value of dough and like I

said I think that this is a little more

readable because even with the one value

it's a little more obvious that we're

saying okay we want this last

placeholder here to have the value of

dough so now if I go ahead and run the

code that we have now then you can see

that our fetch all after the first query

gave us the two entries that we added

earlier in the video and the fetch all

after the second query gave us the

values that we added using the instances

of our employee class up here okay so

we're just about finished up but let me

show you one more thing here that I

think you'll find useful in working with

SQL Lite before I said that when we

first make our connection up here at the

top you can actually set this connection

equal to memory and the way that we do

that again is I'm going to go ahead and

fill this in it's a : memory and then

another : now what that's going to do is

it will give us a database that lives

and ran and that's useful for testing if

you want a fresh clean database on every

run so for example now I can uncomment

out my create table here and also I'm

going to uncomment out these insert

statements so now if I run this code

then you can see that we didn't get any

errors and that's because it starts

completely fresh so every time it

creates this table and inserts these

employees from scratch and you can just

run this multiple times and not get any

errors

like you know the table already exists

or anything like that and it also want

won't insert multiple values since it

starts fresh every time so I'll go ahead

and rerun this again

and you can see that we got the same

result now since this did start from

scratch one of our select statements

here isn't returning any values because

we overrode those insert statements from

earlier so there are no longer any

employees with the last name Schafer so

the end memory database is nice when

you're testing and you don't want to

keep deleting a database file over and

over and over it just automatically

gives you a fresh slate and when you're

ready then you can just pass in a file

and then your database will be stored

just like we did earlier in the video so

with our database and memory let's

quickly prototype out a basic

application where we use our created

table to insert select update and delete

employees from a database just so we can

tie everything together and also show

you a little trick when we do this to

make our executions more pythonic so to

do this I'm going to create four

functions here right above where we

create our employees and just so you

don't have to watch me type these out

I'm actually going to grab these from my

snippets here so I'm going to copy these

over and I'm going to paste these right

above our creative employees so we're

going to have some very simple functions

here where we're going to be able to

insert employees to the database get an

employee by their name update and

employees pay and delete an employee so

for the insert employees function I'm

going to copy and paste one of our

insertions from down here and just

modify it slightly so I'll copy this

second insertion here and put it in here

now instead of inserting this exact

instance now I'm going to insert the

employee that we pass here into this

function which is EMP and here's a

little tip for making your SQL Lite code

a bit more pythonic it's kind of a pain

that we need to remember to commit these

after every insert update or delete now

if you know about contacts managers

using the with statement then you might

be wondering if there's a way that we

can use these with SQL Lite and there is

so if you don't know about context

managers basically they're a way for us

to manage a setup and teardown of

resources

automatically it's common to see these

when using things like files also

because people don't want to remember to

close files every time they open one and

with SQL Lite connection objects can be

used as context managers that

automatically commit or rollback

transactions so transactions will

automatically be committed unless

there's an exception and then it will

automatically be rolled back so to do

this with SQLite we can say with con

which is our connection and then within

this block we just want to put our

execute statement and now since we're

executing it this insertion from within

our context manager we no longer have

the need for a commit statement after

this so now this is done so now for our

get employee by name function let's grab

our select statement from down here

towards the bottom and I'll just grab

this one here and paste that in now

instead of searching specifically for

the last name of though let's instead

search for the last name that's passed

here into this function which is this

last name variable so I'll remove dough

and search for that last name that gets

passed into that function now our select

statements never needed to be committed

so this doesn't need to be within a

context manager like our inserts updates

and deletes now if we wanted to return

just one employee then we could do a

fetch one but let's go ahead and return

all of the employees with this last name

so we'll say return C dot fetch all so

now I think we're kind of getting the

hang of how this works so instead of

watching me type in the rest of these

functions I'm going to go ahead and just

grab these completed functions here from

our snippets and I'm going to go ahead

and paste these in here now just a quick

look at what our functions are doing

here

this update pay function takes in an

employee and a pay and we are using a

context manager here since we're

executing an update statement and we're

basically just setting the employees pay

where their first name and last name

equal the first name and last name of

the employee that we pass in and for our

remove employee here we're using a

context manager again since we're

executing a delete statement and we're

just deleting an employee where the

first-name and

last name equal the first name and last

name of the employee we pass in so now

let's delete all of the code from

earlier and instead use these new

functions so I'm going to keep the

employees that we create there now I'm

going to delete everything except where

we close our connection so now I can

insert both of these employees into the

database just by using our new insert

employee function so I'll insert the

first employee and then I'll copy that

and I'll insert the second employee and

save that and now just like earlier

let's grab all of the employees with the

last name of those so I'll say employees

equal get employees by name and we'll

cert we'll pass in no for that name and

then we will just go ahead and print

those employee results so let's go ahead

and run this and see if this is working

up to this point okay so we got both of

our employees that we inserted so now

let's update the pay of one of our

employees so we'll we'll set so I'll do

an update pay and we'll set employee

two's pay - let's just say 95 thousand

and last thing let's also delete an

employee so we will use our remove

employ function and we'll remove

employee one and now let's rerun that

same get employees by name after we do

all these updates and deletions and save

that so now if I run this then we can

see that the second time we print the

results that John Doe has been removed

and Jane those pay was updated to 95

thousand so that kind of gives you an

idea for how you can use these functions

to do this work for you so that you

don't have to keep writing these same

statements over and over so I think

that's going to do it for this video

hopefully now you have a pretty good

idea for how you can get up and running

with SQLite now there's plenty more to

do with SQLite that we didn't cover in

this video such as doing bulk inserts

and things like that

so definitely give it a look once you

have everything from this video down

then picking those skills up will be

easy now another great thing about

SQLite is that it also works with SQL

alchemy now if you don't know what SQL

alchemy is it's a popular ORM for Python

it that abstract away a lot of

differences between databases and I'll

probably be doing a video on that and

the

as well but you could use SQL alchemy

with SQL light to get everything

prototype doubt in your application and

when you're ready you could easily just

replace that with a Postgres or my

sequel database without changing hardly

any of the code so if anyone has any

questions about what we covered in this

video then feel free to ask in the

comment section below and I'll do my

best to answer those now if you enjoy

these tutorials and would like to

support them then there are some ways

you can do that the easiest way is to

simply like the video and give it a

thumbs up and also it's a huge help to

share these videos with anyone who you

think would find them useful and if you

have the means you can contribute

through patreon and there's a link to

that page in the description section

below be sure to subscribe for future

videos and thank you all for watching

you