Open In App

Working with database using Pandas

Improve
Improve
Like Article
Like
Save
Share
Report

Performing various operations on data saved in SQL might lead to performing very complex queries that are not easy to write. So to make this task easier it is often useful to do the job using pandas which are specially built for data preprocessing and is more simple and user-friendly than SQL.

There might be cases when sometimes the data is stored in SQL and we want to fetch that data from SQL in python and then perform operations using pandas. So let’s see how we can interact with SQL databases using pandas.

This is the database we are going to work with
diabetes_data

Note: Assuming that the data is stored in sqlite3

Reading the data




# import the libraries
import sqlite3
import pandas as pd
  
# create a connection
con = sqlite3.connect('Diabetes.db')
  
# read data from SQL to pandas dataframe.
data = pd.read_sql_query('Select * from Diabetes;', con)
  
# show top 5 rows
data.head()


Output

pandas-databse

Basic operation

  • Slicing of rows
    We can perform slicing operations to get the desired number of rows from within a given range.
    With the help of slicing, we can perform various operations only on the specific subset of the data




    # read the data from sql to pandas dataframe.
    data = pd.read_sql_query('Select * from Diabetes;', con)
      
    # slicing the number of rows 
    df1 = data[10:15]
    df1

    
    

    Output
    pandas database

  • Selecting specific columns
    To select a particular column or to select number of columns from the dataframe for further processing of data.




    # read the data from sql to
    # pandas dataframe.
    data = pd.read_sql_query('Select * from Diabetes;', con)
      
    # selecting specific columns.
    df2 = data.loc[:, ['Glucose', 'BloodPressure']].head()
    df2

    
    

    Output:
    pandas database

  • Summarize the data
    In order to get insights from data, we must have a statistical summary of data. To display a statistical summary of the data such as mean, median, mode, std etc. We perform the following operation




    # read the data from sql 
    # to pandas dataframe.
    data = pd.read_sql_query('Select * from Diabetes;', con)
      
    # summarize the data
    data.describe()

    
    

    Output:
    pandas database

  • Sort data with respect to a column
    For sorting the dataframe with respect to a given column values




    # read the data from sql 
    # to pandas dataframe.
    data = pd.read_sql_query('Select * from Diabetes;', con)
      
    # sort data with respect 
    # to particular column.
    data.sort_values(by ='Age').head()

    
    

    Output:
    pandas database

  • Display mean of each column
    To Display the mean of every column of the dataframe.




    # read the data from sql 
    # to pandas dataframe.
    data = pd.read_sql_query('Select * from Diabetes;', con)
      
    # count number of rows and columns
    data.mean()

    
    

    Output:

    pandas database



Last Updated : 06 Apr, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads