Python MySql - Selecting Records from a Table

Select/Read Operation on any database means to fetch some useful information from the database.

Once our database connection is established, you are ready to make a query into this database. You can use either fetchone() method to fetch single record or fetchall() method to fetch multiple values from a database table.

• fetchone() − It fetches the next row of a query result set. A result set is an object that is returned when a cursor object is used to query a table.

• fetchall() − It fetches all the rows in a result set. If some rows have already been extracted from the result set, then it retrieves the remaining rows from the result set.

• rowcount − This is a read-only attribute and returns the number of rows that were affected by an execute() method.

Syntax

# execute SQL query using execute() method.
cursor.execute(sql)

result = cursor.fetchall()

for record in result:
   print(record)

Example - Selecting Records

Try the following example to select records from a table −

Copy and paste the following example as main.py −

main.py

import mysql.connector

# Open database connection
db = mysql.connector.connect(host="localhost",user="root",password="root@123", database="TUTORIALS")

# prepare a cursor object using cursor() method
cursor = db.cursor()

sql = "Select * from tutorials_tbl"

# execute SQL query using execute() method.
cursor.execute(sql)

# fetch all records from cursor
result = cursor.fetchall()

# iterate result and print records
for record in result:
   print(record)

# disconnect from server
db.close()

Output

Execute the main.py script using python and verify the output.

(1, 'HTML 5', 'Robert', datetime.date(2010, 2, 10))
(2, 'Java', 'Julie', datetime.date(2020, 12, 10))
(3, 'JQuery', 'Julie', datetime.date(2020, 5, 10))