Fetch data from mySql example

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

Read data from mySql in Python

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'))
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, RegDate FROM tbStudent")
     
      myRecordset = mycursor.fetchall()
      for r in myRecordset:
        #print(r[0], r[1], r[2])
        print(r)
      mycursor.close()

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 MySql Fetch example
User defined function in Python
Python programming examples | Join Python Course