Fenchone and Fetch all Data in python

Hello guys. Welcome to LearnVern.

In the last topic we saw Create Table & Insert Record in database.

Where we created a table using Python & then inserted the data in the database using Python.

Today we will fetch the data from the database and fetch by ID that is fetch single data as well from the database.

Like always, moving onto the practicals to make you understand better.

For that, I’ll open the Jupyter.

In which we have to import PYMySQL first of all and make a method to create connection.

If any of you don’t know about this method, kindly watch the previous video which is Create & Insert video.

I have explained everything properly which will make it easier for you to follow.

And then continue here.

So what are we going to do today?

We are going to fetch all the data from our database & print it in our console.

Let’s start with the process.

First off, I’ll markdown Fetch All Data From Database.

We have to make a function here.

And I’ll name the function ShowAllData.

In which I have to establish a connection.

I’ll take a variable named Conn for connection & call the create connection method.

So our connection is established.

Next we have to create a cursor.

I’ve already told you in the last video how to make a cursor.

We have to make a cursor like this.

Then we will fire a query.

Select. Star. from table name.

Our table name is Student. We fired this query.

Now it’s time to fetch all the data from the Student table.

So now when we will fetch the data, we’ll have to store it somewhere.

There is a method used to Fetch called the Fetch All Method.

The Fetch All Method. How will we do it?

You simply have to…

Suppose I took a variable named Result here.

And I called the cursor . (dot) fetchall method.

But when will the cursor fetch? When we will execute our query.

First we will have to write cursor . (dot) execute.

Then we have to pass our query.

So our query will run first & then the data will arrive as result after fetching.

It will be multiple data for sure.

We will rotate a For Loop here.

We will use a For Loop here.

We won’t fire this query again & again for multiple data.

So we will rotate the For Loop here.

I took a For Loop, For I in result.

Then I printed the I.

The For Loop will call the data again & again.

Now as I call the ShowAll method & then run it.

Right now we only have 1 data.

This is our ID, this is the name, this is the email ID & this is the city.

Clear?

Now I will enter the multiple data.

I’ll enter a couple of more data.

Suppose I took Ram. Email R @ gmail.com.

Surat city. It is saying create connection not defined.

We will have to run this first.

We have to make this into a comment & then run it, it will get initialised.

We will run it again & now it should store the data.

Always remember 1 thing guys, if you are working in Jupyter, first you will have to run each cell if you are restarting your code. Surat.

Data inserted. So our data is inserted.

We will run ShowAll again.

See, we got 2 datas.

All the data in our whole data will be shown here.

So we have to remember 1 thing that whenever you restart your code, you’ll have to run all the cells.

So this was Fetch All Data.

Now suppose I want to fetch only 1 person’s data.

For instance, I want to Fetch only Ram’s data.

For that, I’ll have to use Fetch by ID.

Now how do we do Fetch by ID.

I’ll markdown here, Fetch Data by ID from Database.

Let’s see how to do it.

We will have to make another function again.

Def ShowAllDataByID.

We’ll have to call a particular ID’s column here.

I’ll show you by opening the database what is the name of our column ID.

Whenever you open your table, our ID’s column name is SID.

Right now we need the data according to the ID. 

We will fetch the column ID, pass SID in argument.

Because we want the data according to the ID.

First of all, we have to create a connection.

I called the create connection method.

Then we have to make a cursor, connection . (dot) cursor.

Now we want an argument.

We’ll have to bring the argument we passed in the argument method.

We brought SID here.

Then we have to fire a query.

The query will be Select by ID.

Select. Star. from Student where SID = % S.

So we will get whatever ID the user passes.

We will get that ID’s data.

Next up, we have to execute.

Cursor . (dot) execute.

I’ll pass our queries & arguments.

Now we have to fetch data.

So it is compulsory to use the Fetch All method.

Result = cursor . (dot) fetchall method.

We will rotate For Loop again.

Why? Because we want Multiple Objects.

For I in result. Now I’ll print I here.

Now I’ll have to take an input from the user to call this method.

First & foremost, I’ll have to show my data.

I’ll have to show all of my data.

I’ll call my ShowAll method.

Then I’ll ask for the input of the ID.

I took a variable named SID & asked for the integer input.

Enter your ID.

Then I called my ShowAll method.

ShowAllDataByID method. And then passed SID.

Now we will run it. We got the data here.

We have 2 datas here.

Now suppose I want the 2nd ID’s data.

I wrote 2 here. Enter.

See? We got the 2nd ID’s data here.

So this is how you can fetch a single data with the Fetch by ID method.

If you want to fetch multiple data then you can use the Fetch All data method.

Did you understand today's concept?

First of all, we fetched the data.

We fetched all of the data available in the database.

Then we fetched a particular ID’s data from the database.

In the next video, we will see how to Update & Delete data.