Python MySQL SELECT Query: Step by Step Guide in Python

In this article, you will learn how to use Python MySQL SELECT query to retrieve data from your database. If you have a background in SQL, this tutorial will improve your knowledge in handling your database in Python.

Prerequisite

First things first – You need to set up your project. If in case you do not know how to do it, click the link to go to our step by step guide. Also, if you have any trouble in using the PIP command, just click the link to go to our easy step by step fix.

How to use Python MySQL SELECT Query

Import mysql.connector
This will connect your project/program to Python’s MySQL module so that your work can communicate to your database.
Use the connect() method
Using mysql.connector.connect() will establish a connection to your database. You can set the host, user, password, and database name as parameters of this method.
Preparing the SQL
Next is to prepare your query to select all the records from your table. Create an appropriately named variable to store your SQL. We will use this later as a parameter.
The cursor() method
This will create a cursor object that will interact with database.
Execute the SQL with execute()
Use your SQL variable as parameter for the execute method.
Fetch it all!
After a successful query, next is to fetch the records from the result set using cursor.fetchall. This will get all the records from your table.
Print it
Using a loop, you can now print the records one by one.
Closing the connection
Lastly, close the connection of your database.

Sample Code

Let us take a look at the example code. The database used here is “python_db” with a “students” table.

import mysql.connector
from mysql.connector import Error

try:
    dbConnection = mysql.connector.connect(host="localhost", user="root", passwd="1234", database="python_db")

    sqlQuery = "SELECT* from students"
    dbCursor = dbConnection.cursor()
    dbCursor.execute(sqlQuery)
    studentRecords = dbCursor.fetchall()
    print("Total Students: ", dbCursor.rowcount)
    print("Printing Individual Students Records... \n")

    for row in studentRecords:
        print("Id = ", row[0], )
        print("First Name = ", row[1])
        print("Middle Name  = ", row[2])
        print("Last Name  = ", row[3], "\n")

except Error as e:
    print("Error reading data from MySQL table", e)
finally:
    if dbConnection.is_connected():
        dbCursor.close()
        dbConnection.close()
        print("-- Nothing Follows -- \n Database connection is closed.")

The output

Here is the output of the code above.

Total Students:  7
Printing Individual Students Records... 

Id =  20200001
First Name =  Juan
Middle Name  =  Ponce
Last Name  =  Enrile 

Id =  20200002
First Name =  Ramon
Middle Name  =  Cruz
Last Name  =  Delos Reyes 

Id =  20200003
First Name =  Jude
Middle Name  =  Santillan
Last Name  =  Cruz 

Id =  20200004
First Name =  Jose
Middle Name  =  Mercado
Last Name  =  Bautista 

Id =  20200005
First Name =  Maria
Middle Name  =  Villanueva
Last Name  =  De Asis 

Id =  20200006
First Name =  Christina
Middle Name  =  Hortinela
Last Name  =  Dela Tore 

Id =  20200007
First Name =  Sofia
Middle Name  =  De Mesa
Last Name  =  Santa Ana 

-- Nothing Follows -- 
 Database connection is closed.

Process finished with exit code 0

Variations

You can always add a WHERE clause to your SELECT statement. Also, you can have a parameterized query. Take a look at the example below where we used the student ID as a parameter for a function. The “%s” used in the query substitutes the value being passed to the function parameter. In the case below, the value of “id” in getStudentRecord(id) is being used in the “WHERE” clause of your SQL

import mysql.connector
from mysql.connector import Error

def getStudentRecord(id):

    try:
        dbConnection = mysql.connector.connect(host="localhost", user="root", passwd="1234", database="python_db")

        sqlQuery = "SELECT* from students WHERE studentID = %s"
        dbCursor = dbConnection.cursor()
        dbCursor.execute(sqlQuery, (id,))
        studentRecords = dbCursor.fetchall()
    
        for row in studentRecords:
            print("Id = ", row[0], )
            print("First Name = ", row[1])
            print("Middle Name  = ", row[2])
            print("Last Name  = ", row[3], "\n")

    except Error as e:
            print("Error reading data from MySQL table", e)
    finally:
        if dbConnection.is_connected():
            dbCursor.close()
            dbConnection.close()
            print("-- Nothing Follows -- \n Database connection is closed.")

student1 = "20200003"
student2 = "20200007"
student3 = "20200005"

getStudentRecord(student1)
getStudentRecord(student2)
getStudentRecord(student3)

The Output


Id =  20200003
First Name =  Jude
Middle Name  =  Santillan
Last Name  =  Cruz 

-- Nothing Follows -- 
 Database connection is closed.
Id =  20200007
First Name =  Sofia
Middle Name  =  De Mesa
Last Name  =  Santa Ana 

-- Nothing Follows -- 
 Database connection is closed.
Id =  20200005
First Name =  Maria
Middle Name  =  Villanueva
Last Name  =  De Asis 

-- Nothing Follows -- 
 Database connection is closed.

Process finished with exit code 0

Conclusion

That’s how you use Python MySQL SELECT query in your projects. You can always expand and try different ways in implementing the SELECT statement in your Python projects. Check out the different Python – related projects below:

Inquiries

If you have any questions or suggestions about the python mysql select query, please feel free to leave a comment below.