Display Records from MySQL Table using Python

Display records from MySQL table using python is our last article of this series. In this series of Python and MySQL, we learn how to install python package MySQL client to make a connection between Python and MySQL.

Insert record in MySQL table using python, Delete record from MySQL table, Update a record in MySQL table and now we are going to learn how to display records from MySQL table using python.

Display records from mysql using python

MySQL syntax to fetch and display record(s) using SELECT command is as follow

SELECT  * | columnNames
From TableName  
[ where <condition> ]
[ order by  ASC/DESC, ColumnName ASC/DESC ]
[ group by  ]

Unlike other DML ( Data Manipulation Language ) commands SELECT fetch data from the server and display then on the screen, The output of the select command is broadly divided into three major category

  1. Single-word output
  2. Single row output
  3. Multi-row output

Single Word output

Some SQL select commands may generate single word output like

Select count(*) from question

The above command is used to count the total number of rows available in the table question and  generate a single word output.

Before moving ahead, let us explore the table structure of our STUDENT table

student table structure

Student table contains the following records

student table with records

Select statements that produce only single word outputs are tackled differently. Python program to get such result in python is as follows

import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost", "root", "", "binarynote")
cursor = db.cursor()
sql = "select count(*) from question"
cursor.execute(sql)
result = cursor.fetchone()
print(result[0])
db.close()

The above program fetches data in a python variable result using cursor.fechone() method, that is actually a python set. The first value of this set is our result ie result[0]

 Single row output

Few select commands produce single row output. The example of such SELECT statements is as follow

Select admno, name from student where admno = 109

The above SELECT statement generate only a single row output as condition satisfied only on a single row. This type of results are processed differently. Here is the python code to fetch a single record from database table and display it using PrettyTable python Package.

import MySQLdb
from prettytable import PrettyTable
# Open database connection
db = MySQLdb.connect("localhost", "root", "", "binarynote")
cursor = db.cursor()
sql = "select * from question where qid=10"
cursor.execute(sql)
results = cursor.fetchall()
t = PrettyTable(['ID', 'Question', 'A', 'B', 'C', 'D',
'Ans', 'Exp', 'gid', 'sid', 'tid'])
for idr, question, a, b, c, d, ans, exp, gid, sid, tid in results:
      t.add_row([idr, question, a, b, c, d, ans, exp, gid, sid, tid])
print(t)
db.close()

Remember we are fetching all the available rows/ records in the cursor using fetchall() method and printing them on the screen using PrettyTable. The added advantage of PrettyTable is – your output is clean and readable all the time.

Install PrettyTable Python Package using Pip command

Pip Install PrettyTable

Display MultiRow records using Python

There is nothing special in the programming part. The only change is – SQL command that fetches multiple rows.  Thus the program for multiple rows is as follows

import MySQLdb
from prettytable import PrettyTable
# Open database connection
db = MySQLdb.connect("localhost", "root", "", "binarynote")
cursor = db.cursor()
sql = "select * from question"
cursor.execute(sql)
results = cursor.fetchall()
t = PrettyTable(['ID', 'Question', 'A', 'B', 'C', 'D',
     'Ans', 'Exp', 'gid', 'sid', 'tid'])
for idr, question, a, b, c, d, ans, exp, gid, sid, tid in results:
    t.add_row([idr, question, a, b, c, d, ans, exp, gid, sid, tid])
# print(idr,name,fname,add,phone,email)
print(t)
db.close()

The output of the above program

I hope now you will be able to perform a SELECT statement on MySQL Tables using python. If you have any issue with these program then please feel free to contact us via email.

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.