Data exchange between Google sheets and SQLite database using Pandas Dataframe

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

Data Transfer between SQLite and Google Sheet using Pygsheets and Pandas DataFrame

Pandas Pygsheets to MySQL

From SQLite to Google sheets

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="D:\\testing\\sqlite\\my_db.db" #Change the path 
my_conn = sqlite3.connect(my_path)
print("Connected to database successfully")

Using SQLAlchemy

from sqlalchemy import create_engine 
from sqlalchemy.exc import SQLAlchemyError
my_path="D:\\testing\\sqlite\\my_db.db" #Change the path 
my_conn = create_engine("sqlite:///"+ my_path)

We will use read_sql() to create the DataFrame by using the query.

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

import pandas as pd 
try:
  query="SELECT * FROM student"
  df = pd.read_sql(query,my_conn,index_col='id')
  print(df.head()) # Print top 5 rows as sample
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print("DataFrame created successfully..")

Using the Pandas DataFrame df, we will use set_dataframe() to add the dataframe to google sheets.

From DataFrame to pygsheets

Pygsheets and Google API authorization

import pygsheets
path='G:\\My drive\\testing\\google-sheet\\creds1.json' # change path
gc=pygsheets.authorize(service_account_file=path)
sh=gc.open('my_gsheets1') # Open google sheet 
wk1=sh[0] # select first work sheet
wk1.clear() # remove previous data if any 
wk1.set_dataframe(df,(1,1),copy_index=True, extend=True)

Full code is here

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

import pandas as pd 
try:
  query="SELECT * FROM student"
  df = pd.read_sql(query,my_conn,index_col='id')
  print(df.head()) # Print top 5 rows as sample
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print("DataFrame created successfully..")

import pygsheets
path='G:\\My drive\\testing\\google-sheet\\creds1.json'
gc=pygsheets.authorize(service_account_file=path)
sh=gc.open('my_gsheets1') # Open google sheet 
wk1=sh[0] # select first work sheet
wk1.clear() # remove previous data if any 
wk1.set_dataframe(df,(1,1),copy_index=True, extend=True)

From Google sheets to SQLite table

In first part by using get_as_df() we will collect data from Google sheets and create the DataFrame.

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

import pygsheets
path='G:\\My drive\\testing\\google-sheet\\creds1.json'
gc=pygsheets.authorize(service_account_file=path)
sh=gc.open('my_gsheets1')
wk1=sh[0]
df = wk1.get_as_df(index_column=1)
print(df) 

# inserting dataframe to SQLite table:  Part 2 
import pandas as pd 
from sqlalchemy import create_engine
my_path='G:\\My Drive\\testing\\my_db\\my_db.db'
my_conn = create_engine("sqlite:///"+ my_path)
df.to_sql(con=my_conn,name='student2',if_exists='append', index=False)

## to check the data use the code below ##
r_set=my_conn.execute('SELECT * from student2');
for row in r_set:
    print(row)

Pygsheets and google API authorization set_dataframe()

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

SQLite to Google sheets and vice versa

From SQLite to Google sheets

From DataFrame to pygsheets

From Google sheets to SQLite table

Subhendu Mohapatra