import pandas as pd
import sqlite3
import pooch
from markdown import markdownorg = "jupyterhub-contrib"
# If the template has been converted to pages, then org will not have { org } structure
if "{ org }" in org:
org = "jupyter-book"# Download latest release data for Jupyter Book
file_path = pooch.retrieve(
# URL to one of Pooch's test files
url=f"https://github.com/jupyter/github-data/releases/download/latest/{org}.db",
known_hash=None,
)Downloading data from 'https://github.com/jupyter/github-data/releases/download/latest/jupyterhub-contrib.db' to file '/home/runner/.cache/pooch/b321ac68c7d270fea08d5c48d55ef545-jupyterhub-contrib.db'.
SHA256 hash of downloaded file: 18d4656ac78bf859df2ae3540cd1c2968a4bd0c4a26f767d73be8f5edefe5d20
Use this value as the 'known_hash' argument of 'pooch.retrieve' to ensure that the file hasn't changed if it is downloaded again in the future.
def df_from_sql(query, db):
con = sqlite3.connect(db)
return pd.read_sql(query, con)
con.close()repos = df_from_sql("SELECT * FROM repos;", file_path).set_index("id")
issues = df_from_sql("SELECT * FROM issues;", file_path)
issues = issues.query("state == 'open'")
# Add some metadata that will make the outputs nicer
for ix, irow in issues.iterrows():
# Add number of positive reactions
positive = 0
for ii in ["+1", "heart", "hooray"]:
positive += eval(irow["reactions"])[ii]
issues.loc[ix, 'positive'] = int(positive)
# Add the repository
url_repo = repos.loc[irow["repo"]]["html_url"]
url_repo_parts = url_repo.split("/")[-1]
issues.loc[ix, "repo"] = f"[{url_repo_parts}]({url_repo})"
# Add the URL of each issue
url = f"{url_repo}/issues/{irow['number']}"
issues.loc[ix, "mdtitle"] = f"[{irow['title']}]({url})"
# Add a short body
issues["bodyshort"] = issues["body"].map(lambda a: a.replace("#", "")[:400] if a else '')---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
File /opt/hostedtoolcache/Python/3.14.7/x64/lib/python3.14/site-packages/pandas/io/sql.py:2745, in SQLiteDatabase.execute(self, sql, params)
2744 try:
-> 2745 cur.execute(sql, *args)
2746 return cur
OperationalError: no such table: issues
The above exception was the direct cause of the following exception:
DatabaseError Traceback (most recent call last)
Cell In[5], line 2
1 repos = df_from_sql("SELECT * FROM repos;", file_path).set_index("id")
----> 2 issues = df_from_sql("SELECT * FROM issues;", file_path)
3 issues = issues.query("state == 'open'")
4
5 # Add some metadata that will make the outputs nicer
Cell In[4], line 3, in df_from_sql(query, db)
1 def df_from_sql(query, db):
2 con = sqlite3.connect(db)
----> 3 return pd.read_sql(query, con)
4 con.close()
File /opt/hostedtoolcache/Python/3.14.7/x64/lib/python3.14/site-packages/pandas/io/sql.py:702, in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize, dtype_backend, dtype)
700 with pandasSQL_builder(con) as pandas_sql:
701 if isinstance(pandas_sql, SQLiteDatabase):
--> 702 return pandas_sql.read_query(
703 sql,
704 index_col=index_col,
705 params=params,
706 coerce_float=coerce_float,
707 parse_dates=parse_dates,
708 chunksize=chunksize,
709 dtype_backend=dtype_backend,
710 dtype=dtype,
711 )
713 try:
714 _is_table_name = pandas_sql.has_table(sql)
File /opt/hostedtoolcache/Python/3.14.7/x64/lib/python3.14/site-packages/pandas/io/sql.py:2809, in SQLiteDatabase.read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype, dtype_backend)
2798 def read_query(
2799 self,
2800 sql,
(...) 2807 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
2808 ) -> DataFrame | Iterator[DataFrame]:
-> 2809 cursor = self.execute(sql, params)
2810 columns = [col_desc[0] for col_desc in cursor.description]
2812 if chunksize is not None:
File /opt/hostedtoolcache/Python/3.14.7/x64/lib/python3.14/site-packages/pandas/io/sql.py:2757, in SQLiteDatabase.execute(self, sql, params)
2754 raise ex from inner_exc
2756 ex = DatabaseError(f"Execution failed on sql '{sql}': {exc}")
-> 2757 raise ex from exc
DatabaseError: Execution failed on sql 'SELECT * FROM issues;': no such table: issuesA table of all the open issues in the jupyterhub-contrib github organization, sorted by the number of 👍 and ❤️ reactions.
issues_sorted = issues.sort_values("positive", ascending=False).head(100)[["mdtitle", "repo", "bodyshort", "positive"]]
issues_sorted = issues_sorted.rename(columns={"bodyshort": "body", "mdtitle": "title", "positive": "👍"})
def render_markdown(text):
if isinstance(text, str): # Ensure the cell content is a string
return markdown(text)
return text
md_cols = ["title", "body", "repo"]
styledict = {ii: render_markdown for ii in md_cols}
df_style = issues_sorted
styled_df = issues_sorted.style.format(styledict | {"👍": int}).hide(axis="index")
styled_df