Hello guys.
So we are going to continue the discussion with respect to Python.
And in this video we are going to discuss about SQL and SQL Lite.
Now first of all, we will also understand the difference between SQL and SQL Lite.
So first of all, we'll just go ahead and understand the definition, what exactly it is.
And I hope everybody knows about SQL Structured Query Language.
And you may have also written a lot of code maybe in your company, even though you are a fresher,
that you have that specific subject in most of your syllabus.
Right.
So in this video I will be showing you how we can work with SQLite database, how you can connect it
with the help of code, how you can insert records, how you can probably perform all the Crud operations
with respect to this.
So SQL that is Structured Query Language is a standard language for managing and manipulating relational
databases, and SQLite is a self-contained, serverless and zero configuration database engine that
is widely used for embedded data systems.
Right now, the basic difference over there is that SQL is completely a huge thing, right?
It is a complete application over there, which will actually help you to manage the relational databases
that you really have lot of configuration that you really need to perform.
It is not serverless.
You know, you need to probably set up that entire server and do everything.
But you know, the SQLite, which is a lightweight version of SQL without any configuration, you can
actually create this particular database and even store it in your local system, right?
That is the reason it is used widely used for embedded database system itself, right?
So simple.
It's just like a file.
You'll be able to see that I'll be able to store it in my local machine also.
So first of all by default in Python three right SQLite comes installed.
So in order to first of all import it I will just go ahead and write import SQLite three okay.
And for this I will go ahead and select my kernel.
Please make sure that you do that and I will just go ahead and execute it okay.
So this SQLite three we will try to probably create by using this particular library.
We will try to create a database.
We will try to insert the record and do multiple things.
So first of all let's connect to an SQLite database.
So we will first of all go ahead creating a database.
Right.
And we definitely require that SQLite database.
How to probably go ahead and create a database.
Over here we'll use the SQLite three dot connect.
I'm just going to use this connect or function.
And then let's say I want to go ahead and create an example dot db uh database itself.
Right.
So this will basically be my connection over here.
Right.
So if I go ahead and execute it we will be able to see this will be my connection.
And uh connection is not defined okay.
I made some spelling mistake.
Now it is fine.
So this is nothing, but it is a SQLite connection at this particular location.
Let's see whether in my folder location this particular DB file is created.
Yes.
The answer is very much clear.
Here you can see my DB file is basically created and that is the reason.
As I said, we'll be able to store this without any configuration.
Will be able to create it in our local system.
Right.
So it will be very, very handy when we work with embedded system altogether.
Right now we will go ahead and create a cursor object, which will make sure that it will iterate through
all the table's rows.
So that is the reason of course we really need to create a cursor, right?
So first of all we will go ahead and create a cursor.
And with respect to this cursor I will just go ahead and write connection dot cursor.
Okay.
So this is how we basically go ahead and create it.
Okay.
So this is my cursor over here.
So let me make the spelling right.
Okay.
Now the first step is that we will go ahead and create a table.
Obviously we have created our database.
Now inside a database we really need to create a table.
Now we need to create this particular table by using the same cursor object.
Okay.
So here in order to create it we'll just go ahead and say hey Cursor.execute.
And now whatever is my command to probably create my entire table.
That is what I'm actually going to use it over here.
Right.
So you know the query, right.
What I'll do I'll go ahead and write create table.
If not let's say if not I'll use if not exist.
Right.
Let's say I'm going to go ahead and create my employees table.
Employees table.
And this will basically be my brackets right now with respect to this employees table.
What I'm actually going to do is that first of all, I'm going to use ID and this ID will be nothing.
But let's say this is an integer.
Let's say this is an integer.
After creating an integer I will say, hey, this is my primary key.
Okay.
Along with the primary key, I have to make sure, see, at any point of time, whatever tables you
specifically create, you really need to create a primary key.
Okay.
Then the next parameter will be name and I will give a worker.
You can also write it as text.
And this will also be not null because we really need to give this particular value at any point of
time.
Okay.
The third parameter that we are going to give over here is nothing but age, which will be integer,
right?
You can also go ahead and write int.
But right now I'm using integer.
And then we will also go ahead and write department which will again be a text.
Right.
So this is my entire table information that I have with all the information over here.
Now uh, if I want to really execute this right.
So I'll just go ahead and press shift tab so that let's keep the formatting very much simple.
Okay.
Uh here you can see this is closed and this bracket is also closed.
Now in order to execute how to probably create a table.
Right.
I will just go ahead and write.
Hey, uh.
Commit.
Sorry.
See, I'm already executing this entire thing, right?
Cursor.execute.
Right.
And then after this thing is basically executed, when this line of code is executed, I'll be able
to see a table which is called as employees.
And then finally, we also need to commit the changes.
Right?
So committing the changes is very simple.
I just have to write connection dot commit okay.
Without this the committing will not happen.
So if I go ahead and execute it here you can see hey perfectly it has got executed.
Everything is fine over here and it has got successfully executed.
In short, okay.
And right now if you go ahead and just execute again.
Right.
So I'll copy this over here, I'll paste it over here.
And let's say if I just go ahead and write my command uh, select Select star from this employee.
Employee.
Employees.
Okay.
If I go ahead and execute it, you'll be able to see that.
Okay, I'm getting a syntax error.
Okay.
Now it should get executed here.
You can see that I'm getting an object.
So some some data is basically getting returned.
That basically means I am able to execute this particular command right after I create it.
Right.
Don't worry.
I'll be showing you how you can read this entire data that is available inside this particular table.
But before that, what we really need to do is that we need to insert some data.
So let's go ahead and insert some data.
So for this I will go ahead and write insert the data in SQLite three okay SQLite database or table.
Or I'll say table right.
And we are using that particular database.
As you all know that is nothing but employee example dot db okay, now uh how to insert it again I will
be using cursor dot execute.
Okay.
Cursor dot execute.
And here I'm going to probably go ahead and write my insert statement.
right?
So how do I insert my, uh.
Uh, how do I insert records?
So it will be nothing but insert into employees.
And there are three parameters for the name age department.
Okay.
Name age department.
And here specifically we are going to use values.
And with respect to values I'll just go and add my information.
Let's say Krish 32.
And let's say I am a data scientist or data scientist.
So I'm just going to add this entire record over here.
Okay.
Now similarly I need to insert some more records.
So let me do one thing.
Let me just copy some more records similarly.
And we just need to execute this insert command inside Cursor.execute.
So here I'm adding Bob engineering Charlie 35 in finance and all.
Okay.
And finally I have to say connection dot commit.
So this will basically go ahead and uh, this will go ahead and execute my means.
It will just do the commit of this particular code.
Okay.
So connection dot commit.
Okay.
So here we are just going to commit the changes.
That is basically the insertion.
So if I go ahead and execute this you will be able to see that it has got successfully executed.
Now let me do one thing.
Let me just go ahead and execute this.
And or if I really want to query any data how do I query it.
Let's see.
So querying the data from Sqlite3 or from the table or from the table itself.
Right.
How do I query it.
So again what I will do I'll go ahead and right cursor dot execute.
And here I'm just going to use my command.
Okay.
What will be the command over here.
Select star from employees okay.
Now after executing it uh once I execute it I have to probably get the rows right so rows we can get
it.
I just need to use cursor dot fetch all.
Okay, so fetch all is one function that we specifically use to get all the records from the select
star command.
Okay, once I get the roads, we can go ahead and print the query rows.
The query data.
Okay.
So we can go ahead and print the query data.
So let's go ahead and print this.
So I'll say for for rows in row sorry for row for row in rows okay.
We are just going to print the row okay.
So once I execute it here you can see in tuple format I'm able to get 32 data scientist Bob 25 engineering
and Charlie 35 finance.
So what all things we basically did I showed you how to create a database, how to connect to a database,
then how to create a table if that table does not exist.
Also how you can create and always make sure whenever you execute this kind of command, any crude operation
that you are doing, you have to probably write connection dot commit right then here you can see inserting
the data into SQLite.
I've actually done this also.
Okay.
Let's say that I want to update the data in the table.
How do I do that.
Okay.
Update the data in the table.
Okay.
So in order to do that again I'll go ahead and write Cursor.execute.
Okay.
And with respect to this particular execute function I will go ahead and write my update SQL command.
Right.
I hope you are familiar with SQL.
So I'll just go ahead and write it.
Update employees.
Okay so this will be my function that is employees.
And here I'll say set update employees okay.
And I'll say set age is equal to 34.
Let's say I'm going to set the age 34 where the name equal to crush.
So I'm going to basically execute this particular command.
So wherever the name is crush in the record I'm just going to update the age from initially I give 32.
Now I'm actually going to give 34.
Okay.
So once I execute it and again after doing this I have to commit.
If you don't commit it guys, you'll get an error where you will not be able to see the updated records.
So here you can see unrecognized token.
Okay fine Krish.
Okay.
Where name is equal to Krish.
So this is basically executed.
Now I can again go ahead and print all these things.
And here you can probably see I will be getting my row as 34.
So this has also got updated.
Okay so we are just focusing on the entire crude statement right where crude execution statements are.
I've shown you how to do the select insert data.
Uh, query the data, update the data.
Now after this I will also show you how to delete the data from the table.
So let's go ahead and delete the data from the table.
Okay.
So with respect to deleting the data from the table I will again go ahead and write cursor dot execute.
And here I will just go ahead and use multi-line characters so that I will be able to execute it.
So I'll go ahead and write delete from employees.
Okay.
Delete from employees where name is equal to.
Let's say I go ahead and delete this record called as Bob.
And here again I will go ahead and write connection dot commit.
Okay.
Execute it.
Uh, Bob.
Where name.
Delete from employees.
Where name?
Okay.
Capital.
Where?
Okay.
It should be fine.
It should be smaller.
Uh, capital.
Delete from employees.
Where name is equal to Bob.
Let's see.
It says near form Cursor.execute.
Cursor.execute.
Delete from okay form.
It should be from.
Simple.
Mistakes will definitely be there, so don't worry about it.
Now if I'll go ahead and print all my rows by just writing select star from queries or whatever data
I have, you'll be able to see that I only have two records.
Okay, so I'm not going to edit whatever errors I'm specifically getting so that you should be able
to see this entirely right now.
Let me show you one entire project.
So here I'll be working with sales data okay.
Now, first thing first, what I'm actually going to do, I'll first of all connect to a SQLite database.
Okay.
So this is how you can connect it SQLite dot connect sales underscore data dot db.
This is my cursor.
We'll create a table for sales data.
Create table if not exist sales ID is there integer data.
Text not null product text not null.
Sales integer region.
Right.
And then after probably doing that we will go ahead and insert some of the data.
Right.
Sales data.
So in order to insert this time I've actually given this kind of list.
And I've given all the values in tuples based on the four values.
Right.
Whatever four values are, the id integer id is a kind of a primary key, right.
So here you can see date product sales and region is basically given.
So here are all my values.
Now I will just go ahead and write connection dot commit.
Okay.
So we have inserted this entire records, right?
Four records.
Now in order to probably get all the records, I can, uh, say, uh, one more thing.
Right.
So right now, uh, I should not be had executed this because I need to insert this entire sales data.
Right.
Now, there are so many different, different records in the sales data.
How do I insert so many different cells at once?
Because previously you have seen about Cursor.execute and we executed all the insert statement.
Now I will be showing you how you can do the bulk insertion also.
So for this I will be using cursor dot execute many okay.
So if I go ahead and basically write this cursor.execute many.
And inside this I will use my triple quotes just to make sure that I write my query over here.
So here I'm going to write insert into sales.
And here the value are like date Product.
Product sales region.
Okay.
Uh, region.
Uh, values.
And here I'll just give four question mark three and four.
Okay.
And along with this four question mark, I know I have to replace this particular question mark with
something else.
Right.
And how do I do that.
So here I what I will do I will just give it over here like sales underscore data.
Right.
And this will basically be my entire sales data which will get replaced it over here.
Right.
So if I go ahead and execute it and finally now I can go ahead and do my connection dot commit.
Now it should execute it okay.
So connection dot commit okay.
If I go ahead and execute it.
So here you can see all the insertion has may have happened.
Now in order to check I will just go ahead and write the same thing with respect to the.
So here you can see all the records are basically there.
I have just used select star from sales and all and all and all.
Now, if you really want to permanently close the connection, right.
Close the connection.
That basically means you will not have access to any DB as such.
So you can just go ahead and write connection dot close.
This is the last statement that you can basically use.
Okay.
Now uh, if you probably go ahead and see Cursor.execute, if I try to execute this again, it will
give us an error, okay.
Because we have closed the connection.
Cannot operate on a closed database.
See this?
So I hope you are able to understand all these things.
Uh, multiple examples quickly.
I have actually shown you importance of SQLite database and how easy it was probably to create if you
are familiar with somewhere with SQL of writing commands and all, I think you should be very much easily
able to understand.
Okay, so like how we have used execute many right over here.
Execute.
Uh cursor.execute.
We have used here you can see execute many.
You can also go ahead and write cursor dot insert many.
Right.
So there is also a method which you can go ahead and try it out.
So I hope you like this particular video.
I will see you all in the next video.
Thank you.