How To Get / Retrieve Data From MYSQL Database Using Python

We have discussed how to create, connect and insert into the database so far.Please read the previous articles to start.

Get Data From MYSQL Database Using Python

#import mysql module
import mysql.connector

#Connecting to our database
con = mysql.connector.connect(user='root',password='',host='localhost',database='python')

#creating cursor to use its various methods further
cur = con.cursor()

#this is our mysql query
cur.execute("""select * from pytable""")

#It is going to fetch the executed mysql query into rows.
#Hence assigning it to row
row = cur.fetchall()

#printing the result
print row

#closing the function
cur.close()

Output

get-data-mysql-python

 

I give the simple documentation in the code only.Because I already explained clearly in the previous articles about how this code works.

In this code cur.fetchall() is something you may not aware of.If you see the above output image you can get this very easily it just fetches all your data into python lists that contain tuples.

Also Read :   How To Insert Data Into MYSQL Database Using Python

Now you can get your data as you wish.

print row[0] #This will print the first tuple of your data

print row[0][0] #This will print the first element in the First Tuple (prathap)

If you didn’t understand this, please read about python lists and tuples.

Typical MYSQL Fetch Methods In Python

  1. Fetchall()
  2. Fetchone()

We are going to use these two fetch functions to retrieve our data.If you replace the Fetchall() function with fetchone() function you will get a list that contains a single tuple only because it fetches only one row and waits to fetch the next one.

But If your table has the many columns, then it will be hell to understand the data.So we should print the data line by line somewhat professionally 😛 (not much)

 

#import mysql module
import mysql.connector

#Connecting to our database
con = mysql.connector.connect(user='root',password='',host='localhost',database='python')

#creating cursor to use its various methods further
cur = con.cursor()

#this is our mysql query
cur.execute("""select * from pytable""")

#It is going to fetch the executed mysql query into rows.
#Hence assigning it to row
row = cur.fetchall()

#printing the result
while row is not None:
    
    for i in range(len(row)):
         print row[i]
    break
   
        

#closing the function
cur.close()

Here we are using While loop to make sure row is not empty.This data will be printed only if the row is not empty.

Also Read :   How To Insert Data Into MYSQL Database Using Python

We don’t know how many rows will be there so that we are using len(row) to find the length.

So Every row will be printed line by line up to its range.

After iterating through all the rows, finally, our while loop will be terminated.

We can achieve this easily with the fetchone() method.

#import mysql module
import mysql.connector

#Connecting to our database
con = mysql.connector.connect(user='root',password='',host='localhost',database='python')

#creating cursor to use its various methods further
cur = con.cursor()

#this is our mysql query
cur.execute("""select * from pytable""")

#It is going to fetch the executed mysql query into rows.
#Hence assigning it to row
row = cur.fetchone()

#printing the result

while row is not None:
     print(row)
     row = cur.fetchone()
           
#closing the function
cur.close()

Unlike in the last method where all the fetched rows (list of tuples) assigned to a row variable, In this method, we are just fetching one row (a single tuple).So probably row is not an empty then our while loop iterates the row ( Now It prints the first row).

Also Read :   How To Insert Data Into MYSQL Database Using Python

Now we have to get the next row.Here What the fetchone() does exactly for us.It fetches the next row and assigned to the row variable.So the loop iterates in itself only until the rows are empty.

Assume that what If we are not given the row = cur.fetchone() in the while loop ???

OOPS It goes to Infinite Loop 😛 Because, as there is no empty row, it loops always.

Hope you enjoy the article.Please read and practice 🙂

Please, Like Our Facebook Page For More Updates and Comment Here.

One comment

  1. Hello Guys, This is the simple tutorial on how to retrieve data from the database.Please let me know your doubt and errors 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *