Python MySQL - Select Query

SELECT statement is used to retrieve data from one or more tables in a MySQL database. In Python, you can execute SELECT queries using the mysql.connector module to fetch records from the database and process them within your application.

Syntax

The general syntax of the SQL SELECT statement is:

SELECT column_name(s)
FROM table_name;

To retrieve all columns from a table, use the * wildcard.

SELECT *
FROM table_name;

Sample Table

The following Student table is used in the examples below:

Example 1: Select All Records

The example below retrieves all records from the Student table.

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="college"
)

cursor = conn.cursor()
query = "SELECT * FROM Student"
cursor.execute(query)

for row in cursor.fetchall():
    print(row)

conn.close()

Output

(1, 'John', 'CSE', 85)
(2, 'Emma', 'IT', 72)
(3, 'Michael', 'CSE', 91)
(4, 'Sophia', 'ECE', 68)
(5, 'Daniel', 'IT', 88)
(6, 'Olivia', 'CSE', 79)

Example 2: Select Specific Columns

The example below retrieves only the Name and Course columns from the table.

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="college"
)

cursor = conn.cursor()
query = "SELECT Name, Course FROM Student"
cursor.execute(query)

for row in cursor.fetchall():
    print(row)

conn.close()

Output

('John', 'CSE')
('Emma', 'IT')
('Michael', 'CSE')
('Sophia', 'ECE')
('Daniel', 'IT')
('Olivia', 'CSE')

Example 3: Select Records with Sorting

The example below retrieves all students and sorts them in descending order of marks. 

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="college"
)

cursor = conn.cursor()
query = "SELECT Name, Marks FROM Student ORDER BY Marks DESC"
cursor.execute(query)

for row in cursor.fetchall():
    print(row)

conn.close()

Output

('Michael', 91)
('Daniel', 88)
('John', 85)
('Olivia', 79)
('Emma', 72)
('Sophia', 68)