Data exchange between SQLite database and excel sheet using Pandas Dataframe

Interactive Companion Jupyter Notebook

Explore interactive code cells, transfer data between SQLite and Excel files using Pandas, and test SQLAlchemy 2.0+ context manager connections directly in Google Colab.

View on GitHub

We can transfer data from Excel to SQLite database table and from SQLite to Excel by using Pandas DataFrame.

Data Transfer between SQLite and Excel Page using Pandas DataFrame and SQLAlchemy connection

Pandas SQLite

From SQLite to Excel

We will create a Pandas DataFrame by using data from SQLite student table. ( download or create SQLite database with student table)

More on Connection to SQLite database

import sqlite3
my_path = 'G:\My Drive\testing\my_db\my_db.db' # update path
my_conn = sqlite3.connect(my_path)
print("Connected to database successfully")

Using SQLAlchemy (2.0+ Context Manager Standard)

from sqlalchemy import create_engine 
from sqlalchemy.exc import SQLAlchemyError
my_path = 'G:\My Drive\testing\my_db\my_db.db' # update path 
my_conn = create_engine("sqlite:///" + my_path)

We will use read_sql() to create the DataFrame by using the query.
We are using try except code block to handle errors if any.
Using the Pandas DataFrame df, we will use to_sql() to add the dataframe to Excel sheet.

from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
import pandas as pd

my_path = 'G:\My Drive\testing\my_db\my_db.db'
my_conn = create_engine("sqlite:///" + my_path)

try:
  q = "SELECT * FROM student" # query to collect record 
  with my_conn.connect() as connection:
    df = pd.read_sql(q, connection, index_col='id') # create DataFrame
  print(df.head()) # Print top 5 rows as sample
  df.to_excel('D:\student.xlsx') # create the excel file 
except SQLAlchemyError as e:
  print(e)
else:
  print("DataFrame created successfully..")

From Excel to SQLite table

In first part by using read_excel() we will collect data from Excel page and create the DataFrame.

In second part we will use to_sql() to create and store data from the Dataframe to SQLite table ( student3 table ) .

from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError
import pandas as pd 

my_path = 'G:\My Drive\testing\my_db\my_db.db' # update path
my_conn = create_engine("sqlite:///" + my_path) # connection object

try:
  df = pd.read_excel('D:\student.xlsx') # create DataFrame by reading Excel
  print(df.head()) # Print top 5 rows as sample
  with my_conn.begin() as connection:
    df.to_sql(con=connection, name='student3', if_exists='append') # create table
except SQLAlchemyError as e:
  print(e)
else: # show all records to confirm
  with my_conn.connect() as connection:
    r_set = connection.execute(text('SELECT * FROM student3'))
    for row in r_set:
      print(row)

SQLite to Excel using Colab platform
From Google sheet to SQLite using Pygsheets and google API authorization From Google sheet to MySQL and vice versa
Database table to Excel file using xlsxwriter

Pandas DataFrame Python- Tutorials

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

Excel to SQLite database and vice versa

Interactive Companion Jupyter Notebook

From SQLite to Excel

From Excel to SQLite table

Subhendu Mohapatra