DEV Community

Cover image for Creating an SQLite Database From Excel Workbook.
Beryl Chebet
Beryl Chebet

Posted on


Creating an SQLite Database From Excel Workbook.

A database is an organized collection of rows and columns with data that are related to each other. Sometimes you have an excel worksheet and would like to query it using SQL. This post takes you through using Pandas to create an SQLite database from an excel sheet. So let's jump into it .

Step 1:Load the dataset to Pandas

To load the dataset from excel to pandas use the following commands:

      import pandas as pd 
      import sqlite3
      df=pd.read_excel('path to file')```
Enter fullscreen mode Exit fullscreen mode

Import pandas as pd tells python to bring the pandas data analysis library into your current environment. pd portion of the code tells python to give pandas the alias pd. This means you can use pandas by typing pd. Import sqlite3 provides an SQL-like interface to read,query and write SQL databases from python.df=pd.read_excel('path to file') tells pandas to read the excel document in a specific location.

Step 2: Create SQLite database using sqlite3.connect()

The following sample code creates an empty database file called Day.db and assigns it to a variable db_conn

Enter fullscreen mode Exit fullscreen mode

This provides an interface for working with the SQLite database.

Step 3:Create a cursor object to run the SQL code that will create the data tables.

Cursor is an instance using which you can invoke methods that execute SQLite statements from the result sets of the queries i.e makes the connection for executing SQL queries on the SQLite database.

Enter fullscreen mode Exit fullscreen mode

Step 4:Create a table

This is a command used to create a table called DayofWeeek with new columns Day and Sum.The data type for each column is specified to the right of the column name.

```create_table="CREATE TABLE DayofWeek(Day TEXT,Sum INT)```
Enter fullscreen mode Exit fullscreen mode

The column names must match those stored in the dataframe.

Step 6:Moving from pandas dataframe to SQL table

.to_sql command is used to write records stored in a dataframe to a SQL database.DayofWeek specifies the the name of SQL table created.
if_exists='append' inserts new values into the existing table DayofWeek.index=False to avoid bringing the dataframe index as a column.

```df.to_sql('DayofWeek',db_conn,if_exists='append', index=False)```
Enter fullscreen mode Exit fullscreen mode

You can run your sql commands on pandas for example:

pd.read_sql("SELECT * FROM DayofWeek",db_conn)

Enter fullscreen mode Exit fullscreen mode

Top comments (0)

Advice For Junior Developers

Advice from a career of 15+ years for new and beginner developers just getting started on their journey.