Read Database tables into Pandas DataFrame

Photo by Caspar Camille Rubin on Unsplash

As a Machine Learning aspirant we need to read csv/excel files to perform data anaysis but then comes a time when we have to go deeper into loading data and we have to fetch data from a SQL database or a SQL table.

We’ll perform a basic dataloading from a MySQL database table to a pandas DataFrame.

Requirements

We need the following things installed on your machine with basic knowledge of working on pandas and mysql:

1. a sql client, we’ll use MySQL for our operations
2. Python, pandas,pymysql.
3. sqlalchemy, if not installed, use : pip install sqlalchemy

Let’s Start !!

We’ll start with installing packages and libraries onto our machine in a jupyter notebook:

import pandas as pd
from sqlalchemy import create_engine as ce

Create an object for the sqlalchemy and fill in details as per your mysql configurations:

engine=ce("mysql+pymysql://root:Password@199026@localhost:3306/employees")

Refer the below mentioned values:

username: root
password: Password@199026
host    : localhost
port    : 3306
database: employees

We use read_sql_table method from pandas and the below mentioned command in jupyter notebook fetches information from salaries table in the employees database

df=pd.read_sql_table("salaries",engine)

Once the above command has executed without errors, you can execute belwo command to see table:salaries value in a DataFrame:df

df.head()

Output for the above command is as follows:

output

And that was a simple way of fetching data from a database to pandas DataFrame!!