Python read data from MySQL Database

In this tutorial, we’ll show how to read data from MySQL DB Python step by step.

1. Setup Required to connect MySQL

To set up the environment, we need to connect MySQL with Python, go through this tutorial to see how to connect MySQL using mysql connector.

If you follow along with the tutorial link, we have connected everything and have code below in our IDE.

# Importing the MySQL-Python-connector
import mysql.connector as mysqlConnector
# Creating connection with the MySQL Server Running 
conn = mysqlConnector.connect(host='localhost',user='root',passwd='root')
# Checking if connection is made or not
if conn:
    print("Connection Successful :)")
else:
    print("Connection Failed :(")
# Creating a cursor object to traverse the resultset
cur = conn.cursor()
cur.execute("SHOW DATABASES")
for row in cur:
    print(row)
# Closing the connection
conn.close()

Output:

Connection Successful 
(‘information_schema’,)
(‘mysql’,)
(‘performance_schema’,)
(‘sys’,)

2. Python read data from MySQL

We can access the data using the SQL Select query which will return the result set according to the conditions specified. Let us access data from our table.

2.1 Reading all the records:

import mysql.connector as mysqlConnector
conn = mysqlConnector.connect(host='localhost',user='root',passwd='root',database='cse')
if conn:print("Connection Successful :)")
else:print("Connection Failed :(")
cur = conn.cursor()
try:
    cur.execute("select * from students")
    print("Query Executed Successfully !!!")
    for row in cur:
        print(row)
except Exception as e:
    print("Invalid Query")
    print(e)
conn.close()

Output:

Connection Successful :)
Query Executed Successfully !!!
(13, 'Kunal', 91001, 'a')
(1, 'rahul', 91567, 'b')
(10, 'shivam', 91223, 'a')
(31, 'shivam', 91085, 'c')

2.2 Selecting with where clause:

import mysql.connector as mysqlConnector
conn = mysqlConnector.connect(host='localhost',user='root',passwd='root',database='cse')
if conn:print("Connection Successful :)")
else:print("Connection Failed :(")
cur = conn.cursor()
try:
    cur.execute("select * from students where name='shivam'")
    print("Query Executed Successfully !!!")
    for row in cur:
        print(row)
except Exception as e:
    print("Invalid Query")
    print(e)
conn.close()

Output:

Connection Successful :)
Query Executed Successfully !!!
(10, 'shivam', 91223, 'a')
(31, 'shivam', 91085, 'c')

Now that we have successfully learned how to retrieve the data from the tables.

References:

Happy Learning 🙂