How to read a SQL query into a pandas DataFrame

In Data Analysis, acquiring the data is one of the most important steps. There are various sources of data. One of which is a Database. A database is a huge source of data that have tons of features and patterns which will help in Data Analysis. These data can be accessed and manipulated using various Database Management Systems.

For data exploration, the rows and columns of the data contained within the DataFrame is helpful. The data from database can be accessed using MySQL and converted to Pandas DataFrame using various appropriate methods.

For connecting MySQL TO Python we can use mysql-connector or pymysql modules. Connection is established by providing the username and password along with the database name. The username by default is ‘root’.

import pymysql

import pandas as pd

dbcon = pymysql.connect(“localhost”, “root”, “root”, “dbname”)

Now, we can read the data using the SELECT Query using pd.read_sql_query() function. This function takes SQL Query and connection object as attributes. Here in this example, we pass SQL Select Query and dbcon object.

import pymysql

import pandas as pd

dbcon = pymysql.connect(“localhost”, “root”, “root”, “dbname”)

try:

SQL_Query = pd.read_sql_query(

”’select

Name,

country,

from Profile”’, dbcon)

Next step is to convert the variable into DataFrame using pd.DataFrame(). Pandas DaatFrame is two-dimensional, heterogeneous tabular data. It contains the labelled axes (rows and columns ). To convert the SQL_Query variable into Data Frame, we can use the following command.

df = pd.DataFrame(SQL_Query, columns=[‘Name’,’Country’])

print(df)

print(type(df))

The DataFrame takes two parameters: SQL Query Data and Columns, here we have given the same the column names as in table.

The final code looks like below:

import pymysql

import pandas as pd

dbcon = pymysql.connect(“localhost”, “root”, “root”, “dbname”)

try:

SQL_Query = pd.read_sql_query(

”’select

Name,

Country

from Profile”’, dbcon)

df = pd.DataFrame(SQL_Query, columns=[‘Name’,’Country’])

print(df)

print(‘The data type of df is: ‘, type(df))

except:

print(“Error: unable to convert the data”)

dbcon.close()

You can become more familiar with the entire concepts and practice more through data science training institute in Kochi. The right kind of training is required to understand the lifecycle of a Data Science project which can be availed by the extensive course provided by the best data science training in Kochi.