How to select all the data from a table using MySQL in Python?

The tables in MySQL consist of rows and columns. The columns specify the fields and the rows specify the records of data. The data in the tables needs to be fetched to use. We may at times need to fetch all the data from the MySQL table.

All the rows can be fetched from the table using the SELECT * statement.

Syntax

SELECT * FROM table_name

The * in the above syntax means to fetch all the rows from the table.

Steps you need to follow to select all the data from a table using MySQL in python

• import MySQL connector

• establish connection with the connector using connect()

• create the cursor object using cursor() method

• create a query using the appropriate mysql statements

• execute the SQL query using execute() method

• close the connection

Suppose we have a table named ‘MyTable’ and we want to fetch all the data from this table.

+----------+---------+-----------+------------+
| Name     |  Class  |    City   |    Marks   |
+----------+---------+-----------+------------+
|    Karan |    4    | Amritsar  |    95      |
|    Sahil |    6    | Amritsar  |    93      |
|    Kriti |    3    | Batala    |    88      |
|   Khushi |    9    | Delhi     |    90      |
|    Kirat |    5    | Delhi     |    85      |
+----------+---------+-----------+------------+

Example

import mysql.connector

db=mysql.connector.connect(host="your host", user="your username", password="your password",database="database_name")

cursor=db.cursor()

query="SELECT * FROM MyTable"
cursor.execute(query)
for row in cursor:
   print(row)

The above code execute the select * query which fetches all the rows from the table. Later , we print all the rows using for statement.

Output

(‘Karan’, 4, ‘Amritsar’ , 95)
(‘Sahil’ , 6, ‘Amritsar’ ,93)
(‘Kriti’ , 3 ‘Batala’ , 88)
(‘Khushi’ , 9, ‘Delhi’ , 90)
(‘Kirat’ , 5, ‘Delhi’ ,85)

Pawandeep Kaur

Updated on 10-Jun-2021 12:10:16

• Related Questions & Answers
• How can we write MySQL stored procedure to select all the data from a table?
• How can you select data from a table based on some criteria using MySQL in Python?
• How to select all rows from a table except the last one in MySQL?
• How to select data from a table where the table name has blank spaces in MYSQL?
• In ABAP, How to select all the data into my internal table using loops?
• How to select all distinct filename extensions from a table of filenames in MySQL?
• How to select all the records except a row with certain id from a MySQL table?
• Select rows from a MySQL table and display using IN()
• How to select record from last 6 months in a news table using MySQL?
• How can we export all the data from MySQL table into a text file?
• How can we export all the data from MySQL table into a CSV file?
• Delete all the records from a MySQL table?
• MySQL select query to select rows from a table that are not in another table?
• How to use an OUT parameter / read data with SELECT from table in a MySQL procedure?
• MySQL SELECT from table A that does not exist in table B using JOINS?