Fetch data from mySql example

In this tutorial you will learn how to read data from mysql database in python.

First, we need to setup database username, password in configuration file, and then read from there!

Step 1

Read Data from “'mypy.ini” configuration file. You can look at how to read configuration from .ini file

Create a new instance of config = ConfigParser(), at the time of initialization of class.

Create a new instance of mysql.connector.connect, and set all database credentials

class StudentDTO():        
    def __init__(self):
        config = ConfigParser()
        config.read('mypy.ini')
        database = config.get('dbinfo', 'database')
        dbusername = config.get('dbinfo', 'dbusername')
        dbpassword = config.get('dbinfo', 'dbpassword')
        dbhost = config.get('dbinfo', 'dbhost')
         
        self.mydb = mysql.connector.connect(
          host=config.get('dbinfo', 'dbhost'),
          user=config.get('dbinfo', 'dbusername'),
          password=config.get('dbinfo', 'dbpassword'),
          database=config.get('dbinfo', 'database'))

Read data from mySql database in Python

Step 2

Write a function with name "showStudents" (you can give any name), then create mysql cursor object like mycursor = self.mydb.cursor()

Then execute your sql query like mycursor.execute("sql query")

Read data in a recordset like myRecordset = mycursor.fetchall()

Now read all records from recordset using for loop like

for r in myRecordset:
  print(r)

def showStudents(self): 
      mycursor = self.mydb.cursor()
      
      mycursor.execute("SELECT firstName, lastName, region FROM tbStudent")
     
      myRecordset = mycursor.fetchall()
      for r in myRecordset:        
        print(r)
      mycursor.close()

Above query will return a tuple object, means column name and value, like example below.

{'firstName': 'Zambia', 'lastName': 'Africa', 'region': 'Eastern Africa'}

We need to convert tuple into dictionary object, let’s look how we can read values of each row.

myRecordset = mycursor.fetchall()
mycursor.execute("select name,continent, region, surfacearea from world.country")
for r in myRecordset:
# convert tuple into dictionary
# assumption: there wont be any duplicate value myrow=dict(r)
for v in myrow:
print(v, "->", myrow[v])
#result will look like
name -> Zimbabwe
continent -> Africa
region -> Eastern Africa
surfacearea -> 390757.00

Make sure you close the cursor after use, like mycursor.close()

Note: in above example I have used fetchall() method, there are also fetchmany() and fetchone() methods of a cursor class to fetch limited rows from a table.

You also can get the row count from cursor object

myRecordset = mycursor.fetchall()
for r in myRecordset:    
    print(r)
    print("Total rows: ", mycursor.rowcount)

Python fetchone example

Here is an example of using fetchone method in python, you can use this method when you want to read only one record from recordset

def showStudents(self): 
      mycursor = self.mydb.cursor(buffered=True)
      
      mycursor.execute("SELECT firstName, LastName, RegDate FROM tbStudent")
     
      try:        
          row = mycursor.fetchone()
          print(row)        
          print("Total rows: ", mycursor.rowcount)           
      except mysql.connector.Error as error:
        print("Error while reading from MySql", error)
      finally:
            mycursor.close()
            print("MySql cursor closed")

Note: while using fetchone() or fetchmany() method you may get “unread result found” error, just to address that use buffered=True like mycursor = self.mydb.cursor(buffered=True)

Python fetchmany example

In fetchmany method, you can specify number of rows you want, also termed as "fetching size".

def showStudents(self): 
      mycursor = self.mydb.cursor(buffered=True)
      
      mycursor.execute("SELECT firstName, LastName, RegDate FROM tbStudent")
     
      try:
          myRecordset = mycursor.fetchmany(2)
         
          for r in myRecordset:
            print(r[0], ":", r[1], ":", r[2])
            
          print("Total rows: ", mycursor.rowcount)           
      except mysql.connector.Error as error:
        print("Error while reading from MySql", error)
      finally:
            mycursor.close()
            print("MySql cursor closed")

After retrieving data you can read each column value by using index number, like in above example I have used print(r[0], ":", r[1], ":", r[2])

Python Tutorial | Python Course | Python Interview Questions

Python MySql Fetch example

Learn python programming with free python coding tutorials.