Iterate over Rows in Table in Python MySQL

Python MySQL – Iterate over rows in Table

To iterate over rows of a MySQL table in Python program,

1. Create a connection to the MySQL database with user credentials and database name, using connect() function.
2. Get cursor object to the database using cursor() function.
3. Call execute() function on the cursor object, and pass the SELECT FROM table query.
4. Call fetchall() function on the cursor object. The function returns an iterator to the records in the table. Each record is a tuple.
5. Use a For loop statement to iterate over the rows/records from the fetchall() function.

Example

Consider that there is a schema named mydatabase in MySQL. The credentials to access this database are, user: root and password: admin1234, and there is a table named fruits in mydatabase.

In the following program, we fetch all the records from fruits table, iterate over them using For loop, and print them to output.

Each row is a tuple, and we can access the individual column values using index as shown in the For loop.

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="admin1234",
  database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM fruits")

myresult = mycursor.fetchall()

for row in myresult:
  print(row[0], '-', row[1], '-', row[2])

Output

Apple - 25 - Canada
Avocado - 37 - Mexico
Banana - 40 - Mexico
Mango - 15 - India

Summary

In this tutorial of Python Examples, we learned how to iterate over the records of a table in MySQL database, from a Python program.