The URL structure to download a Google sheet to a csv is https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=yourDocId&exportFormat=csv&sheet=1
The URL structure to view a Google shet is https://docs.google.com/spreadsheets/d/yourDocId
import pandas as pd
import sqlite3
# pip install "pandas[excel]"
# connect to database
db_name = "lynch"
conn = sqlite3.connect(f"{db_name}.db")
def mk_gsheets_url(doc_id: str, sheet_num: int) -> str:
"""Create a CSV download link for a single sheet of a Google Sheet"""
return f"https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key={doc_id}&exportFormat=csv&sheet={str(sheet_num)}"
def populate_db(table_name: str, doc_id: str, sheet_num: int, conn) -> None:
"""Populate a SQLite DB from a Google Sheet"""
df = pd.read_csv(mk_gsheets_url(doc_id=doc_id, str(sheet_num=sheet_num)))
df.to_sql(name=table_name, con=conn, if_exists="replace")
# populate DB from David Lynch spreadsheet
for i, table_name in enumerate(["lynch", "actors"]):
populate_db(table_name=table_name, doc_id="1sVvfAGxBdr2R2aiCkG4_STAvIJLBwH3HTADElsagey4", sheet_num=i+1, conn=conn)
# display contents of table
conn.execute(f"SELECT * FROM '{db_name}';").fetchall()To write several DFs to a db, see the following solution: https://stackoverflow.com/q/68705428