How to convert pandas DataFrame into SQL in Python?

In this article, we aim to convert the data frame into an SQL database and then try to read the content from the SQL database using SQL queries or through a table.

Convert Pandas DataFrame into SQL in Python

Below are some steps by which we can export Python dataframe to SQL file in Python:

Step 1: Installation

To deal with SQL in Python, we need to install the Sqlalchemy library using the below-mentioned command by running it in cmd:

pip install sqlalchemy

Step 2: Creating Pandas DataFrame

There is a need to create a pandas data frame to proceed further.

Python3

# import pandas library
import pandas as pd

# create a dataframe
# object from dictionary
dataset = pd.DataFrame({'Names': ['Abhinav', 'Aryan',
                                  'Manthan'],
                        'DOB': ['10/01/2009', '24/03/2009',
                                '28/02/2009']})
# show the dataframe
print(dataset)

Output :

     Names         DOB
0  Abhinav  10/01/2009
1    Aryan  24/03/2009
2  Manthan  28/02/2009

Step 3: Create connection to the SQlite database

After creating the dataset we need to connect the data frame to the database support which is provided for sqlite3.Connection objects. 

Python3

# importing sql library
from sqlalchemy import create_engine

# create a reference
# for sql library
engine = create_engine('sqlite://',
                       echo=False)

# attach the data frame to the sql
# with a name of the table
# as "Employee_Data"
dataset.to_sql('Employee_Data',
               con=engine)

# show the complete data
# from Employee_Data table
print(engine.execute("SELECT * FROM Employee_Data").fetchall())

Output:

[(0, 'Abhinav', '10/01/2009'), (1, 'Aryan', '24/03/2009'), 
(2, 'Manthan', '28/02/2009')]

Step 4: Adding Data to the Database

After adding the data to the database, it is visible to us in the form of records. Data can also be appended to the previously created database as shown below:

Python3

# Create a dataframe
# object from dictionary
df1 = pd.DataFrame({'Names': ['Sonia', 'Priya'],
                    'DOB': ['18/10/2009', '14/06/2009']})

# appending new data frame
# to existing data frame
df1.to_sql('Employee_Data',
           con=engine,
           if_exists='append')

# run a sql query
print(engine.execute("SELECT * FROM Employee_Data").fetchall())

Output:

[(0, 'Abhinav', '10/01/2009'), (1, 'Aryan', '24/03/2009'),
 (2, 'Manthan', '28/02/2009'), (0, 'Sonia', '18/10/2009'),
  (1, 'Priya', '14/06/2009')]

Step 5: Reading and Displaying SQL Employee Data with Pandas and Indexing by Names

As understood from the above example that although data is appended the indexing again started from 0 only when a new data frame is appended.A data frame can be transferred to the SQL database, the same way data frame can also be read from the SQL database. the return type of the read_sql is data frame. 

Python3

# reading the sql database
# with index "Names"
df2 = pd.read_sql('Employee_Data',
                  con=engine,
                  index_col='Names',
                  parse_dates=['DOB'])
# show the dataframe
print(df2)

# print new line
print()

# show the type of df2
print(type(df2))

Output :

 id        DOB
Names               
Sonia   0 2009-10-18
Priya   1 2009-06-14

Example 1: Fetching Names Column from SQL Employee Data

We can also access a particular column in a database rather than the whole table. 

Python3

# acccesing only a particular
# column from the database
df3 = pd.read_sql('Employee_Data',
                  con = engine,
                  columns = ["Names"])
# show the data
print(df3)

Output :

Names
0  Sonia
1  Priya

Example 2: Retrieving Names Column from SQL Employee Data as a List

If we want to have the data in the database in the form of a list that to is possible. 

Python3

# get a particular column
# from a database in the
# form of list
df4 = pd.read_sql('Employee_Data',
                  con=engine,
                  index_col='Names',
                  columns=["Names"])
# show the data
print(df4)

Output :

Empty DataFrame
Columns: []
Index: [Sonia, Priya]

Step 6: Executing SQL Query and Displaying the Result

It is possible to write SQL queries in python using read_sql_query() command and passing the appropriate SQL query and the connection object .

parse_dates: This parameter helps to converts the dates that were originally passed as dates from our side into the genuine dates format.

Python3

# run a sql query in the database
# and store result in a dataframe
df5 = pd.read_sql_query('Select DOB from Employee_Data',
                        con = engine,
                        parse_dates = ['DOB'])
# show the dataframe
print(df5)

Output :

   DOB
0 2009-10-18
1 2009-06-14