Select data from Table

Welcome to another tutorial on Python MySQL. Here you learn how to retrieve data from MySQL table in python, including, the complete table data, and data from some specific columns.

Python MySQL - SELECT Data

In MySQL, to retrieve data from a table we will use the SELECT statement. The syntax for the same is given below:

SELECT column_names FROM table_name

Retrieve All records from MySQL Table

To obtain the records from a table, the * is used rather than the column names. Check out the example below on how to retrieve all the data from the student's table that was initially inserted.

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database="mydatabase1"
)

cursor = db.cursor()

## defining the Query
query = "SELECT * FROM students"

## getting records from the table
cursor.execute(query)

## fetching all records from the 'cursor' object
records = cursor.fetchall()

## Showing the data
for record in records:
    print(record)

Output:

('Mike', 'Computer Science', 'London, 1)
('Ads', 'Computer Science', 'Hong Kong', 2)
('Michael', 'Computer Science', 'USA', 3)

Retrieve data from specific Column(s) of a Table

To select data from some columns of the table, you just need to mention the column name after the SELECT in the syntax mentioned above:

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database="mydatabase1"
)

cursor = db.cursor()

## defining the Query
query = "SELECT name FROM students"

## getting 'name' column from the table
cursor.execute(query)

## fetching all usernames from the 'cursor' object
names = cursor.fetchall()

## Showing the data
for name in names:
    print(name)

Output:

('Mike',)
('Ads',)
('Michael',)

Selecting Multiple columns from a Table

We can as well select multiple columns from a table at a given time by just providing the names of the multiple columns in the syntax above. Check out the example below.

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database="mydatabase1"
)

cursor = db.cursor()
## defining the Query
query = "SELECT name, branch FROM students"

## getting 'name', 'branch' columns from the table
cursor.execute(query)

## fetching all records from the 'cursor' object
data = cursor.fetchall()

## Showing the data
for pair in data:
    print(pair)

Output:

('Mike', 'Computer Science')
('Ads', 'Computer Science')
('Michael', 'Computer Science')

To fetch the first record - fetchone()

From the above example, you can see that all the rows are fetched because we initially used the fetchall() method. So, to fetch only a single – row, the fetchone() method will be used. Thus, this method will return the first row from the records fetched by the query. Check this below:

import mysql.connector as mysql
db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database = "mydatabase1"
)

cursor = db.cursor()
cursor.execute("SELECT * FROM students")

myresult = cursor.fetchone() ##fetches first row of the record

print(myresult)

Output:

('Mike', 'Computer Science', 'London', 1)