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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | 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:
- Hospital Management System in Python
- Student Management System in Python
- Random Password Generator in Python
- Best Python Projects for Beginners
Inquiries
If you have any questions or suggestions about the python mysql select query, please feel free to leave a comment below.
