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:

   ```python 
      import pandas as pd 
      import sqlite3
      df=pd.read_excel('path to file')```

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

   ```db_conn=sqlite3.connect('Day.db')```

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.

```cur=db_conn.cursor()```

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)```

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)```

You can run your sql commands on pandas for example:

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