如何提取个人GitHub仓库的Issues并保存至数据库或Excel？

Hey there! Let me walk you through a few reliable, up-to-date ways to pull issues from your GitHub repo and get them into a database or Excel—no outdated tools required.

GitHub CLI is maintained directly by GitHub, so it's always up-to-date and reliable. Here's how to use it:

• First, install GitHub CLI (available for Windows, Mac, and Linux) and log in with gh auth login—just follow the prompts to authenticate your GitHub account.
• Run this command to export all issues (open, closed, and drafts) to a CSV file (which Excel can open directly, or you can import it into most databases):

gh issue list --repo OWNER/REPO --state all --json number,title,body,createdAt,updatedAt,assignees,labels --csv number,title,body,createdAt,updatedAt,assignees,labels > github_issues.csv

• Replace OWNER/REPO with your repo's full name (like octocat/Hello-World).
• The --json flag lets you specify exactly which fields you want to export—feel free to add or remove fields based on your needs.
• The CSV file will save to your current directory; you can drag it straight into Excel or use your database's import tool to load it.

If you need more control over formatting, want to add custom logic, or directly push issues to a database, a Python script is perfect. Here's a step-by-step example:

1. Install the required packages:

pip install pygithub pandas sqlalchemy

2. Create a script like this (replace placeholders with your details):

from github import Github
import pandas as pd
from sqlalchemy import create_engine

# Authenticate with a GitHub Personal Access Token (PAT)
# Generate one in GitHub Settings > Developer Settings > Fine-grained Tokens (give it repo read access)
g = Github("YOUR_PERSONAL_ACCESS_TOKEN")
repo = g.get_repo("OWNER/REPO")

# Fetch all issues (include closed/draft with state="all")
issues = repo.get_issues(state="all")

# Organize issue data into a structured format
issue_records = []
for issue in issues:
    # Capture only the fields you care about—customize this!
    issue_records.append({
        "issue_number": issue.number,
        "title": issue.title,
        "description": issue.body,
        "created_date": issue.created_at,
        "last_updated": issue.updated_at,
        "status": issue.state,
        "assignee": issue.assignee.login if issue.assignee else "Unassigned",
        "labels": ", ".join([label.name for label in issue.labels])
    })

# Convert to a DataFrame for easy exporting
df = pd.DataFrame(issue_records)

# Export to Excel
df.to_excel("github_issues.xlsx", index=False)

# Export directly to a database (example for MySQL—adjust for your DB type)
# Replace the connection string with your database credentials
engine = create_engine("mysql+pymysql://DB_USER:DB_PASSWORD@DB_HOST/DB_NAME")
df.to_sql("github_issues", engine, if_exists="replace", index=False)

• The Personal Access Token (PAT) is required to access private repos (and avoid rate limits on public ones). Make sure to restrict its permissions to only what you need (repo read access is enough here).
• You can easily add more data (like issue comments, reactions, or milestones) by extending the issue_records dictionary with additional fields from the PyGitHub library.

If you prefer working directly in Excel, Power Query can pull issues straight from GitHub's API:

• Open Excel, go to the Data tab > Get Data > From Other Sources > From Web.
• Enter the GitHub API URL for your repo's issues: https://api.github.com/repos/OWNER/REPO/issues?state=all
• When prompted for authentication, select Basic Authentication: use your GitHub username as the username, and your Personal Access Token as the password.
• Power Query will load the JSON data—you can use the query editor to select the fields you want, clean up the data (like expanding labels or assignee info), then load it into your Excel sheet.
• Bonus: You can set up automatic refresh to keep your Excel file updated with new issues!

Quick Tips #

• Rate Limits: GitHub's API has rate limits, but both GitHub CLI and PyGitHub handle retries automatically. For large repos, you might want to add a small delay in the Python script if you hit limits.
• Private Repos: All three methods work for private repos as long as you use a valid PAT with the right permissions.
• Custom Fields: Don't be afraid to tweak the fields you export—whether it's adding issue comments, milestone names, or author info, you can adjust all methods to fit your needs.

内容的提问来源于stack exchange，提问作者user6818056