dogsheep/swarm-to-sqlite

Create a SQLite database containing your checkin history from Foursquare Swarm

swarm-to-sqlite

Create a SQLite database containing your checkin history from Foursquare Swarm.

How to install

$ pip install swarm-to-sqlite

Usage

You will need to first obtain a valid OAuth token for your Foursquare account. You can do so using this tool: https://your-foursquare-oauth-token.glitch.me/

Simplest usage is to simply provide the name of the database file you wish to write to. The tool will prompt you to paste in your token, and will then download your checkins and store them in the specified database file.

$ swarm-to-sqlite checkins.db
Please provide your Foursquare OAuth token:
Importing 3699 checkins  [#########-----------------------] 27% 00:02:31

You can also pass the token as a command-line option:

$ swarm-to-sqlite checkins.db --token=XXX

Or as an environment variable:

$ export FOURSQUARE_TOKEN=XXX
$ swarm-to-sqlite checkins.db

To retrieve just checkins within the past X hours, days or weeks, use the --since= option. For example, to pull only checkins that happened within the last 10 days use:

$ swarm-to-sqlite checkins.db --token=XXX --since=10d

Use 2w for two weeks, 10h for ten hours, 3d for three days.

In addition to saving the checkins to a database, you can also write them to a JSON file using the --save option:

$ swarm-to-sqlite checkins.db --save=checkins.json

Having done this, you can re-import checkins directly from that file (rather than making API calls to fetch data from Foursquare) like this:

$ swarm-to-sqlite checkins.db --load=checkins.json

Using with Datasette

The SQLite database produced by this tool is designed to be browsed using Datasette.

You can install the datasette-cluster-map plugin to view your checkins on a map.

swarm-to-sqlite's Issues

403 when getting token

I tried to use https://your-foursquare-oauth-token.glitch.me/ to get my Swarm auth token and got a 403 after I clicked the Allow button:

I'm not sure if this is the right repo to report this in

Error thrown: sqlite3.OperationalError: table users has no column named lastName

Just installed swarm-to-sqlite-0.3.2 and tried according to the docs:

Traceback (most recent call last):
  File "/usr/local/bin/swarm-to-sqlite", line 8, in <module>
    sys.exit(cli())
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/swarm_to_sqlite/cli.py", line 73, in cli
    save_checkin(checkin, db)
  File "/usr/local/lib/python3.9/site-packages/swarm_to_sqlite/utils.py", line 82, in save_checkin
    checkins_table.m2m("users", user, m2m_table="likes", pk="id")
  File "/usr/local/lib/python3.9/site-packages/sqlite_utils/db.py", line 1914, in m2m
    id = other_table.insert(record, pk=pk, replace=True).last_pk
  File "/usr/local/lib/python3.9/site-packages/sqlite_utils/db.py", line 1647, in insert
    return self.insert_all(
  File "/usr/local/lib/python3.9/site-packages/sqlite_utils/db.py", line 1765, in insert_all
    self.insert_chunk(
  File "/usr/local/lib/python3.9/site-packages/sqlite_utils/db.py", line 1575, in insert_chunk
    result = self.db.execute(query, params)
  File "/usr/local/lib/python3.9/site-packages/sqlite_utils/db.py", line 200, in execute
    return self.conn.execute(sql, parameters)
sqlite3.OperationalError: table users has no column named lastName

--save option to dump checkins to a JSON file on disk

This is a complement to the --load option - mainly useful for development purposes.

(I'll rename --file to --load as part of this issue).

Option to fetch only checkins more recent than the current max checkin

The Foursquare checkins API supports "return every checkin occurring after this point" - I can pass it the maximum createdAt date currently stored in the database. This will allow for quick incremental fetches via a cron.

Treat Foursquare timestamps as UTC

Current test failure is due to timezone differences between my laptop and Circle CI:

https://circleci.com/gh/dogsheep/swarm-to-sqlite/3

E         Full diff:
E         - [{'created': '2018-07-01T04:48:19',
E         ?                           ^
E         + [{'created': '2018-07-01T02:48:19',
E         ?                           ^
E         'createdAt': 1530413299,

The timestamps I store in created should always be UTC.

Implement code to pull checkins from the Foursquare API

The tool currently only works with a pre-prepared JSON file of checkins.

When called without options, it should prompt the user to paste in a Foursquare OAuth token.

The --token= option should work too, and should be backed up by an optional environment variable.

Define a view that displays photos correctly

The photos table stores data like this:

id	createdAt	source	prefix	suffix	width	height	visibility	created ▲	user
5e12c9708506bc000840262a	January 06, 2020 - 05:45:20 UTC	Swarm for iOS 1	https://fastly.4sqi.net/img/general/	/15889193_AXxGk4I1nbzUZuyYqObgbXdJNyEHiwj6AUDq0tPZWtw.jpg	1920	1440	public	2020-01-06T05:45:20	15889193

The photo URL can be derived from those pieces - define a SQL view which does that (using datasette-json-html to display the pictures)

Upgrade to sqlite-utils 2.x

Error thrown: table photos has no column named hasSticker

While running swarm-to-sqlite it throws an error:

harper@:~/dogsheep/swarm$ swarm-to-sqlite checkins.db --save=checkins.json
Please provide your Foursquare OAuth token:
Importing 8127 checkins  [#################-------------------]   49%  00:01:52
Traceback (most recent call last):
File "/home/harper/.local/bin/swarm-to-sqlite", line 11, in <module>
    sys.exit(cli())
File "/home/harper/.local/lib/python3.6/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
File "/home/harper/.local/lib/python3.6/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
File "/home/harper/.local/lib/python3.6/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
File "/home/harper/.local/lib/python3.6/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
File "/home/harper/.local/lib/python3.6/site-packages/swarm_to_sqlite/cli.py", line 73, in cli
    save_checkin(checkin, db)
File "/home/harper/.local/lib/python3.6/site-packages/swarm_to_sqlite/utils.py", line 94, in save_checkin
    photos_table.insert(photo, replace=True)
File "/home/harper/.local/lib/python3.6/site-packages/sqlite_utils/db.py", line 963, in insert
    alter = self.value_or_default("alter", alter)
File "/home/harper/.local/lib/python3.6/site-packages/sqlite_utils/db.py", line 1142, in insert_all
    def upsert_all(
sqlite3.OperationalError: table photos has no column named hasSticker

Where should i dig in?

ambiguous column name: createdAt - on checkin_details view

It looks like Swarm changed their schema and now both venues and checkins have createdAt fields.

Which breaks this view:

swarm-to-sqlite/swarm_to_sqlite/utils.py

Lines 171 to 188 in 719b6e9

	select
	checkins.id,
	created,
	venues.id as venue_id,
	venues.name as venue_name,
	venues.latitude,
	venues.longitude,
	group_concat(categories.name) as venue_categories,
	shout,
	createdBy,
	events.name as event_name
	from checkins
	join venues on checkins.venue = venues.id
	left join events on checkins.event = events.id
	join categories_venues on venues.id = categories_venues.venues_id
	join categories on categories.id = categories_venues.categories_id
	group by checkins.id
	order by createdAt desc

Online tool for getting a Foursquare OAuth token

I will link to this from the documentation. See also this conversation on Twitter: https://twitter.com/simonw/status/1166822603023011840

I've decided to go with "copy and paste in a token" rather than hooking up a local web server that can have tokens passed to it.

fails before generating views. ERR: table sqlite_master may not be modified

generates checkins.db but seems to fail before generating views

note: it worked on an Ubuntu WSL but fails on macOS 12.5.1

later edit: I suspect this is a problem with my local set-up, dogsheep-beta index also throws the same error

full error:

Importing 2591 checkins  [###################################-]   98%  00:00:00
Traceback (most recent call last):
  File "/Users/pax/devbox/envAll/bin/swarm-to-sqlite", line 8, in <module>
    sys.exit(cli())
  File "/Users/pax/devbox/envAll/lib/python3.8/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/Users/pax/devbox/envAll/lib/python3.8/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/Users/pax/devbox/envAll/lib/python3.8/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/pax/devbox/envAll/lib/python3.8/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/Users/pax/devbox/envAll/lib/python3.8/site-packages/swarm_to_sqlite/cli.py", line 77, in cli
    ensure_foreign_keys(db)
  File "/Users/pax/devbox/envAll/lib/python3.8/site-packages/swarm_to_sqlite/utils.py", line 145, in ensure_foreign_keys
    db[fk.table].add_foreign_key(fk.column, fk.other_table, fk.other_column)
  File "/Users/pax/devbox/envAll/lib/python3.8/site-packages/sqlite_utils/db.py", line 2123, in add_foreign_key
    self.db.add_foreign_keys([(self.name, column, other_table, other_column)])
  File "/Users/pax/devbox/envAll/lib/python3.8/site-packages/sqlite_utils/db.py", line 1086, in add_foreign_keys
    cursor.execute(
sqlite3.OperationalError: table sqlite_master may not be modified