12 Jun 2023

GitHub: Get a CSV containing my pull requests (PRs)

I wanted to get a list of my GitHub pull requests (PRs) and commits, which was surprisingly difficult to figure out how to do. I’m sure it must be possible to get this data from the API, but it was a lot easier to figure out how to do so with the GitHub CLI.

This blog post explains how to use the GitHub CLI on the Mac OS terminal. If you’re trying to do this on Windows, see Get a CSV of all my pull requests from Github using Github CLI and PowerShell.

So I installed that and it had to be authorised via the GitHub website the first time.

I could then get a list of my PRs by running the following command:

gh search prs  \
--author=@me \
--limit 10 \
--json title,repository,closedAt,url | jq -c '.[]'

Output

{"closedAt":"2023-06-12T13:09:43Z","repository":{"name":"dev.startree.ai","nameWithOwner":"startreedata/dev.startree.ai"},"title":"RT data ingestion explanation","url":"https://github.com/startreedata/dev.startree.ai/pull/173"}
{"closedAt":"0001-01-01T00:00:00Z","repository":{"name":"sourcegraph","nameWithOwner":"sourcegraph/sourcegraph"},"title":"Small typo","url":"https://github.com/sourcegraph/sourcegraph/pull/52604"}
{"closedAt":"2023-05-13T16:40:59Z","repository":{"name":"venkat","nameWithOwner":"mneedham/venkat"},"title":"pull out language","url":"https://github.com/mneedham/venkat/pull/3"}
{"closedAt":"2023-05-05T06:00:29Z","repository":{"name":"dev.startree.ai","nameWithOwner":"startreedata/dev.startree.ai"},"title":"Update README.md","url":"https://github.com/startreedata/dev.startree.ai/pull/149"}
{"closedAt":"2023-05-10T01:22:35Z","repository":{"name":"risingwave-docs","nameWithOwner":"risingwavelabs/risingwave-docs"},"title":"Update risingwave-kubernetes.md","url":"https://github.com/risingwavelabs/risingwave-docs/pull/792"}
{"closedAt":"2023-04-04T13:08:18Z","repository":{"name":"pinot-recipes","nameWithOwner":"startreedata/pinot-recipes"},"title":"Update README.md","url":"https://github.com/startreedata/pinot-recipes/pull/10"}
{"closedAt":"2023-03-30T10:07:52Z","repository":{"name":"pinot-recipes","nameWithOwner":"startreedata/pinot-recipes"},"title":"Update README.md","url":"https://github.com/startreedata/pinot-recipes/pull/8"}
{"closedAt":"2023-03-30T10:07:13Z","repository":{"name":"pinot-recipes","nameWithOwner":"startreedata/pinot-recipes"},"title":"Update README.md","url":"https://github.com/startreedata/pinot-recipes/pull/7"}
{"closedAt":"0001-01-01T00:00:00Z","repository":{"name":"pinot","nameWithOwner":"apache/pinot"},"title":"Consumer lag unknown","url":"https://github.com/apache/pinot/pull/10310"}
{"closedAt":"2023-02-13T23:30:29Z","repository":{"name":"pinot","nameWithOwner":"apache/pinot"},"title":"Bug with time based segment relocator on real-time segments","url":"https://github.com/apache/pinot/pull/10272"}

I want those in CSV format though, which we can do by putting all the values into an array and then calling @csv:

gh search prs  \
--author=@me \
--limit 10 \
--json title,repository,closedAt,url |
jq -r '.[]| [.title, .url, .repository.nameWithOwner, .closedAt] | @csv'

