Python Basics

Python PyMySQL Read Records (Select Statement)

So far we have learned how to establish connection with mysql database using pymysql, create table, insert record. In this tutorial, we are going to learn how to print records from the table. For this we are going to use sql select statement.

The table from which I am going to fetch record is as below. The table name is emp_info

emp_id emp_name emp_age
1 Raj 22
2 Rahul 24
3 Vivek 26
4 Ajay 28
5 Suraj 29

Print all the records from table

I have already mentioned, to run any sql command we need cursor object and we have learned how to create it. cursor object has execute method or function which runs the sql command. cursor object has another method or function ‘fetchall‘ which collects the desired result once select statement is executed successfully and returns in the form of tuple. fetchall() method / function returns collected record from table in the form of tuple.

So if we loop through the tuple, then we can get the desired result. Following example code is going to clear the above mentioned narration.

conn=pymysql.connect(‘localhost’,’root’,”,’emp_db’)

cursor=conn.cursor

sql=”select * from emp_info”

try:

cursor.execute(sql)

rows=cursor.fetchall()

for row in rows:

print(row)

except pymysql.Error as pe:

print(pe)

conn.rollback()

finally:

cursor.close()

conn.close()

Output: 

(1,Raj,22)

(2,Rahul,24)

(3,Vivek, 26)

(4,Ajay,28)

(5,Suraj,29)

If I want to print only name, then in the place of print(row), I have to use print(row[1])

Print Only One Record using where clause

Lets print the name where id=3.

Before proceeding further, understand here, we can use fetchall() function of cursor object to fetch all the records but here we are sure, we are going to get only one record, so in this case we can use the other function fetchone() of cursor object. The fetchone() function returns the first record always depending upon the sql command provided. Here also the single record is in the form of tuple.

conn=pymysql.connect(‘localhost’,’root’,”,’emp_db’)

cursor=conn.cursor

sql=”select * from emp_info where id=3″

try:

cursor.execute(sql)

row=cursor.fetchone()

print(row)

except pymysql.Error as pe:

print(pe)

conn.rollback()

finally:

cursor.close()

conn.close()

Output:

(3,Vivek,26)

Note: fetchone() function is not only used in the case where we are well aware we are going to get one record from the table. But also we can use fetchone method in those cases also where the sql command fetches more than one record but we want to get first record only.

Can you guess what will fetchone or fetchall going to return if it fails to find any record?

It will return None.