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

Python Server Side Programming Programming

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

Published on 10-Jun-2021 12:07:52

Previous Page Print Page