Fetch data from MySQL table in Python Program

This Python tutorial is all about how to Retrieve data from MySQL Table in Python. Here we will be focusing on how to use SQL SELECT FROM Statement in Python Program.

We will discuss from the beginning level so that this tutorial considered easy for the learners too.

Read data from MySQL Database Table in Python

To read MySQL Data in Python we need to learn some basics of setting up our MySQL Connection with our Python program.

Stepts to be followed before starting to fetch data from a MySQL Table in Python

1. Install MySQL Connector in Python – Please note that in this article we will be working with MySQL Connector/Python, as this is one of the best connectors. But if you wish you can use other modules such as PyMySQL, MySQLDB, OurSQL. All the modules are almost the same in terms of method, syntax and the way of accessing a database.
2. Connect MySQL with Python – You must have to build the connection successfully between the database and your Python Program.
3. Create a database in MySQL using Python – If you don’t have a database to work with then create a database where you can keep your table and all the data.
4. Create MySQL table in Python – If you don’t have a table create one with this tutorial.
5. Insert data into MySQL Table in Python Programming – You are going to learn how to fetch data, so it is always better to insert the data first in the table.

Once you are done with all the 5 steps mentioned above you are ready to Fetch MySQL data from table using Python.

Steps: Read data from MySQL table in Python

• Execution of SELECT Query using execute() method.
• Process the execution result set data.
• Use fetchall(), fetchmany(), fetchone() based on your needs to return list data.
• Use for loop to return the data one by one.

The following things are mandatory to fetch data from your MySQL Table

• Password and username of your MySQL server to connect MySQL with your Python Program.
• Database name and table name.

Here is a demo table from where I am going to fetch data in Python

Demo table to show How to Fetch MySQL Data in Python

Table name: codespeedy

Columns:

category duration level

Python Program to fetch data from MySQL table with SELECT Query

The below Python program will return all the rows from the table codespeedy.

Fetch all rows from a MySQL Table in Python using fetchall()

import mysql.connector
db_connection = mysql.connector.connect(
  host="localhost",
  user="username_of_your_MySQL_server",
  passwd="password_of_your_mysql_server",
  database="your_database_name"
)
my_database = db_connection.cursor()
sql_statement = "SELECT * FROM CODESPEEDY"

my_database.execute(sql_statement)
output = my_database.fetchall()
for x in output:
  print(x)

Output:

('Java', '80 Hours', 'Advanced')
('Php', '70 Hours', 'Basic')
('Python', '100 Hours', 'intermediate')

Also, learn,
UPDATE Data in MySQL Database table using Python

Explanation of this Python Code:

• db_connection variable stores the required info to build our MySQL Connection.
• We have stored the MySQL SELECT query in variable sql_statement.
• execute() method is used for the execution of our MySQL Query.
• Then fetchall() method is used to return all the result set as a list of tuples.
  • to learn more on fetchall() documentation of fetchall()
• for loop is finally used in order to print out the tuples one by one.

Read a particular row from MySQL table in Python

Here we will fetch a particular row from MySQL table in Python

For example, let’s fetch all the data from row no 3.

import mysql.connector
db_connection = mysql.connector.connect(
  host="localhost",
  user="username_of_your_MySQL_server",
  passwd="password_of_your_mysql_server",
  database="your_database_name"
)
my_database = db_connection.cursor()
sql_statement = "SELECT * FROM CODESPEEDY"

my_database.execute(sql_statement)
output = my_database.fetchall()
print (output[3-1])

Output:

('Python', '100 Hours', 'intermediate')

So you can see that from our table only the data from row no 3 is printed.

print (output[n-1])

n will be the row number, we want to be fetched.

To print the data from row no 1 you have to use:

print (output[1-1])

Or you can simply write:

print (output[0])

fetchmany() method to select limited number of rows from MySQL table in Python

The example table we have given is consisting of 3 rows. But just think of it, what will happen if you are having millions of rows and you need to fetch a fewer number of rows from the table. In this type of situations fetchmany() decreases the time of execution of our code. As our program does not need to fetch all the rows one by one where we only need a fewer number of rows.

fetchmany() method allow us to select a fewer number of rows from MySQL table in Python

fetchmany(size=n)

If you want to select only first 4 rows from a table just put 4 instead of that n.

fetchmany(size=4)

Example of fetchmany() in Python to select a fewer number of rows from a MySQL Database table

Again we are taking the first demo table in this example.

Table name codespeedy

Demo table to show How to Fetch MySQL Data in Python – fetchmany() method

import mysql.connector
db_connection = mysql.connector.connect(
  host="localhost",
  user="username_of_your_MySQL_server",
  passwd="password_of_your_mysql_server",
  database="your_database_name"
)
my_database = db_connection.cursor()
sql_statement = "SELECT * FROM CODESPEEDY"

my_database.execute(sql_statement)
output = my_database.fetchmany(size=2)
for x in output:
  print(x)

Output:

('Java', '80 Hours', 'Advanced')
('Php', '70 Hours', 'Basic')

So you can see we have set the size=2, thus we are getting only the first two rows from our MySQL table.

Default size of fetchmany() method is one.

fetchone() example in Python to select a single row from a MySQL table

import mysql.connector
db_connection = mysql.connector.connect(
  host="localhost",
  user="username_of_your_MySQL_server",
  passwd="password_of_your_mysql_server",
  database="your_database_name"
)
my_database = db_connection.cursor()
sql_statement = "SELECT * FROM CODESPEEDY"

my_database.execute(sql_statement)
output = my_database.fetchone()
for x in output:
  print(x)

Output:

Java
80 Hours
Advanced

We hope that this Python MySQL tutorial on How to read MySQL Data in Python was helpful.