fetchone and fetchall in Python to get records in MySQL

Method fetchone collects the next row of record from the table.
We defined my_conn as connection object.

my_cursor = my_conn.cursor()

my_cursor.execute("SELECT * FROM student")

my_result = my_cursor.fetchone() # we get a tuple
#print each cell ( column ) in a line 
print(my_result)
#Print each colomn in different lines. 
for x in my_result:
  print(x)

We defined my_cursor in our database connection string. We have used our student table.

Tutorial on MySQL SELECT Query

SELECT query

The retured data from database is a tuple. In above code we can check the data type of variable my_result like this

print(type(my_result)) # Output is <class 'tuple'>

The output of our above code is here

(1, 'John Deo', 'Four', 75, 'female')
1
John Deo
Four
75
female

This is our first record of the student table.

To display columns we can use like this

my_cursor = my_conn.cursor()

my_cursor.execute("SELECT * FROM student")

my_result = my_cursor.fetchone()
print("Student id = ",my_result[0])
print("Student Name = ",my_result[1])
print("Student Class = ",my_result[2])
print("Student Mark = ",my_result[3])
print("Student gender = ",my_result[4])

Output is here

Student id =  1
Student Name =  John Deo
Student Class =  Four
Student Mark =  75
Student gender =  female

By adding this code at the end of the above secript ,we can display the next record

my_result = my_cursor.fetchone()
print("Student id = ",my_result[0])
print("Student Name = ",my_result[1])
print("Student Class = ",my_result[2])
print("Student Mark = ",my_result[3])
print("Student gender = ",my_result[4])

We can use the same technique to iterate through all the records and display

my_cursor = my_conn.cursor()

my_cursor.execute("SELECT * FROM student")
my_result = my_cursor.fetchone()
while my_result is not None:
    print(my_result)
    my_result = my_cursor.fetchone()

This will display all records

(1, 'John Deo', 'Four', 75, 'female')
(2, 'Max Ruin', 'Three', 85, 'male')
(3, 'Arnold', 'Three', 55, 'male')
 -  -  -  - - -
 -  -  -  - - -

We can use cursor to iterate through the records

my_cursor = my_conn.cursor()

my_cursor.execute("SELECT * FROM student")
for my_result in my_cursor:
    print(my_result)

Output will display all the records as above.

fetchall()

fetchall() method returns a tuple. We can iterate through this and disply records

my_cursor = my_conn.cursor()

my_cursor.execute("SELECT * FROM student")
my_result=my_cursor.fetchall()
for row  in my_result:
    print(row)

The output is same as above , displaying all the records.

get a list with column values

From student table we will get all student id and names as lists.

q="SELECT id,name FROM student WHERE class='Four'  "
my_cursor=my_conn.execute(q)
my_result=my_cursor.fetchall()
my_ids = [row[0] for row in my_result] # All id as list 
my_names = [row[1] for row in my_result]   # All names as list

By changing the query, we can get all the classes with number of students in each class as list.

q="SELECT class,count(*) as no  FROM student GROUP BY class"

Getting column names

q="SELECT id,name FROM student WHERE class='Four'  "
my_cursor=my_conn.execute(q)
columns=list(my_cursor.keys()) # column names as list

Output

['id', 'name']

fetchmany()

We can collect fixed number of records by using fetchmaney()

my_cursor = my_conn.cursor()

my_cursor.execute("SELECT * FROM student")
my_result_top=my_cursor.fetchmany(size=3)
#my_result=my_cursor.fetchall()
for row  in my_result_top:
    print(row)

Output is here

(1, 'John Deo', 'Four', 75, 'female')
(2, 'Max Ruin', 'Three', 85, 'male')
(3, 'Arnold', 'Three', 55, 'male')

If you don't use size=3 then fetchmany will return one row of record only.

my_cursor = my_conn.cursor()

my_cursor.execute("SELECT * FROM student")
my_result_top=my_cursor.fetchmany()
for row  in my_result_top:
    print(row)

Output is here

(1, 'John Deo', 'Four', 75, 'female')

By using both fetchall() and fetchmaney()

my_cursor = my_conn.cursor()

my_cursor.execute("SELECT * FROM student")
my_result_top=my_cursor.fetchmany(size=3)
for row  in my_result_top:
    print(row)

my_result=my_cursor.fetchall()
for row in my_result:
    print(row)

Output is here ( displayes all records )

(1, 'John Deo', 'Four', 75, 'female')
(2, 'Max Ruin', 'Three', 85, 'male')
(3, 'Arnold', 'Three', 55, 'male')
(4, 'Krish Star', 'Four', 60, 'female')
(5, 'John Mike', 'Four', 60, 'female')
(6, 'Alex John', 'Four', 55, 'male')
 -  -  -  - - -
 -  -  -  - - -

Note that after using fetchmany(size=3), fetchall() returns balance rows starting from 4th record.

You can see we are not using the SQL again, so it is evident that after retrieving all data from MySQL ( by using the SQL ) records are stored in Python memory and we only retrieve part of the available records by using fetchmany() and rest of the records are collected through fetchall() from Python memory and not from database again.

my_cursor = my_conn.cursor()

my_cursor.execute("SELECT * FROM student WHERE id=7")
my_result=my_cursor.fetchall()
for row  in my_result:
    print(row)

This will return only one record. We can change the Query part like this

my_cursor.execute("SELECT * FROM student LIMIT 0,10")

This will return 10 records starting from first record.( What is LIMIT Query ? )

Changing Query

We used SQL to collect all the records from MySQL table. We can change this SQL to limit the number of records. This is better way than collecting all records and using limited numbers from them by using fetchmany() or fetchone(). We can change the SQL part like this ( in above code )
Using sqlalchemy to Manage MySQL

Dictionary from database record set

from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_tutorial")

query = "SELECT *  FROM student LIMIT 0,5"
my_data = list(my_conn.execute(query))  # SQLAlchem engine result set
my_dict = {} # Create an empty dictionary
my_list = [] # Create an empty list
for row in my_data:
    my_dict[[row][0][0]] = row  # id as key
    my_list.append(row[1]) # name as list
print(my_dict)
print(my_list)
# Print the other values for matching Name
for i, j in my_dict.items():
    if j[1] == "Arnold":
        print(i, j[0], j[1], j[2])

Python functions for MySQL Collect records Python- Tutorials

Subscribe to our YouTube Channel here

Python fetchone fetchall records from MySQL

Tutorial on MySQL SELECT Query

fetchall()

get a list with column values

Getting column names

fetchmany()

Changing Query

Dictionary from database record set

Subscribe