SQLite to Google sheets and vice versa

Google sheets to SQLite and vice versa
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)
google sheet data from SQLite table

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()
Subscribe to our YouTube Channel here

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2022 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer