Use Data from SQLite in other commands

[daniel-butler]

As a total beginner here how could you access data from the sqlite table to run other commands.

What I am thinking is I want to get all the repos in an organization then using the repo list pull all the commit messages for each repo.

I love this project by the way!

[daniel-butler]

For the purposes below I am assuming the organization I would get all the repositories and their related commits from is called gh-organization. The github's owner id of gh-orgnization is 123456789.

github-to-sqlite  repos github.db gh-organization

I'm on a windows computer running git bash to be able to use the | command. This works for me

sqlite3 github.db "SELECT full_name FROM repos WHERE owner = '123456789';" | tr '\n\r' ' ' | xargs | { read repos; github-to-sqlite commits github.db $repos; }

On a pure linux system I think this would work because the new line character is normally \n

sqlite3 github.db "SELECT full_name FROM repos WHERE owner = '123456789';" | tr '\n' ' ' | xargs | { read repos; github-to-sqlite commits github.db $repos; }`

As expected I ran into rate limit issues #51

[simonw]

I really like the way you're using pipes here - really smart. It's similar to how I build the demo database in this GitHub Actions workflow:

github-to-sqlite/.github/workflows/deploy-demo.yml

Lines 52 to 82 in 62dfd3b

	run: |-
	sqlite-utils github.db "select full_name from repos where owner = 53015001 union select 'simonw/datasette' as full_name union select 'simonw/sqlite-utils' as full_name" \
	--csv --no-headers | while read repo;
	do github-to-sqlite releases \
	github.db $(echo $repo | tr -d '\r');
	sleep 10;
	github-to-sqlite commits \
	github.db $(echo $repo | tr -d '\r');
	sleep 10;
	github-to-sqlite tags \
	github.db $(echo $repo | tr -d '\r');
	sleep 10;
	github-to-sqlite contributors \
	github.db $(echo $repo | tr -d '\r');
	sleep 10;
	github-to-sqlite issues \
	github.db $(echo $repo | tr -d '\r');
	sleep 10;
	github-to-sqlite pull-requests \
	github.db $(echo $repo | tr -d '\r');
	sleep 10;
	github-to-sqlite issue-comments \
	github.db $(echo $repo | tr -d '\r');
	sleep 10;
	github-to-sqlite stargazers \
	github.db $(echo $repo | tr -d '\r');
	sleep 10;
	github-to-sqlite workflows \
	github.db $(echo $repo | tr -d '\r');
	sleep 10;
	done;

twitter-to-sqlite actually has a mechanism for doing this kind of thing, documented at https://github.com/dogsheep/twitter-to-sqlite#providing-input-from-a-sql-query-with---sql-and---attach

It lets you do things like:

$ twitter-to-sqlite users-lookup my.db --sql="select follower_id from following" --ids

Maybe I should add something similar to github-to-sqlite? Feels like it could be really useful.

[daniel-butler]

Yes that would be cool! I wouldn't mind helping. Is this the meat of it? https://github.com/dogsheep/twitter-to-sqlite/blob/21fc1cad6dd6348c67acff90a785b458d3a81275/twitter_to_sqlite/utils.py#L512

It looks like the cli option is added with this decorator : https://github.com/dogsheep/twitter-to-sqlite/blob/21fc1cad6dd6348c67acff90a785b458d3a81275/twitter_to_sqlite/cli.py#L14

I looked a bit at utils.py in the GitHub repository. I was surprised at the amount of manual mapping of the API response you had to do to get this to work.