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)