Sooner or later it will happen that you will have to work with a skilled database as a programmer for
storing and retrieving data.
Now, SQL databases have some crucial advantages towards the other data storage solutions such as CSV,
text, JSON, and other file storage formats.
The two main advantages of databases are speed and concurrency.
When you stored data in a SQL database and you retrieve data, you modify them.
These transactions tend to be faster compared when doing the same things on files such as JSON and TXT
and CSV.
However, the advantage is not so pronounced when you work with smaller amounts of data.
So when you work with smaller amounts of data using CSV, TXT and JSON is still more advantageous because
it's easier to set up.
The other advantage of single databases is concurrency, which means that SQL databases do a great job
for concurrent transactions, meaning that if two users of your app are doing the same thing in the
app, some actions which modify the data in the database, the SQL structure will take care to to preserve
the integrity of your data.
So again, concurrency is something you need to worry about for larger apps where you have many users
accessing the same amount of the same data set.
So that was some introduction of on what data are from a theoretical perspective.
Now let's look at them practically to see them at first hand, what it means to work with databases.
So to work with databases, you need some sort of program which can be a programming language such as
Python, or it can be another program such as the DB browser for SQLite, for example.
This is a graphical user interface program.
And so before we jump into Python and interact with databases through Python, first we want to interact
through databases with this program so that this will give you an initial idea what it means to work
with databases.
So please search for DB browser for SQLite on Google and then go to Escalade browser dot org.
And then please go to downloads and then you want to download one of these executables and please install
that.
And after you install it, you should see something like this.
If the font is smaller in your case, please go to the settings and then you want to increase the font
here and also in the database tab in here.
And also.
In the data browser here.
And also in the ethical tab, Right?
So through this app, you can do a series of things with databases, you can create a new database,
you can create a new table inside the database.
And you can add to records.
So rose in that table.
So the hierarchy looks like this.
So we have a database, we have tables contained in that database, and then we have rows contained
in those tables.
So let's start from the top.
Let's create a database.
To create a database, you need to go to this bottom new database.
Now, this program uses SQLite databases, so there are different database systems such as IQ, Light
and my SQL, PostgreSQL, Oracle, etc..
Now.
All these have in common SQL.
So SQL is the common structure and it's also a language which is something that you'll see how SQL comes
to be a language.
So all these databases that I mentioned are based in SQL.
So we will use SQLite.
So you need to create a database file.
So please perhaps locate the project you have in pie charm.
So that would be app ten.
In my case, I have the project files in here.
So in this project, in this folder, I want to create this database.
So please specify a name such as data.
Don't provide an extension because this program DB browser for SQLite will add an extension automatically.
So press and save.
Now the database is being created and it's asking you to add one table because the database needs a
table, at least one table.
So this window here is asking you to provide a name for that table.
So let's say events.
Right.
So in this program, we might have different tables.
So events is one table.
Perhaps later on we will expand this program by scraping some other values such as temperature or etc..
In that case, you need to create a separate table because this table now is only about events, and
now this table will have rows and each row will have different columns.
Right?
So it's like a C as the structure.
Therefore, we need to add these types of columns for the events table to add columns to this table.
You should use this add button.
So the first column will be the band rate.
We have the band name, as you might remember from the app.
So just to show you that when we run this program.
So we have the band.
We have the city closed city, and then we'll have the date.
So let's create these three columns in the database so bands this will be text, right?
We have different types.
So each column should have a type and we declare that type when we create the column.
So since band is just text, we press on text there, then we add the new column.
This will be city.
This is also text.
And then we add another one.
This would be the date.
Now, there are no specific date types for ESC lite.
So for this we will also use text.
If you had some numbers, for example the number of band members, then you'd use an integer, for example.
Or if you had something that was a float number like the price, you would use a real number.
In this case, we only have text values, so present text everywhere.
And this now is as cu l language.
We'll talk about that later.
So just take a look for now and.
I'll talk about this language in just a bit.
PRESS Okay.
And what that will do is it will add a database with a table inside.
So now in database structure, you can actually see that you have this one table here, which is events.
And the events table has three columns and currently it has zero rows.
We haven't added any data, right.
We haven't added any band or city or date.
So this is an empty table and you can see that in the Browse data tab.
So that's how the table looks like.
These are the columns and we have no data currently.
In your folder where you created a database, you'll see the file.
So data that DB in my case.
That's a file.
If you open it, you will not see anything, actually, because this is a binary file, so it's a non
text file.
Therefore we can not view the data in a text editor.
That's why we need special programs such as this DB browser which accesses the data and it allows you
to perform actions such as create tables, databases and add roles, which is something we will do now
and we can do also all these things we can do also in Python.
So first, let's do them here so that you get a better understanding of on what's going on.
And that will not make it look like a black box.
So next thing to do is let's insert some rows in the database.
No, there are two ways to do that in this program.
You either use this button here, you see, insert a new record in the current table, and then you
want to click on that null value.
Just click once and maybe wait a second and then type in a band name, for example, Lions.
Right.
And something in the city.
The lion.
City, for example, and something for date 2088 for example dot Oct ten dot Let me expand this.
15.
Press enter.
And to actually write the data in the table, then you want to press on this right changes there.
So that's we'll actually store that row in this table now.
Right.
So we can use this button here because this graphical user interface lets us do that.
But in Python, we don't have buttons to add data, right?
In that case, in Python, we will use something called SQL, as I mentioned, SQL queries.
So SQL language and these queries will look like this.
I'll give you the exact code later, but approximately the SQL query will look like this insert into
events.
Values in parentheses.
You provide the values.
Which could be single quotes since we have.
Double quotes in here.
Let's say Tigers for the band.
Tiger City in single quotes.
A comma again.
And also the date again in single quotes such as 288.
That's 1014.
So that here now is an SQL query and Python will execute that in the database.
Now if we get this query and go to execute SQL, this button here and you paste that query in there
and then you go to this play button to execute that query.
What that will do is it will enter those data.
So that's the query.
Make sure that this says query executed successfully.
Successfully.
Otherwise, you might have some typos in here.
Right.
So that's the query.
Now, if you go to browse data, you will see that tigers were added to the database.
There we go.
So that's what SQL language is about.
So SQL communicates with a database such as this data deep file here, so it communicates with that
database.
And in this case it executed the insert command.
Insert into command in the events table and then it inserted the values.
So that is an ethical command.
That is an ethical command.
That's true.
This is the table name.
And so these are the data.
Let's execute one more query.
Let's say.
Monkeys.
Monkey City and.
15 of October.
Press that play button, go to browse data.
And there we have Monkey CD.
So that was how to insert new data to the database.
Now, if you want to really write the changes, you want to press this write changes button here.
So in Python, we also have some methods which will write the changes.
So here we are doing this with this button in Python.
We will do that with methods.
After.
After this, for example, we will add something like that.
Commit that will write the changes.
Now that was how to insert data, but how to query data.
So Python will have to insert data at some point and then it will have to query data to to select data
so that you can print them out and show them on a web app or on a desktop or on the command line.
So to query Delta.
You use the select.
Command you want to select all with that asterisk from events.
Events is the name of the table.
And then you usually want to condition rights.
So perhaps the user is asking for events.
On the 15th of October.
Right.
So in that case, you do wear date.
Date is the name of that column which contains the date or date is equal to.
Single quotes and insight.
Single quotes you enter the date.
In my case, that would be 2088. 1015 dot 10.15.
So if I execute this now, this query present that and this will show me all the rows which have date
equal to that value.
So that's how you select queries.
And this is also a command which we can run in Python and I'll show that in the next videos.
So this one in here, right?
I'm just keeping them in here to have them handy later on when I use them.
Something to note is that the select operation is not a right operation.
So you don't have to write any changes because you didn't do any modifications in the database.
You're simply querying data.
Lastly, let's see at the delete command how to delete some data.
For this.
We want to use the delete command from events.
Where.
Band is equal to tigers.
Execute.
Go to Browse Dutta and Tigers has disappeared from the table and make sure to perhaps write the changes
to actually write the new update in the database.
And that's what a database is.
That's how you can interact with the database using SQL queries.
Now it's time to go into Python and I'll show you how we can send these queries to this data DB file.
So I'll see you in the next video.
Thanks.