issue_comments

Table actions

486 rows where author_association = "MEMBER" sorted by author_association

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: reactions, created_at (date), updated_at (date)

author_association 1 ✖

  • MEMBER · 486 ✖
id html_url issue_url node_id user created_at updated_at author_association ▼ body reactions issue performed_via_github_app
513437463 https://github.com/dogsheep/healthkit-to-sqlite/issues/1#issuecomment-513437463 https://api.github.com/repos/dogsheep/healthkit-to-sqlite/issues/1 MDEyOklzc3VlQ29tbWVudDUxMzQzNzQ2Mw== simonw 9599 2019-07-20T05:19:59Z 2019-07-20T05:19:59Z MEMBER I ran xml_analyser against the XML HealthKit `export.xml` file and got the following results: ```python { 'ActivitySummary': {'attr_counts': {'activeEnergyBurned': 980, 'activeEnergyBurnedGoal': 980, 'activeEnergyBurnedUnit': 980, 'appleExerciseTime': 980, 'appleExerciseTimeGoal': 980, 'appleStandHours': 980, 'appleStandHoursGoal': 980, 'dateComponents': 980}, 'child_counts': {}, 'count': 980, 'parent_counts': {'HealthData': 980}}, 'Correlation': {'attr_counts': {'creationDate': 1, 'endDate': 1, 'sourceName': 1, 'sourceVersion': 1, 'startDate': 1, 'type': 1}, 'child_counts': {'MetadataEntry': 1, 'Record': 2}, 'count': 1, 'parent_counts': {'HealthData': 1}}, 'ExportDate': {'attr_counts': {'value': 1}, 'child_counts': {}, 'count': 1, 'parent_counts': {'HealthData': 1}}, 'HealthData': {'attr_counts': {'locale': 1}, 'child_counts': {'ActivitySummary': 980, 'Correlation': 1, 'ExportDate': 1, 'Me': 1, 'Record': 2672231, 'Workout': 663}, 'count': 1, 'parent_counts': {}}, 'HeartRateVariabilityMetadataList': {'attr_counts': {}, 'child_counts': {'InstantaneousBeatsPerMinute': 93653}, 'count': 2318, … {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Use XML Analyser to figure out the structure of the export XML 470637068  
513439411 https://github.com/dogsheep/healthkit-to-sqlite/issues/2#issuecomment-513439411 https://api.github.com/repos/dogsheep/healthkit-to-sqlite/issues/2 MDEyOklzc3VlQ29tbWVudDUxMzQzOTQxMQ== simonw 9599 2019-07-20T05:58:57Z 2019-07-20T05:58:57Z MEMBER ```python 'Workout': {'attr_counts': {'creationDate': 663, 'device': 230, 'duration': 663, 'durationUnit': 663, 'endDate': 663, 'sourceName': 663, 'sourceVersion': 663, 'startDate': 663, 'totalDistance': 663, 'totalDistanceUnit': 663, 'totalEnergyBurned': 663, 'totalEnergyBurnedUnit': 663, 'workoutActivityType': 663}, 'child_counts': {'MetadataEntry': 1928, 'WorkoutEvent': 2094, 'WorkoutRoute': 340}, 'count': 663, 'parent_counts': {'HealthData': 663}}, 'WorkoutEvent': {'attr_counts': {'date': 2094, 'duration': 837, 'durationUnit': 837, 'type': 2094}, 'child_counts': {}, 'count': 2094, 'parent_counts': {'Workout': 2094}}, 'WorkoutRoute': {'attr_counts': {'creationDate': 340, 'endDate': 340, 'sourceName': 340, 'sourceVersion': 340, 'startDate': 340}, 'child_counts': {'Location': 398683, 'MetadataEntry': 546}, 'count': 340, 'parent_counts': {'Workout': 340}}} ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Import workouts 470637152  
513440090 https://github.com/dogsheep/healthkit-to-sqlite/issues/4#issuecomment-513440090 https://api.github.com/repos/dogsheep/healthkit-to-sqlite/issues/4 MDEyOklzc3VlQ29tbWVudDUxMzQ0MDA5MA== simonw 9599 2019-07-20T06:11:50Z 2019-07-20T06:11:50Z MEMBER Some examples: https://github.com/dogsheep/healthkit-to-sqlite/blob/d016e70c31cf84ba0f5ec3102546db54a51aaffb/tests/export.xml#L4-L13 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Import Records 470640505  
513514978 https://github.com/dogsheep/healthkit-to-sqlite/issues/5#issuecomment-513514978 https://api.github.com/repos/dogsheep/healthkit-to-sqlite/issues/5 MDEyOklzc3VlQ29tbWVudDUxMzUxNDk3OA== simonw 9599 2019-07-21T02:55:12Z 2019-07-21T02:55:12Z MEMBER I'm going to show this by default. Users can pass `-s` or `--silent` to disable the progress bar. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Add progress bar 470691622  
513625406 https://github.com/dogsheep/healthkit-to-sqlite/issues/5#issuecomment-513625406 https://api.github.com/repos/dogsheep/healthkit-to-sqlite/issues/5 MDEyOklzc3VlQ29tbWVudDUxMzYyNTQwNg== simonw 9599 2019-07-22T03:20:16Z 2019-07-22T03:20:16Z MEMBER It now renders like this: ``` Importing from HealthKit [#-----------------------------------] 5% 00:01:33 ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Add progress bar 470691622  
513626742 https://github.com/dogsheep/healthkit-to-sqlite/issues/6#issuecomment-513626742 https://api.github.com/repos/dogsheep/healthkit-to-sqlite/issues/6 MDEyOklzc3VlQ29tbWVudDUxMzYyNjc0Mg== simonw 9599 2019-07-22T03:28:55Z 2019-07-22T03:28:55Z MEMBER Here's what it looks like now as separate tables: <img width="1050" alt="hello9_and_Populate__endpoint__key_in_ASGI_scope_·_Issue__537_·_simonw_datasette" src="https://user-images.githubusercontent.com/9599/61604322-eba05000-abf5-11e9-8f8b-d0204e9a705a.png"> {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Break up records into different tables for each type 470856782  
514496725 https://github.com/dogsheep/healthkit-to-sqlite/issues/7#issuecomment-514496725 https://api.github.com/repos/dogsheep/healthkit-to-sqlite/issues/7 MDEyOklzc3VlQ29tbWVudDUxNDQ5NjcyNQ== simonw 9599 2019-07-24T06:20:59Z 2019-07-24T06:20:59Z MEMBER I'm using https://pypi.org/project/memory-profiler/ to explore this in more detail: ``` $ pip install memory-profiler matplotlib ``` Then: ``` $ mprof run healthkit-to-sqlite ~/Downloads/healthkit-export.zip healthkit.db $ mprof plot ``` <img width="1128" alt="Screen Shot 2019-07-24 at 8 17 06 AM" src="https://user-images.githubusercontent.com/9599/61769849-f3294a00-adeb-11e9-9026-02c24209530f.png"> {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Script uses a lot of RAM 472097220  
514498221 https://github.com/dogsheep/healthkit-to-sqlite/issues/7#issuecomment-514498221 https://api.github.com/repos/dogsheep/healthkit-to-sqlite/issues/7 MDEyOklzc3VlQ29tbWVudDUxNDQ5ODIyMQ== simonw 9599 2019-07-24T06:26:49Z 2019-07-24T06:26:49Z MEMBER Adding `el.clear()` got me a huge improvement: <img width="1128" alt="Screen Shot 2019-07-24 at 8 23 26 AM" src="https://user-images.githubusercontent.com/9599/61770130-c3c70d00-adec-11e9-886a-93a2cea5bf4b.png"> {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Script uses a lot of RAM 472097220  
514500253 https://github.com/dogsheep/healthkit-to-sqlite/issues/7#issuecomment-514500253 https://api.github.com/repos/dogsheep/healthkit-to-sqlite/issues/7 MDEyOklzc3VlQ29tbWVudDUxNDUwMDI1Mw== simonw 9599 2019-07-24T06:34:28Z 2019-07-24T06:34:28Z MEMBER Clearing the root element each time saved even more: <img width="1128" alt="Screen Shot 2019-07-24 at 8 30 38 AM" src="https://user-images.githubusercontent.com/9599/61770555-d3932100-aded-11e9-8ffe-bebd682f94ed.png"> {"total_count": 2, "+1": 0, "-1": 0, "laugh": 0, "hooray": 2, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Script uses a lot of RAM 472097220  
515226724 https://github.com/dogsheep/healthkit-to-sqlite/issues/9#issuecomment-515226724 https://api.github.com/repos/dogsheep/healthkit-to-sqlite/issues/9 MDEyOklzc3VlQ29tbWVudDUxNTIyNjcyNA== simonw 9599 2019-07-25T21:46:01Z 2019-07-25T21:46:01Z MEMBER I can work around this here (prior to the fix in sqlite-utils) by setting the batch size to something a bit lower here. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Too many SQL variables 472429048  
515322294 https://github.com/dogsheep/healthkit-to-sqlite/issues/9#issuecomment-515322294 https://api.github.com/repos/dogsheep/healthkit-to-sqlite/issues/9 MDEyOklzc3VlQ29tbWVudDUxNTMyMjI5NA== simonw 9599 2019-07-26T06:07:12Z 2019-07-26T06:07:12Z MEMBER @tholo this should be fixed in just-released version 0.3.2 - could you run a `pip install -U healthkit-to-sqlite` and let me know if it works for you now? {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Too many SQL variables 472429048  
526701674 https://github.com/dogsheep/swarm-to-sqlite/issues/2#issuecomment-526701674 https://api.github.com/repos/dogsheep/swarm-to-sqlite/issues/2 MDEyOklzc3VlQ29tbWVudDUyNjcwMTY3NA== simonw 9599 2019-08-30T18:24:26Z 2019-08-30T18:24:26Z MEMBER I renamed `--file` to `--load` in 0e5b6025c6f9823ff81aa8aae1cbff5c45e57baf {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} --save option to dump checkins to a JSON file on disk 487598468  
526853542 https://github.com/dogsheep/swarm-to-sqlite/issues/4#issuecomment-526853542 https://api.github.com/repos/dogsheep/swarm-to-sqlite/issues/4 MDEyOklzc3VlQ29tbWVudDUyNjg1MzU0Mg== simonw 9599 2019-08-31T18:06:32Z 2019-08-31T18:06:32Z MEMBER https://your-foursquare-oauth-token.glitch.me/ Source code: https://glitch.com/~your-foursquare-oauth-token {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Online tool for getting a Foursquare OAuth token 487601121  
527200332 https://github.com/dogsheep/swarm-to-sqlite/issues/3#issuecomment-527200332 https://api.github.com/repos/dogsheep/swarm-to-sqlite/issues/3 MDEyOklzc3VlQ29tbWVudDUyNzIwMDMzMg== simonw 9599 2019-09-02T16:32:20Z 2019-09-02T16:32:39Z MEMBER Also needed: an option for "fetch all checkins created within the last X days". This should help provide support for that Swarm feature where you can retroactively checkin to places in the past. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Option to fetch only checkins more recent than the current max checkin 487600595  
527682713 https://github.com/dogsheep/twitter-to-sqlite/issues/4#issuecomment-527682713 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/4 MDEyOklzc3VlQ29tbWVudDUyNzY4MjcxMw== simonw 9599 2019-09-03T23:48:57Z 2019-09-03T23:48:57Z MEMBER One interesting challenge here is that the JSON format for tweets in the archive is subtly different from the JSON format currently returned by the API. If we want to keep the tweets in the same database table (which feels like the right thing to me) we'll need to handle this. One thing we can do is have a column for `from_archive` which is set to 1 for tweets that were recovered from the archive. We can also ensure that tweets from the API always over-write the version that came from the archive (using `.upsert()`) while tweets from the archive use `.insert(..., ignore=True)` to avoid over-writing a better version that came from the API. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command for importing data from a Twitter Export file 488835586  
527684202 https://github.com/dogsheep/twitter-to-sqlite/issues/5#issuecomment-527684202 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/5 MDEyOklzc3VlQ29tbWVudDUyNzY4NDIwMg== simonw 9599 2019-09-03T23:56:28Z 2019-09-03T23:56:28Z MEMBER I previously used betamax here: https://github.com/simonw/github-contents/blob/master/test_github_contents.py {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Write tests that simulate the Twitter API 488874815  
527954898 https://github.com/dogsheep/twitter-to-sqlite/issues/2#issuecomment-527954898 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/2 MDEyOklzc3VlQ29tbWVudDUyNzk1NDg5OA== simonw 9599 2019-09-04T15:31:46Z 2019-09-04T15:31:46Z MEMBER I'm going to call this `twitter-to-sqlite user-timeline` to reflect the language used to describe the API endpoint: https://developer.twitter.com/en/docs/tweets/timelines/api-reference/get-statuses-user_timeline.html {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} "twitter-to-sqlite user-timeline" command for pulling tweets by a specific user 488833698  
527955302 https://github.com/dogsheep/twitter-to-sqlite/issues/2#issuecomment-527955302 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/2 MDEyOklzc3VlQ29tbWVudDUyNzk1NTMwMg== simonw 9599 2019-09-04T15:32:39Z 2019-09-04T15:32:39Z MEMBER Rate limit is 900 / 15 minutes which is 1 call per second. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} "twitter-to-sqlite user-timeline" command for pulling tweets by a specific user 488833698  
527990908 https://github.com/dogsheep/twitter-to-sqlite/issues/2#issuecomment-527990908 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/2 MDEyOklzc3VlQ29tbWVudDUyNzk5MDkwOA== simonw 9599 2019-09-04T16:57:24Z 2019-09-04T16:57:24Z MEMBER I just tried this using `max_id=` pagination as described in [Working with timelines](https://developer.twitter.com/en/docs/tweets/timelines/guides/working-with-timelines) and I got back all 17,759 of my tweets. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} "twitter-to-sqlite user-timeline" command for pulling tweets by a specific user 488833698  
529239307 https://github.com/dogsheep/twitter-to-sqlite/issues/8#issuecomment-529239307 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/8 MDEyOklzc3VlQ29tbWVudDUyOTIzOTMwNw== simonw 9599 2019-09-08T20:36:49Z 2019-09-08T20:36:49Z MEMBER `--attach` can optionally take a name for the database connection alias like this: $ twitter-to-sqlite users-lookup users.db --attach foo:attending.db ... If you omit the `alias:` bit the stem of the database (without the file extension) will be used. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} --sql and --attach options for feeding commands from SQL queries 490803176  
529240286 https://github.com/dogsheep/twitter-to-sqlite/issues/8#issuecomment-529240286 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/8 MDEyOklzc3VlQ29tbWVudDUyOTI0MDI4Ng== simonw 9599 2019-09-08T20:48:33Z 2019-09-08T20:48:33Z MEMBER ```ATTACH DATABASE "file:blah.db?mode=ro" AS foo``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} --sql and --attach options for feeding commands from SQL queries 490803176  
530028567 https://github.com/dogsheep/twitter-to-sqlite/issues/9#issuecomment-530028567 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/9 MDEyOklzc3VlQ29tbWVudDUzMDAyODU2Nw== simonw 9599 2019-09-10T16:59:25Z 2019-09-10T16:59:25Z MEMBER By default in SQLite foreign key constraints are not enforced (you need to run `PRAGMA foreign_keys = ON;` to enforce them). We will take advantage of this - even though the `following` table has foreign keys against user we will allow IDs to populate that table without a corresponding user record. In the future we may add a command that can backfill missing user records. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} followers-ids and friends-ids subcommands 491791152  
530417631 https://github.com/dogsheep/twitter-to-sqlite/issues/8#issuecomment-530417631 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/8 MDEyOklzc3VlQ29tbWVudDUzMDQxNzYzMQ== simonw 9599 2019-09-11T14:52:44Z 2019-09-14T19:09:22Z MEMBER - [x] This needs documentation. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} --sql and --attach options for feeding commands from SQL queries 490803176  
531404891 https://github.com/dogsheep/twitter-to-sqlite/issues/8#issuecomment-531404891 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/8 MDEyOklzc3VlQ29tbWVudDUzMTQwNDg5MQ== simonw 9599 2019-09-13T22:01:57Z 2019-09-13T22:01:57Z MEMBER I also wrote about this in https://simonwillison.net/2019/Sep/13/weeknotestwitter-sqlite-datasette-rure/ {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} --sql and --attach options for feeding commands from SQL queries 490803176  
531516956 https://github.com/dogsheep/github-to-sqlite/issues/3#issuecomment-531516956 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/3 MDEyOklzc3VlQ29tbWVudDUzMTUxNjk1Ng== simonw 9599 2019-09-14T21:56:31Z 2019-09-14T21:56:31Z MEMBER https://api.github.com/users/simonw/repos It would be useful to be able to fetch stargazers, forks etc as well. Not sure if that should be a separate command or a `--stargazers` option to this command. Probably a separate command since `issues` is a separate command already. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command to fetch all repos belonging to a user or organization 493670426  
531517083 https://github.com/dogsheep/github-to-sqlite/issues/3#issuecomment-531517083 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/3 MDEyOklzc3VlQ29tbWVudDUzMTUxNzA4Mw== simonw 9599 2019-09-14T21:58:42Z 2019-09-14T21:58:42Z MEMBER Split stargazers into #4 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command to fetch all repos belonging to a user or organization 493670426  
531517138 https://github.com/dogsheep/github-to-sqlite/issues/4#issuecomment-531517138 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/4 MDEyOklzc3VlQ29tbWVudDUzMTUxNzEzOA== simonw 9599 2019-09-14T21:59:59Z 2019-09-14T21:59:59Z MEMBER Paginate through https://api.github.com/repos/simonw/datasette/stargazers Send `Accept: application/vnd.github.v3.star+json` to get the `starred_at` dates. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command to fetch stargazers for one or more repos 493670730  
538711918 https://github.com/dogsheep/twitter-to-sqlite/issues/11#issuecomment-538711918 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/11 MDEyOklzc3VlQ29tbWVudDUzODcxMTkxOA== simonw 9599 2019-10-06T04:54:17Z 2019-10-06T04:54:17Z MEMBER Shipped in 0.6. Here's the documentation: https://github.com/dogsheep/twitter-to-sqlite#capturing-tweets-in-real-time-with-track-and-follow {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Commands for recording real-time tweets from the streaming API 503045221  
538804815 https://github.com/dogsheep/twitter-to-sqlite/issues/13#issuecomment-538804815 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/13 MDEyOklzc3VlQ29tbWVudDUzODgwNDgxNQ== simonw 9599 2019-10-07T00:33:49Z 2019-10-07T00:33:49Z MEMBER Documentation: https://github.com/dogsheep/twitter-to-sqlite#retrieve-tweets-in-bulk {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} statuses-lookup command 503085013  
538847446 https://github.com/dogsheep/pocket-to-sqlite/issues/1#issuecomment-538847446 https://api.github.com/repos/dogsheep/pocket-to-sqlite/issues/1 MDEyOklzc3VlQ29tbWVudDUzODg0NzQ0Ng== simonw 9599 2019-10-07T05:41:17Z 2019-10-07T05:41:17Z MEMBER Prototype code: ```python offset = 0 fetched = [] size = 500 while True: page = requests.get("https://getpocket.com/v3/get", { "consumer_key": consumer_key, "access_token": access_token, "sort": "oldest", "detailType": "complete", "count": size, "offset": offset, }).json() print(offset) fetched.append(page) offset += size if not len(page["list"]): break ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Use better pagination (and implement progress bar) 503233021  
538847796 https://github.com/dogsheep/pocket-to-sqlite/issues/2#issuecomment-538847796 https://api.github.com/repos/dogsheep/pocket-to-sqlite/issues/2 MDEyOklzc3VlQ29tbWVudDUzODg0Nzc5Ng== simonw 9599 2019-10-07T05:43:30Z 2019-10-07T05:43:30Z MEMBER We can persist the `since` value in its own single-row table. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Track and use the 'since' value 503234169  
540879620 https://github.com/dogsheep/twitter-to-sqlite/issues/4#issuecomment-540879620 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/4 MDEyOklzc3VlQ29tbWVudDU0MDg3OTYyMA== simonw 9599 2019-10-11T02:59:16Z 2019-10-11T02:59:16Z MEMBER Also import ad preferences and all that other junk. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command for importing data from a Twitter Export file 488835586  
541112108 https://github.com/dogsheep/twitter-to-sqlite/issues/17#issuecomment-541112108 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/17 MDEyOklzc3VlQ29tbWVudDU0MTExMjEwOA== simonw 9599 2019-10-11T15:30:15Z 2019-10-11T15:30:15Z MEMBER It should delete the tables entirely. That way it will work even if the table schema has changed. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} import command should empty all archive-* tables first 505674949  
541112588 https://github.com/dogsheep/twitter-to-sqlite/issues/17#issuecomment-541112588 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/17 MDEyOklzc3VlQ29tbWVudDU0MTExMjU4OA== simonw 9599 2019-10-11T15:31:30Z 2019-10-11T15:31:30Z MEMBER No need for an option: > This command will delete and recreate all of your `archive-*` tables every time you run it. If this is not what you want, run the command against a fresh SQLite database rather than running it again one that already exists. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} import command should empty all archive-* tables first 505674949  
541118773 https://github.com/dogsheep/twitter-to-sqlite/issues/18#issuecomment-541118773 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/18 MDEyOklzc3VlQ29tbWVudDU0MTExODc3Mw== simonw 9599 2019-10-11T15:48:31Z 2019-10-11T15:48:31Z MEMBER https://developer.twitter.com/en/docs/tweets/timelines/api-reference/get-statuses-home_timeline {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command to import home-timeline 505928530  
541118934 https://github.com/dogsheep/twitter-to-sqlite/issues/18#issuecomment-541118934 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/18 MDEyOklzc3VlQ29tbWVudDU0MTExODkzNA== simonw 9599 2019-10-11T15:48:54Z 2019-10-11T15:48:54Z MEMBER Rate limit is tight: 15 requests every 15 mins! {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command to import home-timeline 505928530  
541119834 https://github.com/dogsheep/twitter-to-sqlite/issues/18#issuecomment-541119834 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/18 MDEyOklzc3VlQ29tbWVudDU0MTExOTgzNA== simonw 9599 2019-10-11T15:51:22Z 2019-10-11T16:51:33Z MEMBER In order to support multiple user timelines being saved in the same database, I'm going to import the tweets into the `tweets` table AND add a new `timeline_tweets` table recording that a specific tweet showed up in a specific user's timeline. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command to import home-timeline 505928530  
541141169 https://github.com/dogsheep/twitter-to-sqlite/issues/18#issuecomment-541141169 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/18 MDEyOklzc3VlQ29tbWVudDU0MTE0MTE2OQ== simonw 9599 2019-10-11T16:51:29Z 2019-10-11T16:51:29Z MEMBER Documented here: https://github.com/dogsheep/twitter-to-sqlite/blob/master/README.md#retrieving-tweets-from-your-home-timeline {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command to import home-timeline 505928530  
541248629 https://github.com/dogsheep/twitter-to-sqlite/issues/19#issuecomment-541248629 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/19 MDEyOklzc3VlQ29tbWVudDU0MTI0ODYyOQ== simonw 9599 2019-10-11T22:48:56Z 2019-10-11T22:48:56Z MEMBER `since_id` documented here: https://developer.twitter.com/en/docs/tweets/timelines/api-reference/get-statuses-home_timeline > Returns results with an ID greater than (that is, more recent than) the specified ID. There are limits to the number of Tweets which can be accessed through the API. If the limit of Tweets has occured since the since_id, the since_id will be forced to the oldest ID available. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} since_id support for home-timeline 506087267  
541387822 https://github.com/dogsheep/github-to-sqlite/issues/6#issuecomment-541387822 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/6 MDEyOklzc3VlQ29tbWVudDU0MTM4NzgyMg== simonw 9599 2019-10-13T05:27:39Z 2019-10-13T05:27:39Z MEMBER This should be fixed by https://github.com/dogsheep/github-to-sqlite/commit/552543a74970f8a3a3f87f887be23a0c6eb1cb5b {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} sqlite3.OperationalError: table users has no column named bio 504238461  
541387941 https://github.com/dogsheep/github-to-sqlite/issues/6#issuecomment-541387941 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/6 MDEyOklzc3VlQ29tbWVudDU0MTM4Nzk0MQ== simonw 9599 2019-10-13T05:30:19Z 2019-10-13T05:30:19Z MEMBER Fix released in 0.5: https://github.com/dogsheep/github-to-sqlite/releases/tag/0.5 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} sqlite3.OperationalError: table users has no column named bio 504238461  
541388038 https://github.com/dogsheep/twitter-to-sqlite/issues/20#issuecomment-541388038 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/20 MDEyOklzc3VlQ29tbWVudDU0MTM4ODAzOA== simonw 9599 2019-10-13T05:31:58Z 2019-10-13T05:31:58Z MEMBER For favourites a `--stop_after=200` option is probably good enough. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} --since support for various commands for refresh-by-cron 506268945  
541493242 https://github.com/dogsheep/twitter-to-sqlite/issues/3#issuecomment-541493242 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/3 MDEyOklzc3VlQ29tbWVudDU0MTQ5MzI0Mg== simonw 9599 2019-10-14T03:35:36Z 2019-10-14T03:35:36Z MEMBER https://developer.twitter.com/en/docs/tweets/search/api-reference/get-search-tweets {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command for running a search and saving tweets for that search 488833975  
541721437 https://github.com/dogsheep/github-to-sqlite/issues/7#issuecomment-541721437 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/7 MDEyOklzc3VlQ29tbWVudDU0MTcyMTQzNw== simonw 9599 2019-10-14T14:44:12Z 2019-10-14T14:44:12Z MEMBER Docs: https://github.com/dogsheep/github-to-sqlite/blob/0.5/README.md#retrieving-issue-comments-for-a-repository {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} issue-comments command for importing issue comments 506276893  
541748580 https://github.com/dogsheep/twitter-to-sqlite/issues/10#issuecomment-541748580 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/10 MDEyOklzc3VlQ29tbWVudDU0MTc0ODU4MA== simonw 9599 2019-10-14T15:30:44Z 2019-10-14T15:30:44Z MEMBER Had several recommendations for https://github.com/tqdm/tqdm which is what goodreads-to-sqlite uses. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Rethink progress bars for various commands 492297930  
542333836 https://github.com/dogsheep/twitter-to-sqlite/issues/21#issuecomment-542333836 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/21 MDEyOklzc3VlQ29tbWVudDU0MjMzMzgzNg== simonw 9599 2019-10-15T18:00:48Z 2019-10-15T18:00:48Z MEMBER I'll use `html.unescape()` for this: https://docs.python.org/3/library/html.html#html.unescape {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Fix &amp; escapes in tweet text 506432572  
542832952 https://github.com/dogsheep/twitter-to-sqlite/issues/19#issuecomment-542832952 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/19 MDEyOklzc3VlQ29tbWVudDU0MjgzMjk1Mg== simonw 9599 2019-10-16T18:30:11Z 2019-10-16T18:30:11Z MEMBER The `--since` option will derive the `since_id` from the max ID in the `timeline_tweets` table: $ twitter-to-sqlite home-timeline --since The `--since_id=xxx` option lets you specify that ID directly. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} since_id support for home-timeline 506087267  
542849963 https://github.com/dogsheep/twitter-to-sqlite/issues/19#issuecomment-542849963 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/19 MDEyOklzc3VlQ29tbWVudDU0Mjg0OTk2Mw== simonw 9599 2019-10-16T19:13:06Z 2019-10-16T19:13:06Z MEMBER Updated documentation: https://github.com/dogsheep/twitter-to-sqlite/blob/fced2a9b67d2cbdf9817f1eb75f7c28e413c963b/README.md#retrieving-tweets-from-your-home-timeline {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} since_id support for home-timeline 506087267  
542854749 https://github.com/dogsheep/twitter-to-sqlite/issues/20#issuecomment-542854749 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/20 MDEyOklzc3VlQ29tbWVudDU0Mjg1NDc0OQ== simonw 9599 2019-10-16T19:26:01Z 2019-10-16T19:26:01Z MEMBER I'm not going to do this for "accounts that have followed me" and "new accounts that I have followed" - instead I will recommend running the `friend_ids` and `followers_ids` commands on a daily basis since that data doesn't really change much by the hour. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} --since support for various commands for refresh-by-cron 506268945  
542855081 https://github.com/dogsheep/twitter-to-sqlite/issues/12#issuecomment-542855081 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/12 MDEyOklzc3VlQ29tbWVudDU0Mjg1NTA4MQ== simonw 9599 2019-10-16T19:26:56Z 2019-10-16T19:26:56Z MEMBER This may be the first case where I want to be able to repair existing databases rather than discarding their contents. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Extract "source" into a separate lookup table 503053800  
542855427 https://github.com/dogsheep/twitter-to-sqlite/issues/12#issuecomment-542855427 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/12 MDEyOklzc3VlQ29tbWVudDU0Mjg1NTQyNw== simonw 9599 2019-10-16T19:27:55Z 2019-10-16T19:27:55Z MEMBER I can do that by keeping `source` as a `TEXT` column but turning it into a non-enforced foreign key against a new `sources` table. Then I can run code that scans that column for any values beginning with a `<` and converts them. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Extract "source" into a separate lookup table 503053800  
542858025 https://github.com/dogsheep/twitter-to-sqlite/issues/12#issuecomment-542858025 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/12 MDEyOklzc3VlQ29tbWVudDU0Mjg1ODAyNQ== simonw 9599 2019-10-16T19:35:31Z 2019-10-16T19:36:09Z MEMBER Maybe this means I need an `upgrade` command to apply these kinds of migrations? Total feature creep! {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Extract "source" into a separate lookup table 503053800  
542875885 https://github.com/dogsheep/swarm-to-sqlite/issues/3#issuecomment-542875885 https://api.github.com/repos/dogsheep/swarm-to-sqlite/issues/3 MDEyOklzc3VlQ29tbWVudDU0Mjg3NTg4NQ== simonw 9599 2019-10-16T20:23:08Z 2019-10-16T20:23:08Z MEMBER https://developer.foursquare.com/docs/api/users/checkins documents `afterTimestamp`: > Retrieve the first results to follow these seconds since epoch. This should be useful for paging forward in time, or when polling for changes. To avoid missing results when polling, we recommend subtracting several seconds from the last poll time and then de-duplicating. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Option to fetch only checkins more recent than the current max checkin 487600595  
542876047 https://github.com/dogsheep/swarm-to-sqlite/issues/3#issuecomment-542876047 https://api.github.com/repos/dogsheep/swarm-to-sqlite/issues/3 MDEyOklzc3VlQ29tbWVudDU0Mjg3NjA0Nw== simonw 9599 2019-10-16T20:23:36Z 2019-10-16T20:23:36Z MEMBER I'm going to go with `--since=1d/2w/3h` for this. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Option to fetch only checkins more recent than the current max checkin 487600595  
542882604 https://github.com/dogsheep/swarm-to-sqlite/issues/3#issuecomment-542882604 https://api.github.com/repos/dogsheep/swarm-to-sqlite/issues/3 MDEyOklzc3VlQ29tbWVudDU0Mjg4MjYwNA== simonw 9599 2019-10-16T20:41:23Z 2019-10-16T20:41:23Z MEMBER Documented here: https://github.com/dogsheep/swarm-to-sqlite/blob/0.2/README.md#usage {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Option to fetch only checkins more recent than the current max checkin 487600595  
543217890 https://github.com/dogsheep/twitter-to-sqlite/issues/23#issuecomment-543217890 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/23 MDEyOklzc3VlQ29tbWVudDU0MzIxNzg5MA== simonw 9599 2019-10-17T15:03:10Z 2019-10-17T15:03:10Z MEMBER Thinking about this further: the concept of migrations may end up being in direct conflict with the `sqlite-utils` concept of creating tables on demand the first time they are used - and of creating table schemas automatically to fit the shape of the JSON that is being inserted into them. I'm going to forge ahead anyway and build this because I think it will be an interesting exploration, but it's very likely this turns out to be a bad idea in the long run! {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Extremely simple migration system 508190730  
543222239 https://github.com/dogsheep/twitter-to-sqlite/issues/23#issuecomment-543222239 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/23 MDEyOklzc3VlQ29tbWVudDU0MzIyMjIzOQ== simonw 9599 2019-10-17T15:12:33Z 2019-10-17T15:12:33Z MEMBER Migrations will run only if you open a database that previously existed (as opposed to opening a brand new empty database). This means that the first time you run a command against a fresh database, migrations will not run and the `migrations` table will not be created. The _second_ time you run any command against that database the migrations will execute and populate the `migrations` table. This also means that each migration needs to be able to sanity check the database to see if it should run or not. If it should NOT run, it will do nothing but still be marked as having executed by adding to the `migrations` table. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Extremely simple migration system 508190730  
543265058 https://github.com/dogsheep/twitter-to-sqlite/issues/25#issuecomment-543265058 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/25 MDEyOklzc3VlQ29tbWVudDU0MzI2NTA1OA== simonw 9599 2019-10-17T16:51:12Z 2019-10-17T16:51:12Z MEMBER This migration function only runs if there is a table called `tweets` and the migration has not run before. I think this can happen if the database has just been freshly created (by a command that fetches the user's user timeline for example) and is then run a SECOND time. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Ensure migrations don't accidentally create foreign key twice 508578780  
543266947 https://github.com/dogsheep/twitter-to-sqlite/issues/25#issuecomment-543266947 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/25 MDEyOklzc3VlQ29tbWVudDU0MzI2Njk0Nw== simonw 9599 2019-10-17T16:56:06Z 2019-10-17T16:56:06Z MEMBER I wrote a test that proves that this is a problem. Should be an easy fix though. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Ensure migrations don't accidentally create foreign key twice 508578780  
543269396 https://github.com/dogsheep/twitter-to-sqlite/issues/10#issuecomment-543269396 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/10 MDEyOklzc3VlQ29tbWVudDU0MzI2OTM5Ng== simonw 9599 2019-10-17T17:02:07Z 2019-10-17T17:02:07Z MEMBER A neat trick that Click does is detecting if an interactive terminal is attached and NOT showing a progress bar if there isn't one. Need to figure out how to do that with tqdm. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Rethink progress bars for various commands 492297930  
543270714 https://github.com/dogsheep/twitter-to-sqlite/issues/10#issuecomment-543270714 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/10 MDEyOklzc3VlQ29tbWVudDU0MzI3MDcxNA== simonw 9599 2019-10-17T17:05:16Z 2019-10-17T17:05:16Z MEMBER https://github.com/pallets/click/blob/716a5be90f56ce6cd506bb53d5739d09374b1636/click/_termui_impl.py#L93 is how Click does this: ``` self.is_hidden = not isatty(self.file) ``` Where `isatty` is a Click utility function: `from ._compat import isatty` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Rethink progress bars for various commands 492297930  
543271000 https://github.com/dogsheep/twitter-to-sqlite/issues/10#issuecomment-543271000 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/10 MDEyOklzc3VlQ29tbWVudDU0MzI3MTAwMA== simonw 9599 2019-10-17T17:05:59Z 2019-10-17T17:05:59Z MEMBER Looks like tqdm already does a TTY check here: https://github.com/tqdm/tqdm/blob/89b73bdc30c099c5b53725806e7edf3a121c9b3a/tqdm/std.py#L889-L890 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Rethink progress bars for various commands 492297930  
543273540 https://github.com/dogsheep/twitter-to-sqlite/issues/3#issuecomment-543273540 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/3 MDEyOklzc3VlQ29tbWVudDU0MzI3MzU0MA== simonw 9599 2019-10-17T17:12:51Z 2019-10-17T17:12:51Z MEMBER Just importing tweets here isn't enough - how are we supposed to know which tweets were imported by which search? So I think the right thing to do here is to also create a `search_runs` table, which records each individual run of this tool (with a timestamp and the search terms used). Then have a `search_runs_tweets` m2m table which shows which Tweets were found by that search. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command for running a search and saving tweets for that search 488833975  
543290744 https://github.com/dogsheep/twitter-to-sqlite/issues/3#issuecomment-543290744 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/3 MDEyOklzc3VlQ29tbWVudDU0MzI5MDc0NA== simonw 9599 2019-10-17T17:57:14Z 2019-10-17T17:57:14Z MEMBER I have a working command now. I'm going to ship it early because it could do with some other people trying it out. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command for running a search and saving tweets for that search 488833975  
544335363 https://github.com/dogsheep/twitter-to-sqlite/issues/20#issuecomment-544335363 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/20 MDEyOklzc3VlQ29tbWVudDU0NDMzNTM2Mw== simonw 9599 2019-10-21T03:32:04Z 2019-10-21T03:32:04Z MEMBER In case anyone is interested, here's an extract from the crontab I'm running these under at the moment: ``` 1,11,21,31,41,51 * * * * /home/ubuntu/datasette-venv/bin/twitter-to-sqlite user-timeline /home/ubuntu/twitter.db -a /home/ubuntu/auth.json --since 2,7,12,17,22,27,32,37,42,47,52,57 * * * * /home/ubuntu/datasette-venv/bin/twitter-to-sqlite home-timeline /home/ubuntu/timeline.db -a /home/ubuntu/auth.json --since 6,16,26,36,46,56 * * * * /home/ubuntu/datasette-venv/bin/twitter-to-sqlite favorites /home/ubuntu/twitter.db -a /home/ubuntu/auth.json --stop_after=50 ``` {"total_count": 1, "+1": 1, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} --since support for various commands for refresh-by-cron 506268945  
544646516 https://github.com/dogsheep/genome-to-sqlite/issues/1#issuecomment-544646516 https://api.github.com/repos/dogsheep/genome-to-sqlite/issues/1 MDEyOklzc3VlQ29tbWVudDU0NDY0NjUxNg== simonw 9599 2019-10-21T18:30:14Z 2019-10-21T18:30:14Z MEMBER Thanks to help from Dr. Laura Cantino at Science Hack Day San Francisco I've been able to pull together this query: ```sql select rsid, genotype, case genotype when 'AA' then 'brown eye color, 80% of the time' when 'AG' then 'brown eye color' when 'GG' then 'blue eye color, 99% of the time' end as interpretation from genome where rsid = 'rs12913832' ``` See also https://www.snpedia.com/index.php/Rs12913832 - in particular this table: <img width="321" alt="rs12913832_-_SNPedia" src="https://user-images.githubusercontent.com/9599/67232392-216ff300-f3f6-11e9-8e14-b5f50c0c0d16.png"> {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Figure out some interesting example SQL queries 496415321  
544648863 https://github.com/dogsheep/genome-to-sqlite/issues/1#issuecomment-544648863 https://api.github.com/repos/dogsheep/genome-to-sqlite/issues/1 MDEyOklzc3VlQ29tbWVudDU0NDY0ODg2Mw== simonw 9599 2019-10-21T18:36:03Z 2019-10-21T18:36:03Z MEMBER <img width="1418" alt="natalie__select_rsid__genotype__case_genotype_when__AA__then__brown_eye_color__80__of_the_time__when__AG__then__brown_eye_color__when__GG__then__blue_eye_color__99__of_the_time__end_as_interpretation_from_genome_where_rsid____rs12913832__an" src="https://user-images.githubusercontent.com/9599/67232810-f4701000-f3f6-11e9-90e2-8fe2cca1d98d.png"> {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Figure out some interesting example SQL queries 496415321  
547713287 https://github.com/dogsheep/twitter-to-sqlite/issues/26#issuecomment-547713287 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/26 MDEyOklzc3VlQ29tbWVudDU0NzcxMzI4Nw== simonw 9599 2019-10-30T02:36:13Z 2019-10-30T02:36:13Z MEMBER Shipped this in 0.13: https://github.com/dogsheep/twitter-to-sqlite/releases/tag/0.13 See also this Twitter thread: https://twitter.com/simonw/status/1189369677509623809 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command for importing mentions timeline 513074501  
549094195 https://github.com/dogsheep/github-to-sqlite/pull/8#issuecomment-549094195 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/8 MDEyOklzc3VlQ29tbWVudDU0OTA5NDE5NQ== simonw 9599 2019-11-03T00:43:16Z 2019-11-03T00:43:28Z MEMBER Also need to take #5 into account - if this command creates incomplete user records, how do we repair them? And make sure that if we run this command first any future commands that populate users don't break (probably just a case of using `alter=True` in a few places). {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} stargazers command, refs #4 516763727  
549094229 https://github.com/dogsheep/github-to-sqlite/issues/5#issuecomment-549094229 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/5 MDEyOklzc3VlQ29tbWVudDU0OTA5NDIyOQ== simonw 9599 2019-11-03T00:44:03Z 2019-11-03T00:44:03Z MEMBER Might not need an incomplete boolean - may be possible to handle this with `alter=True` and then by filtering for users with null values in certain columns. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Add "incomplete" boolean to users table for incomplete profiles 493671014  
549095217 https://github.com/dogsheep/twitter-to-sqlite/issues/27#issuecomment-549095217 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/27 MDEyOklzc3VlQ29tbWVudDU0OTA5NTIxNw== simonw 9599 2019-11-03T01:06:25Z 2019-11-03T01:06:25Z MEMBER Wow, that `retweets_of_me` endpoint is almost completely useless: ``` $ twitter-to-sqlite fetch https://api.twitter.com/1.1/statuses/retweets_of_me.json ``` It returns my own tweets that have been retweeted, but with no indication at all of who retweeted them. It looks like this needs to be combined with this API - https://developer.twitter.com/en/docs/tweets/post-and-engage/api-reference/get-statuses-retweets-id - to fetch the details of up to 100 recent users who actually DID retweet an individual status. But that has a one-every-12-seconds rate limit on it. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} retweets-of-me command 514459062  
549095317 https://github.com/dogsheep/twitter-to-sqlite/issues/27#issuecomment-549095317 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/27 MDEyOklzc3VlQ29tbWVudDU0OTA5NTMxNw== simonw 9599 2019-11-03T01:08:10Z 2019-11-03T01:08:10Z MEMBER Hmm... one thing that could be useful is that `retweets_of_me` can support a `--since` parameter - so if run frequently it should hopefully let us know which tweets we would need to run `statuses/retweets/:id.json` against. I'm not sure if the `--since` parameter would show me a tweet that was previously retweeted but has now been retweeted again. I'll have a bit of a test and see. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} retweets-of-me command 514459062  
549095463 https://github.com/dogsheep/twitter-to-sqlite/issues/27#issuecomment-549095463 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/27 MDEyOklzc3VlQ29tbWVudDU0OTA5NTQ2Mw== simonw 9599 2019-11-03T01:10:52Z 2019-11-03T01:10:52Z MEMBER I imagine it won't, since the data I would be recording and then passing to `since_id` would be the highest ID of my own tweets that have been retweeted at least once. So it won't be able to spot if I should check for fresh retweets of a given tweet. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} retweets-of-me command 514459062  
549095641 https://github.com/dogsheep/twitter-to-sqlite/issues/27#issuecomment-549095641 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/27 MDEyOklzc3VlQ29tbWVudDU0OTA5NTY0MQ== simonw 9599 2019-11-03T01:12:58Z 2019-11-03T01:12:58Z MEMBER It looks like Twitter really want you to subscribe to a premium API for this kind of thing and consume retweets via webhooks: https://developer.twitter.com/en/docs/accounts-and-users/subscribe-account-activity/api-reference I'm going to give up on this for the moment. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} retweets-of-me command 514459062  
549096321 https://github.com/dogsheep/twitter-to-sqlite/issues/3#issuecomment-549096321 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/3 MDEyOklzc3VlQ29tbWVudDU0OTA5NjMyMQ== simonw 9599 2019-11-03T01:27:55Z 2019-11-03T01:28:17Z MEMBER It would be neat if this could support `--since`, with that argument automatically finding the maximum tweet ID from a previous search that used the same exact arguments (using the `search_runs` table). {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command for running a search and saving tweets for that search 488833975  
549226399 https://github.com/dogsheep/twitter-to-sqlite/issues/3#issuecomment-549226399 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/3 MDEyOklzc3VlQ29tbWVudDU0OTIyNjM5OQ== simonw 9599 2019-11-04T05:11:57Z 2019-11-04T05:11:57Z MEMBER I'm going to add a `hash` column to `search_runs` to support that. It's going to be the sha1 hash of the key-ordered JSON of the search arguments used by that run. Then `--since` can look for an identical hash and use it to identify the highest last fetched tweet to use in `since_id`. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command for running a search and saving tweets for that search 488833975  
549228535 https://github.com/dogsheep/twitter-to-sqlite/issues/3#issuecomment-549228535 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/3 MDEyOklzc3VlQ29tbWVudDU0OTIyODUzNQ== simonw 9599 2019-11-04T05:31:55Z 2019-11-04T05:31:55Z MEMBER Documented here: https://github.com/dogsheep/twitter-to-sqlite/blob/801c0c2daf17d8abce9dcb5d8d610410e7e25dbe/README.md#running-searches {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command for running a search and saving tweets for that search 488833975  
549230337 https://github.com/dogsheep/github-to-sqlite/issues/10#issuecomment-549230337 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/10 MDEyOklzc3VlQ29tbWVudDU0OTIzMDMzNw== simonw 9599 2019-11-04T05:47:18Z 2019-11-04T05:47:18Z MEMBER This definition isn't quite right - it's not pulling the identity of the user who starred the repo (`users.login` ends up being the owner login instead). {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Add this repos_starred view 516967682  
549230583 https://github.com/dogsheep/github-to-sqlite/pull/8#issuecomment-549230583 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/8 MDEyOklzc3VlQ29tbWVudDU0OTIzMDU4Mw== simonw 9599 2019-11-04T05:49:26Z 2019-11-04T05:49:26Z MEMBER Adding the view from #10 would be useful here too. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} stargazers command, refs #4 516763727  
549233778 https://github.com/dogsheep/github-to-sqlite/pull/8#issuecomment-549233778 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/8 MDEyOklzc3VlQ29tbWVudDU0OTIzMzc3OA== simonw 9599 2019-11-04T06:14:40Z 2019-11-04T06:14:40Z MEMBER Spotted a tricky problem: running `github-to-sqlite starred stargazers.db` results in an incomplete `simonw` record. It creates a proper record for me thanks to this bit: https://github.com/dogsheep/github-to-sqlite/blob/ea07274667a08c67907e8bfbbccb6f0fb95ce817/github_to_sqlite/cli.py#L120-L126 But then... when it gets to the `datasette` repository which I have starred it over-writes my full user record with one that's missing most of the details, thanks to this bit: https://github.com/dogsheep/github-to-sqlite/blob/ea07274667a08c67907e8bfbbccb6f0fb95ce817/github_to_sqlite/utils.py#L117-L124 I need to find a way of NOT over-writing a good record with a thinner one. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} stargazers command, refs #4 516763727  
550388354 https://github.com/dogsheep/github-to-sqlite/issues/4#issuecomment-550388354 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/4 MDEyOklzc3VlQ29tbWVudDU1MDM4ODM1NA== simonw 9599 2019-11-06T16:26:55Z 2019-11-06T16:26:55Z MEMBER Here's a query I figured out using a window function that shows cumulative stargazers over time: ```sql select yyyymmdd, sum(n) over ( order by yyyymmdd rows unbounded preceding ) as cumulative_count from ( select substr(starred_at, 0, 11) as yyyymmdd, count(*) as n from stars group by yyyymmdd ) ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command to fetch stargazers for one or more repos 493670730  
550783316 https://github.com/dogsheep/healthkit-to-sqlite/issues/10#issuecomment-550783316 https://api.github.com/repos/dogsheep/healthkit-to-sqlite/issues/10 MDEyOklzc3VlQ29tbWVudDU1MDc4MzMxNg== simonw 9599 2019-11-07T05:16:56Z 2019-11-07T05:34:29Z MEMBER It looks like Apple changed the location of these in iOS 13 - they are now in separate `.gpx` files: ![2FF70E95-CDEE-4241-A5C5-EE95A862E519](https://user-images.githubusercontent.com/9599/68362042-be12e000-00da-11ea-8925-7397410332d8.png) {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Failed to import workout points 519038979  
550806302 https://github.com/dogsheep/healthkit-to-sqlite/issues/10#issuecomment-550806302 https://api.github.com/repos/dogsheep/healthkit-to-sqlite/issues/10 MDEyOklzc3VlQ29tbWVudDU1MDgwNjMwMg== simonw 9599 2019-11-07T05:33:31Z 2019-11-07T05:33:31Z MEMBER The XML now includes references to these new files: ![CBBA54FC-51FB-4BB3-927C-C2CA99237B04](https://user-images.githubusercontent.com/9599/68362716-121ec400-00dd-11ea-9846-387c7cd64c8b.jpeg) {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Failed to import workout points 519038979  
550824838 https://github.com/dogsheep/healthkit-to-sqlite/issues/10#issuecomment-550824838 https://api.github.com/repos/dogsheep/healthkit-to-sqlite/issues/10 MDEyOklzc3VlQ29tbWVudDU1MDgyNDgzOA== simonw 9599 2019-11-07T05:47:07Z 2019-11-07T05:47:07Z MEMBER Relevant code: https://github.com/dogsheep/healthkit-to-sqlite/blob/d16f45f06fbae6ec8a78cc9ca7b5b7db0413f139/healthkit_to_sqlite/utils.py#L58-L64 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Failed to import workout points 519038979  
550828084 https://github.com/dogsheep/healthkit-to-sqlite/issues/10#issuecomment-550828084 https://api.github.com/repos/dogsheep/healthkit-to-sqlite/issues/10 MDEyOklzc3VlQ29tbWVudDU1MDgyODA4NA== simonw 9599 2019-11-07T05:49:24Z 2019-11-07T05:49:24Z MEMBER So the fix there is going to be to detect the new `FileReference` element and load the corresponding points data from it. This will be a little tricky because that function will need access to the zip file. It probably won't work at all for the mode where the `export.xml` file is passed directly using the `--xml` option. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Failed to import workout points 519038979  
552129686 https://github.com/dogsheep/twitter-to-sqlite/issues/29#issuecomment-552129686 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/29 MDEyOklzc3VlQ29tbWVudDU1MjEyOTY4Ng== simonw 9599 2019-11-09T19:27:39Z 2019-11-09T19:27:39Z MEMBER I think this is fixed by the latest version of `sqlite-utils` - https://sqlite-utils.readthedocs.io/en/latest/changelog.html#v1-12-1 - I'll bump the dependency. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} `import` command fails on empty files 518725064  
552129921 https://github.com/dogsheep/twitter-to-sqlite/issues/29#issuecomment-552129921 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/29 MDEyOklzc3VlQ29tbWVudDU1MjEyOTkyMQ== simonw 9599 2019-11-09T19:30:42Z 2019-11-09T19:30:42Z MEMBER Confirmed, that seems to fix it: ``` (twitter-to-sqlite) ~/Dropbox/Development/twitter-to-sqlite $ twitter-to-sqlite import blah.db ~/Dropbox/dogsheep/twitter-2019-06-25-b31f246100821b551f2f9a23f21ac6fb565dab49dd23a35630cabbf2b94a1f03/account-suspension.js Traceback (most recent call last): File "/Users/simonw/.local/share/virtualenvs/twitter-to-sqlite-4ech4lJi/bin/twitter-to-sqlite", line 11, in <module> load_entry_point('twitter-to-sqlite', 'console_scripts', 'twitter-to-sqlite')() File "/Users/simonw/.local/share/virtualenvs/twitter-to-sqlite-4ech4lJi/lib/python3.7/site-packages/click/core.py", line 764, in __call__ return self.main(*args, **kwargs) File "/Users/simonw/.local/share/virtualenvs/twitter-to-sqlite-4ech4lJi/lib/python3.7/site-packages/click/core.py", line 717, in main rv = self.invoke(ctx) File "/Users/simonw/.local/share/virtualenvs/twitter-to-sqlite-4ech4lJi/lib/python3.7/site-packages/click/core.py", line 1137, in invoke return _process_result(sub_ctx.command.invoke(sub_ctx)) File "/Users/simonw/.local/share/virtualenvs/twitter-to-sqlite-4ech4lJi/lib/python3.7/site-packages/click/core.py", line 956, in invoke return ctx.invoke(self.callback, **ctx.params) File "/Users/simonw/.local/share/virtualenvs/twitter-to-sqlite-4ech4lJi/lib/python3.7/site-packages/click/core.py", line 555, in invoke return callback(*args, **kwargs) File "/Users/simonw/Dropbox/Development/twitter-to-sqlite/twitter_to_sqlite/cli.py", line 633, in import_ archive.import_from_file(db, path.name, open(path, "rb").read()) File "/Users/simonw/Dropbox/Development/twitter-to-sqlite/twitter_to_sqlite/archive.py", line 224, in import_from_file db[table_name].upsert_all(rows, hash_id="pk") File "/Users/simonw/.local/share/virtualenvs/twitter-to-sqlite-4ech4lJi/lib/python3.7/site-packages/sqlite_utils/db.py", line 1094, in upsert_all extracts=extracts, File "/Users/simonw/.local/share/virtualenvs/twitter-to-sqlite-4ech4lJi/lib/python3.7/site-packages/sqlite… {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} `import` command fails on empty files 518725064  
552131798 https://github.com/dogsheep/twitter-to-sqlite/issues/30#issuecomment-552131798 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/30 MDEyOklzc3VlQ29tbWVudDU1MjEzMTc5OA== simonw 9599 2019-11-09T19:54:45Z 2019-11-09T19:54:45Z MEMBER Good catch - not sure how that bug crept in. Removing line 116 looks like the right fix to me. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} `followers` fails because `transform_user` is called twice 518739697  
552133449 https://github.com/dogsheep/twitter-to-sqlite/issues/29#issuecomment-552133449 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/29 MDEyOklzc3VlQ29tbWVudDU1MjEzMzQ0OQ== simonw 9599 2019-11-09T20:15:15Z 2019-11-09T20:15:15Z MEMBER Released: https://github.com/dogsheep/twitter-to-sqlite/releases/tag/0.15 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} `import` command fails on empty files 518725064  
552133468 https://github.com/dogsheep/twitter-to-sqlite/issues/30#issuecomment-552133468 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/30 MDEyOklzc3VlQ29tbWVudDU1MjEzMzQ2OA== simonw 9599 2019-11-09T20:15:27Z 2019-11-09T20:15:27Z MEMBER Released: https://github.com/dogsheep/twitter-to-sqlite/releases/tag/0.15 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} `followers` fails because `transform_user` is called twice 518739697  
552133488 https://github.com/dogsheep/twitter-to-sqlite/issues/28#issuecomment-552133488 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/28 MDEyOklzc3VlQ29tbWVudDU1MjEzMzQ4OA== simonw 9599 2019-11-09T20:15:42Z 2019-11-09T20:15:42Z MEMBER Released: https://github.com/dogsheep/twitter-to-sqlite/releases/tag/0.15 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Add indexes to followers table 515658861  
552135263 https://github.com/dogsheep/twitter-to-sqlite/issues/31#issuecomment-552135263 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/31 MDEyOklzc3VlQ29tbWVudDU1MjEzNTI2Mw== simonw 9599 2019-11-09T20:38:35Z 2019-11-09T20:38:35Z MEMBER Command still needs documentation and a bit more testing. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} "friends" command (similar to "followers") 520508502  
559883311 https://github.com/dogsheep/github-to-sqlite/issues/14#issuecomment-559883311 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/14 MDEyOklzc3VlQ29tbWVudDU1OTg4MzMxMQ== simonw 9599 2019-11-29T21:30:37Z 2019-11-29T21:30:37Z MEMBER I should build the command to persist ETags and obey their polling guidelines: > Events are optimized for polling with the "ETag" header. If no new events have been triggered, you will see a "304 Not Modified" response, and your current rate limit will be untouched. There is also an "X-Poll-Interval" header that specifies how often (in seconds) you are allowed to poll. In times of high server load, the time may increase. Please obey the header. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command for importing events 530491074  
559902818 https://github.com/dogsheep/github-to-sqlite/issues/14#issuecomment-559902818 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/14 MDEyOklzc3VlQ29tbWVudDU1OTkwMjgxOA== simonw 9599 2019-11-30T01:32:38Z 2019-11-30T01:32:38Z MEMBER Prototype: ``` pip install sqlite-utils paginate-json paginate-json "https://api.github.com/users/simonw/events" | sqlite-utils insert /tmp/events.db events - --pk=id ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command for importing events 530491074  
594151327 https://github.com/dogsheep/github-to-sqlite/issues/12#issuecomment-594151327 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/12 MDEyOklzc3VlQ29tbWVudDU5NDE1MTMyNw== simonw 9599 2020-03-03T20:26:15Z 2020-03-03T20:32:23Z MEMBER Better version (since this also includes JSON array of repository topics): ```sql CREATE VIEW recent_releases AS select repos.rowid as rowid, json_object("label", repos.full_name, "href", repos.html_url) as repo, json_object( "href", releases.html_url, "label", releases.name ) as release, substr(releases.published_at, 0, 11) as date, releases.body as body_markdown, releases.published_at, coalesce(repos.topics, '[]') as topics from releases join repos on repos.id = releases.repo order by releases.published_at desc ``` That `repos.rowid as rowid` bit is necessary because otherwise clicking on a link in facet-by-topic doesn't return any results. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Add this view for seeing new releases 520756546  
594154644 https://github.com/dogsheep/github-to-sqlite/pull/8#issuecomment-594154644 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/8 MDEyOklzc3VlQ29tbWVudDU5NDE1NDY0NA== simonw 9599 2020-03-03T20:33:57Z 2020-03-03T20:33:57Z MEMBER `sqlite-utils` supports proper upserts now so this problem should be easy to fix. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} stargazers command, refs #4 516763727  
594155249 https://github.com/dogsheep/github-to-sqlite/issues/12#issuecomment-594155249 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/12 MDEyOklzc3VlQ29tbWVudDU5NDE1NTI0OQ== simonw 9599 2020-03-03T20:35:17Z 2020-03-03T20:35:17Z MEMBER `swarm-to-sqlite` has an example of adding views here: https://github.com/dogsheep/swarm-to-sqlite/blob/f2c89dd613fb8a7f14e5267ccc2145463b996190/swarm_to_sqlite/utils.py#L141 I think that approach can be approved by first checking if the view exists, then dropping it, then recreating it. Could even try to see if the view exists and matches what we were going to set it to and do nothing if that is the case. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Add this view for seeing new releases 520756546  
597354514 https://github.com/dogsheep/github-to-sqlite/issues/17#issuecomment-597354514 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/17 MDEyOklzc3VlQ29tbWVudDU5NzM1NDUxNA== simonw 9599 2020-03-10T22:37:45Z 2020-03-10T22:37:45Z MEMBER I should add an option to stop the moment you see a commit you have fetched before. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command for importing commits 578883725  
597358364 https://github.com/dogsheep/github-to-sqlite/issues/17#issuecomment-597358364 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/17 MDEyOklzc3VlQ29tbWVudDU5NzM1ODM2NA== simonw 9599 2020-03-10T22:50:20Z 2020-03-11T01:18:36Z MEMBER By default it will stop when it sees a commit that has already been stored. You will be able to over-ride that behaviour using `--all`. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Command for importing commits 578883725  
601861908 https://github.com/dogsheep/twitter-to-sqlite/issues/34#issuecomment-601861908 https://api.github.com/repos/dogsheep/twitter-to-sqlite/issues/34 MDEyOklzc3VlQ29tbWVudDYwMTg2MTkwOA== simonw 9599 2020-03-20T18:56:44Z 2020-03-20T18:56:44Z MEMBER Could this be a bug in `sqlite-utils`? This table has a primary key, so why is it running a query on `rowid = ?`? {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} IndexError running user-timeline command 585266763  

Next page

Advanced export

JSON shape: default, array, newline-delimited, object

CSV options: 

CREATE TABLE [issue_comments] (
   [html_url] TEXT,
   [issue_url] TEXT,
   [id] INTEGER PRIMARY KEY,
   [node_id] TEXT,
   [user] INTEGER REFERENCES [users]([id]),
   [created_at] TEXT,
   [updated_at] TEXT,
   [author_association] TEXT,
   [body] TEXT,
   [reactions] TEXT,
   [issue] INTEGER REFERENCES [issues]([id])
, [performed_via_github_app] TEXT);
CREATE INDEX [idx_issue_comments_issue]
                ON [issue_comments] ([issue]);
CREATE INDEX [idx_issue_comments_user]
                ON [issue_comments] ([user]);