Javaexercise.com

Python Tutorial

Fetch Data From MySQL Table Using Python

In our previous topic, we have discussed about how to connect to MySQL using Python, creating database, creating table etc.

Now in this tutorial, we will fetch data from MySQL table using Python script. To fetch data, we will use SQL select query and built-in methods of Python MySQL connector.

Python MySQL Connector is a driver that acts as a bridge to establish connection between Python and MySQL. For more details, you can refer our previous topics.

Here we will fetch data by using three main methods which are used to fetch data based on the requirement. It means we can fetch all the records of table, or just couple of rows or even single row from the table. Lets see the methods details:

  • fetchone(): It returns a single row from the table. It returns an empty list if no row is present.

  • fetchmany(): It fetches the numbers of rows which are specified using the size argument, which defaults to one.

  • fetchall(): It fetches all rows of a table and returns a list of tuples. It returns an empty list if table is empty or no more rows are available.

Now lets see examples of each method to understand in better manner.

For all the below examples, we refer a database company and table EMPLOYEE. This table contains four rows which are seen in the below snapshot.

query-table

Example: Fetch single row from MySQL table using Python?

Lets create an example that will fetch single row from the table. Here, we used fetchone() method that returns a tuple consisting of data.

import mysql.connector
from mysql.connector import errorcode

try:
  
  # Connecting with MySql
  con = mysql.connector.connect(user='root', password='mysqlpassword', host='127.0.0.1', database='company')
	
  # Using cursor() method to create cursor object
  cursor = con.cursor()

  # SQL query to fetch data.
  sql_query = (
  "SELECT * FROM EMPLOYEE"
  )
  
  # Executing query
  cursor.execute(sql_query)

  # Fetch single record
  row = cursor.fetchone()

  print(row)

except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Your user name or password is incorrect")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)
else:
  con.close()

Output:

('E20021', 'John', 'Polo', 'Sr. Java Programmer')


Python Example: Fetch multiple rows from MySQL table

In this example, we are fetching multiple rows by using fetchmany() method. It returns a list of tuples consists of data. See the below example.

import mysql.connector
from mysql.connector import errorcode

try:
  
  # Connecting with MySql
  con = mysql.connector.connect(user='root', password='mysqlpassword', host='127.0.0.1', database='company')
	
  # Using cursor() method to create cursor object
  cursor = con.cursor()

  # SQL query to fetch data.
  sql_query = (
  "SELECT * FROM EMPLOYEE"
  )
  
  # Executing query
  cursor.execute(sql_query)

  # Fetch multiple records
  rows = cursor.fetchmany(size=2)

  print(rows)

except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Your user name or password is incorrect")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)
else:
  con.close()

Output:

[('E20021', 'John', 'Polo', 'Sr. Java Programmer'), ('E23005', 'Somya', 'Shri', 'Designer')]


 

Python Example: Fetch all rows from MySQL table

In this example, we are using fetchall() method to fatch all the available rows in the table. This method returns list of tuples. See the below example.

import mysql.connector
from mysql.connector import errorcode

try:
  
  # Connecting with MySql
  con = mysql.connector.connect(user='root', password='mysqlpassword', host='127.0.0.1', database='company')
	
  # Using cursor() method to create cursor object
  cursor = con.cursor()

  # SQL query to fetch data.
  sql_query = (
  "SELECT * FROM EMPLOYEE"
  )
  
  # Executing query
  cursor.execute(sql_query)

  # Fetch all records
  rows = cursor.fetchall()

  print(rows)

except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Your user name or password is incorrect")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)
else:
  con.close()

Output:

[('E20021', 'John', 'Polo', 'Sr. Java Programmer'), ('E23005', 'Somya', 'Shri', 'Designer'), ('E23006', 'Rohan', 'Som', 'Linux Admin'), ('E23007', 'David', 'Parker', 'Developer')]

 

Example: Iterating/Traversing Data

In the above example, we traverse the data by using for-loop. We use loop to fetch record column wise. 

import mysql.connector
from mysql.connector import errorcode

try:
  
  # Connecting with MySql
  con = mysql.connector.connect(user='root', password='mysqlpassword', host='127.0.0.1', database='company')
	
  # Using cursor() method to create cursor object
  cursor = con.cursor()

  # SQL query to fetch data.
  sql_query = (
  "SELECT * FROM EMPLOYEE"
  )
  
  # Executing query
  cursor.execute(sql_query)

  # Fetch single record
  row = cursor.fetchone()

  # Iterating Tuple
  for row in rows:
     print(row)

except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Your user name or password is incorrect")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist") 
  else:
    print(err)
else:
  con.close()

Output:

E20021

John

Polo

Sr. Java Programmer


Conclusion

Well, in this topic, we learnt to fetch data from table using fetchone(), fetchall() and fetchmany() methods.

 

Previous
Next