MySql →Python Connection: Read Operation

Overview

If you are working in the data industry then you must have come to the stage where you need the python and SQL connection to fetch data for analysis/prediction. This blog will help you to make the connection between python with MySQL. Let’s get started….

Python & MySql Affair

Python needs a MySql driver to establish the connection with MySql Database. 1. Installation of Mysql Driver in python environment

pip install mysql #installation of mysql Connector

Importing the installed driver in python and setting up the connection.

import mysql.connector
mydb=mysql.connector.connect(host='endpoints provided by your tech team'
                             userName='provide the user name'
                             password='password')
print(mydb)

By filling above credentials you will set up your python connection with the MySQL database. Now we need to execute the query in python using MySQL database. The primary step would be setting up the cursor for the query(to extract data from MySql Database).

Reading the data and converting it into the pandas data frame

cursor=mydb.cursor() # setting up the cursor
cursor.execute(''' Select * from akhil_db.users as u limit 10''') # it will return first 10 rows

Now we need to form a Data Frame in python, by using the data we fetched from the MySQL database. For this, we need rows and columns.

rows=cursor.fetchall()
columns=[i[o] for i in range cursor.description] #we need column name present on index 0 

cursor.fetchall It will fetch all the rows and return the data as a list of tuples.

cursor.description It gives a description of the columns present in the result set.

import pandas as pd
df=pd.DataFrame(rows=rows,columns=columns)
df.head()

Conclusion

Thanks for reading this blog. You can access more data-related content here.

More content at PlainEnglish.io.

Sign up for our free weekly newsletter. Follow us on Twitter, LinkedIn, YouTube, and Discord.

Looking to scale awareness and adoption for your tech startup? Check out Circuit.