Table 1. Output
RT data ingestion explanation	https://github.com/startreedata/dev.startree.ai/pull/173	startreedata/dev.startree.ai	2023-06-12T13:09:43Z
Small typo	https://github.com/sourcegraph/sourcegraph/pull/52604	sourcegraph/sourcegraph	0001-01-01T00:00:00Z
Pull out language	https://github.com/mneedham/venkat/pull/3	mneedham/venkat	2023-05-13T16:40:59Z
Update README.md	https://github.com/startreedata/dev.startree.ai/pull/149	startreedata/dev.startree.ai	2023-05-05T06:00:29Z
Update risingwave-kubernetes.md	https://github.com/risingwavelabs/risingwave-docs/pull/792	risingwavelabs/risingwave-docs	2023-05-10T01:22:35Z
Update README.md	https://github.com/startreedata/pinot-recipes/pull/10	startreedata/pinot-recipes	2023-04-04T13:08:18Z
Update README.md	https://github.com/startreedata/pinot-recipes/pull/8	startreedata/pinot-recipes	2023-03-30T10:07:52Z
Update README.md	https://github.com/startreedata/pinot-recipes/pull/7	startreedata/pinot-recipes	2023-03-30T10:07:13Z
Consumer lag unknown	https://github.com/apache/pinot/pull/10310	apache/pinot	0001-01-01T00:00:00Z
Bug with time based segment relocator on real-time segments	https://github.com/apache/pinot/pull/10272	apache/pinot	2023-02-13T23:30:29Z

You can see that I don’t actually create that many pull requests as I tend to commit things straight to main! As a bonus, let’s have a look at my commits as well:

gh search commits  \
--author=@me --limit 1 \
--author-date=">2022-01-01" --order desc --sort author-date \
--visibility public --json repository,sha,commit |
jq  '.[]'

Output

{
  "commit": {
    "author": {
      "date": "2023-06-12T10:59:13+01:00",
      "email": "m.h.needham@gmail.com",
      "name": "Mark Needham"
    },
    "comment_count": 0,
    "committer": {
      "date": "2023-06-12T10:59:13+01:00",
      "email": "m.h.needham@gmail.com",
      "name": "Mark Needham"
    },
    "message": "Merge branch 'main' of github.com:startreedata/pinot-recipes",
    "tree": {
      "sha": "71da7e300e8ed035c6877422e8dd30266d7abc2c"
    }
  },
  "repository": {
    "description": "This repository contains recipes for Apache Pinot.",
    "fullName": "startreedata/pinot-recipes",
    "id": "R_kgDOGdrbcQ",
    "isFork": false,
    "isPrivate": false,
    "name": "pinot-recipes",
    "owner": {
      "id": "MDEyOk9yZ2FuaXphdGlvbjc2NjAxMzMz",
      "is_bot": false,
      "login": "startreedata",
      "type": "Organization",
      "url": "https://github.com/startreedata"
    },
    "url": "https://github.com/startreedata/pinot-recipes"
  },
  "sha": "373dc891a04ecb9dba009394ce9266a07d8ac9c0"
}

Let’s pull out just the bits we want:

gh search commits  \
--author=@me --limit 10 \
--author-date=">2022-01-01" --order desc --sort author-date \
--visibility public --json repository,sha,commit |
jq -r '.[] | [.commit.author.date, .commit.message, .repository.fullName] | @csv'

Table 2. Output
2023-06-12T10:59:13+01:00	Merge branch 'main' of github.com:startreedata/pinot-recipes	startreedata/pinot-recipes
2023-06-12T10:59:01+01:00	fixed host	startreedata/pinot-recipes
2023-06-09T09:30:53+01:00	update	mneedham/hugo-blog
2023-06-09T07:48:13+01:00	updates	mneedham/mneedham.github.io
2023-06-09T07:40:23+01:00	linkedin	mneedham/mneedham.github.io
2023-06-09T07:03:28+01:00	updates	mneedham/hugo-blog
2023-06-09T06:56:00+01:00	duckdb	mneedham/mneedham.github.io
2023-06-06T16:46:23+01:00	Update README.md	startreedata/pinot-recipes
2023-06-06T16:45:41+01:00	Update README.md	startreedata/pinot-recipes
2023-06-06T16:45:22+01:00	Update README.md	startreedata/pinot-recipes

About the author

I'm currently working on short form content at ClickHouse. I publish short 5 minute videos showing how to solve data problems on YouTube @LearnDataWithMark. I previously worked on graph analytics at Neo4j, where I also co-authored the O'Reilly Graph Algorithms Book with Amy Hodler.