Python-MySQL SELECT FROM

In this tutorial, we will see how to fetch the data from a MySQL table using the python program in easy steps. We will see how can we fetch only one row or multiple rows using different functions. So, let’s get started!

Also read: Python MySQL Overview and Introduction

Before We Start

We all know that we use the SELECT statement to fetch the data from the MySQL table. Let’s revise the concepts once again.

To select all rows from the table-

SELECT * FROM table_name;Code language: SQL (Structured Query Language) (sql)

To select particular columns from the table-

SELECT col1,col2 ... FROM table_name;Code language: SQL (Structured Query Language) (sql)

To select a particular number of rows from the table-

SELECT * FROM table_name LIMIT 4;Code language: SQL (Structured Query Language) (sql)

Python-MySQL SELECT Data From Table

Python provides us with three different methods to fetch the data from the table- fetchone, fetchmany and fetchall.

Note that, you don’t need to write different queries for the above three methods. We will understand it through examples below-

Before we demonstrate examples, note that we will use the table of the following description and we already have some data inserted into it as shown below.

Fetch All Rows From Table using fetchall()

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="1234",
    database="journaldev"
)
mycursor = mydb.cursor()
mycursor.execute("select * from emp")
result = mycursor.fetchall()
for x in result:
    print(x)Code language: Python (python)

In the above code, we first create a cursor object.

Second, we execute the SELECT query.

Using the fetchall() method, we get all the rows from the table and store them in the variable result.

The result we get is in the list form. To print one result per line, we use the FOR loop.

Let’s run the program and see the result.

As you can see, we have received all the results on the terminal.

Fetch Single Row From Table using fetchone()

If you want to get the only first row in the table, you can use the fetchone() method instead of using the LIMIT clause.

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="1234",
    database="journaldev"
)
mycursor = mydb.cursor()
mycursor.execute("select * from emp")
result = mycursor.fetchone()
print(result)Code language: Python (python)

In the above code, we will get only a single record. Therefore, we don’t need the FOR loop.

As you can see, we have got the only first row.

Also read: Python – MySQL Insert Data

Fetch Multiple Rows From Table using fetchmany()

Using the fetchmany() method, we can get multiple records without specifying the LIMIT clause.

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="1234",
    database="journaldev"
)
mycursor = mydb.cursor()
mycursor.execute("select * from emp")
result = mycursor.fetchmany(5)
for i in result:
    print(i)Code language: Python (python)

In the above code, we have used the fetchmany() method and passed the number of records we want as an argument.

As you can see, we have received five rows.

Conclusion

In this tutorial, we have learned to fetch data from the MySQL table. We have learned to use the python functions such as fetchone(), fetchmany() and fetchall(). You can get the same results without using these functions with the help of the LIMIT clause. You are free to use any method as long as it works efficiently. I hope this tutorial was helpful to you. Stay tuned for more interesting tutorials.