myedibleenso · August 29, 2025 00:13 · May 26, 2024 · May 26, 2024 · May 7, 2024 · May 7, 2024
diff --git a/README.md b/README.md
@@ -13,23 +13,26 @@ db_name = "lynch"
 conn = sqlite3.connect(f"{db_name}.db")
 
 
-def mk_gsheets_url(doc_id: str, sheet_num: int) -> str: 
+def mk_gsheets_url(doc_id: str, gid: 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)}"
+  return f"https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key={doc_id}&exportFormat=csv&gid={str(gid)}"
 
 
-def populate_db(table_name: str, doc_id: str, sheet_num: int, conn) -> None:
+def populate_db(table_name: str, doc_id: str, gid: int, conn) -> pd.core.frame.DataFrame:
   """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 = pd.read_csv(mk_gsheets_url(doc_id=doc_id, gid=gid))
   df.to_sql(name=table_name, con=conn, if_exists="replace")
+  return df
 
 # 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)
+tables = [("lynch", 0), ("actors", 485988046)]
+for table_name, gid in tables:
+  _ = populate_db(table_name=table_name, doc_id="1sVvfAGxBdr2R2aiCkG4_STAvIJLBwH3HTADElsagey4", gid=gid, conn=conn)
 
 
 # display contents of table
-conn.execute(f"SELECT * FROM '{db_name}';").fetchall()
+for table, _ in tables:
+  print(conn.execute(f"SELECT * FROM '{table}';").fetchall())
 ```
 
 To write several DFs to a db, see the following solution: https://stackoverflow.com/q/68705428
diff --git a/README.md b/README.md
@@ -4,19 +4,29 @@ The URL structure to view a Google shet is `https://docs.google.com/spreadsheets
 
 ```python
 import pandas as pd
+import sqlite3
 
-mk_gsheets_url = lambda doc_id: f"https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key={doc_id}&exportFormat=csv"
-
-df = pd.read_csv(mk_gsheets_url("1sVvfAGxBdr2R2aiCkG4_STAvIJLBwH3HTADElsagey4"))
+# pip install "pandas[excel]"
 
 # connect to database
-import sqlite3
-
 db_name = "lynch"
 conn = sqlite3.connect(f"{db_name}.db")
 
-# push the dataframe to sql 
-df.to_sql(name=db_name, con=conn, if_exists="replace")
+
+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()

diff --git a/README.md b/README.md
@@ -20,4 +20,6 @@ df.to_sql(name=db_name, con=conn, if_exists="replace")
 
 # 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
diff --git a/README.md b/README.md
@@ -1,4 +1,4 @@
-The URL structure to download a Google sheet to a csv is `https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=yourDocId&exportFormat=csv`
+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`
 

diff --git a/README.md b/README.md
@@ -1,5 +1,6 @@
-The format for a Google sheet URL is `https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=yourDocId&exportFormat=csv`
+The URL structure to download a Google sheet to a csv is `https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=yourDocId&exportFormat=csv`
 
+The URL structure to view a Google shet is `https://docs.google.com/spreadsheets/d/yourDocId`
 
 ```python
 import pandas as pd

diff --git a/README.md b/README.md
@@ -0,0 +1,22 @@
+The format for a Google sheet URL is `https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=yourDocId&exportFormat=csv`
+
+
+```python
+import pandas as pd
+
+mk_gsheets_url = lambda doc_id: f"https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key={doc_id}&exportFormat=csv"
+
+df = pd.read_csv(mk_gsheets_url("1sVvfAGxBdr2R2aiCkG4_STAvIJLBwH3HTADElsagey4"))
+
+# connect to database
+import sqlite3
+
+db_name = "lynch"
+conn = sqlite3.connect(f"{db_name}.db")
+
+# push the dataframe to sql 
+df.to_sql(name=db_name, con=conn, if_exists="replace")
+
+# display contents of table
+conn.execute(f"SELECT * FROM '{db_name}';").fetchall()
+```
No results found