Hey, welcome back to Python.
So in this video we'll be working on interacting with an actual database from Python.
Please watch the previous video if you missed it because the two videos are somehow related.
So in the previous video we created this data DB database.
If you don't have that file, I've attached it in the lecture resources so you can download it from
there so that that file has an event table which has these two rows for now.
If your table has different rows, that's fine.
So in this video, we still don't want to go and work with the app.
We still want to do some practice with SQL and Python.
So please create a new file.
Such as?
An example.
I will not add it to the repository.
But you can add it if you like.
So this is not related to the app.
It's not part of the app.
So here I want to show you how Python will interact with databases.
So in the previous video you saw how this DB browser for escalated program interacted with the database.
Now it's time to do this programmatically from Python.
So as you lie three is a standard library, and this is something that you need to interact with school
light databases.
So you want to start a connection first?
To do that, you do ask your light.
Three dot connect.
And inside the parentheses you want to supply the DB file or whatever name you have given to this file
so that the DB should be in the project directory.
And so this is a method.
Well, actually it's a function because this is a module.
So if you go to connect and right click and go to implementations, you'll see that this is actually
a function anyway.
So that will create a connection.
Objects instance.
If you print it out, don't expect to see anything impressive because we still have something else to
do.
We have to create a cursor object as well, which comes from connection dot, cursor method's, sorry,
connection.
So this variable, so this connection instance has a cursor object and so the cursor serves as an object
which can execute a SQL queries using the execute method, which now expects one of those SQL queries.
For example, select all from events.
Where.
So Events is the name of the table.
Right.
And then where?
Let's select all the rows where band is equal to lines.
So the band column is equal to lines.
We have three columns, right?
So where band is equal to in single quotes, we provide the value Lions with a capital L?
Yes, Capital L And at this point this cursor object will somehow be modified.
So think of it as a list.
So at this point this will contain the query delta.
It will contain that row, right?
So this row inside the cursor object.
So to print that out, you want to do.
Print cursor that fetch all this is a method or you can store this in a result variable or rows variable.
So cursor, dot, fetch all and then print rows.
Let's execute this and you should see a list of tuples.
Let's try to alter this a little bit.
Instead of bands, I want to provide a date column where the date is 2088, 10.15.
We have two rows for that.
So let's say a date is equal to 2088. 10.15 Execute.
This time we get a list of two tables.
Previously we got a list of one table.
So now we get we get two because there are two rows which contain that date.
So as you will see now, this is indeed a list.
So just the typical I think that's obvious.
But just to make sure you understand what's going on here.
So it's a list of tuples.
And so now what you can do with this data depends on your application.
Here we're just simply printing them in the command line.
But you can do different things as you'll see in the next videos where we integrate these queries into
our application.
So that was to query data, that was to establish a connection and the cursor.
A small iteration of this would be this instead of of doing.
Instead of doing all you could do.
Select band common date from events.
Right.
Stole the rows in this.
Print the rose.
And if you execute this time, this is the results we get from this print function.
So now this includes only those columns.
So band and date band here and date.
So you don't have to write an asterisk if you don't need all the data.
So that was query all data.
This is query.
Certain columns.
Next is inserting new euros.
So let's add two new rules to the table.
But before we do that, you need to prepare a list of tuples, Right?
So that would be in the same format as we had them in here.
Right.
So I'll just copy one of those lists, paste it here, and do some changes.
Such as?
Cats here.
Cat seed.
Hence and hence.
Let's change the date slightly 1717 here.
So I'll store that in variable.
Let's say in euros.
You can split this list of tuples in here if you like.
Then you want to refer to the cursor object and then to execute many this time.
So that's execute and this is execute menu, which means you add multiple rows to the database.
And here goes again the SQL query.
So insert into events.
Values.
And here, as you know from the previous video, we can supply these parentheses.
And inside here in single quotes, you could add the values like this.
Right.
But normally you will have those values stored in variables like is the case here, right?
We have this list of tuples.
So in that case, the syntax will not be like this, but here you'll have to supply question marks.
One, two and three.
We have three columns, right?
We have the banned column, the city column and the date column.
So then outside of the double codes which wrap around the SQL statement, you want to provide a second
argument.
So this is the second argument of the execute many methods.
That's the first argument.
The second argument is new rows, this variable.
So that means that each of these values will go inside here and this execute.
Many methods will take care of inserting those values into into the.
Corresponding positions in the table.
And then we do connection that commit.
Remember previously in the previous video we use this right changes button when we did some some operations
which modified the database.
So this here insert is also a an operation which modifies the database.
Therefore we need to write the changes and to do that in Python you use the commit method.
So let's execute this.
MIRREN Right.
So the process was executed.
We don't see any output because we were not printing out the data, right?
So these were printed out at this point.
So the Python interpreter runs the scripts and it shows you the current data, but then it adds these
new rows and it doesn't do anything else here.
It doesn't print out any rows.
So if you want to see what rows you have, perhaps you want to temporarily common this out, you can
select them and press control hash symbol on your keyboard or command hash symbol to comment multiple
rows of code.
And now, if I.
Do a query operation, for example, if I copy these and place them here.
But note that these queries were the date, is that value, right?
So to query all the data, all the data you want to delete that condition.
You only want to keep select all from events in double quotes and execute.
So that's what we get.
So that's the printout of the last print function rows.
So we have the cats and we have hands.
And.
Yeah, that's it.
So that was how to query all data based on a condition.
This was hard to query certain columns based on a condition.
This was hard to insert new rules.
And of course, this list can have also only one table if you only want to add only one table.
So it would be a list of one couple that would also work.
And then we have this to query all the data.
Right.
So that's about this video.
Thanks a lot for following.
And let's move on with working on our real world application.