Simon Willison simonw

Running twitter-to-sqlite using Datasette Desktop

Here's how to run twitter-to-sqlite on macOS without having to first installing Python, using the Datasette Desktop macOS application.

Install Datasette Desktop - it's a Mac application that you download
Launch the application once. This will set up a Python environment completely independent of anything else you may have on your machine, ideally avoiding the horrors of XKCD 1987.
Install twitter-to-sqlite by running this command: ~/.datasette-app/venv/bin/pip install twitter-to-sqlite
Now you can run twitter-to-sqlite by following that tool's documentation - but each time you run it you'll need to use this path: ~/.datasette-app/venv/bin/twitter-to-sqlite

The goal is to be able to take any Python string and reversibly convert it into a string that consists only of a-zA-Z9-0_ characters.

>>> alnum_encode("hello.csv")
'hello_2e_csv'
>>> alnum_encode("this é has ü accents")
'this_20__e9__20_has_20__fc__20_accents'

Then I pasted that JSON into https://www.jqkungfu.com/ and iterated on a jq program until I got to this one:

.timestamp as $timestamp | [.trace[] | {

	with snippets as (
	select
	substr(body, instr(body, 'refcode=') + 8, 128) as snippet
	from
	emails
	where
	body LIKE '%refcode%'
	),
	refcodes as (
	select

	<!DOCTYPE html>
	<html lang="en">
	<head>
	<meta charset="UTF-8" />
	<title>Single File Web Component</title>
	<style>
	body {
	background-color: #eee;
	font-family: Helvetica, sans-serif;
	}

	global_power_plant_database.country: (1/25)

	Total rows: 33643
	Null rows: 0
	Blank rows: 0

	Distinct values: 167

	Most common:
	8688: USA

	from django.urls import path
	from app.views import complete_google_login, start_google_login


	urlpatterns = [
	path("login/google/", start_google_login),
	path("login/google/complete/", complete_google_login),
	]

	function slugify(s) {
	return s
	.toLowerCase()
	.replace(/[^-\w\s]/g, "") // remove non-alphanumerics
	.trim()
	.replace(/[-\s]+/g, "-") // spaces to hyphens
	.replace(/-+$/g, ""); // trim trailing hyphens
	}

	function slugAutoFill() {