Python fetchone fetchall records from MySQL

Method fetchone collects the next row of record from the table.
We defined my_cursor as connection object.
my_cursor = my_connect.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.

SELECT query

The retured data from database is a tuple. In above code we can check the data type of variable my_rsult 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_connect.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 Sex = ",my_result[4])
Output is here
Student id =  1
Student Name =  John Deo
Student Class =  Four
Student Mark =  75
Student Sex =  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 Sex = ",my_result[4])
We can use the same technique to iterate through all the records and display
my_cursor = my_connect.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_connect.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_connect.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.

fetchmany()

We can collect fixed number of records by using fetchmaney()
my_cursor = my_connect.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_connect.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_connect.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_connect.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 )



Your Rating




Post your comments , suggestion , error , requirements etc here




We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2019 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer