Python MySQL - Select data from Table

In this tutorial, we will learn how to retrieve data from MySQL table in python, both, 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

In order to get all the records from a table, * is used instead of column names. Let us retrieve all the data from the students table which we inserted before:

import mysql.connector as mysql

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

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)

Thus the output of the above code will be:

('Ramesh', 'CSE', '149 Indirapuram', 1) ('Peter', 'ME', 'Noida', 2) ('Amy', 'CE', 'New Delhi', 3) ('Michael', 'CSE', 'London', 4)

Below we have a snapshot of the exact output when we run this python code:

In the next section we will learn how to retrieve data of certain columns from a table.

Retrieve data from specific Column(s) of a Table

In order to select data from some columns of the table just 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 = "studytonight"
)

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)

The above code will fetch the name column from the students table:

('Ramesh',) ('Peter',) ('Amy',) ('Michael',)

Selecting Multiple columns from a Table

You can also select multiple columns from a table at a time by providing multiple column names in the above syntax. Let us see the code snippet given below for clear understanding:

import mysql.connector as mysql

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

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)

The above code will fetch both name and branch column both from the table students:

('Ramesh', 'CSE') ('Peter', 'ME') ('Amy', 'CE') ('Michael', 'CSE')

To fetch the first record - fetchone()

In the above examples, we saw that all rows are fetched because we were using fetchall() method. Now to fetch only a single-row fetchone() method will be used. This method will return the first row from the records fetched by the query. Let us see the code snippet given below:

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

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

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

print(myresult)

Thus in the output the first row of the record will be fetched:

('Ramesh', 'CSE', '149 Indirapuram', 1)

So in this tutorial, we learned various ways to retrieve data from a MySQL table in Python.