| 8126 |
Datasette 0.59: The annotated release notes |
select * from content
where created_time = '07:00'
and author = :author |
https://simonwillison.net/e/8126 |
| 8126 |
Datasette 0.59: The annotated release notes |
select * from [cdcr-prison-totals]
where "zipcode" = :zip |
https://simonwillison.net/e/8126 |
| 8118 |
Weeknotes: Getting my personal Dogsheep up and running again |
twitter-to-sqlite statuses-lookup twitter.db \
--sql='select tweetId from archive_like' \
--skip-existing
Importing 33,382 tweets [------------------------------------] 0% 00:18:28
|
https://simonwillison.net/e/8118 |
| 8118 |
Weeknotes: Getting my personal Dogsheep up and running again |
sqlite-utils create-view twitter.db media_details "
select
json_object('img_src', media_url_https, 'width', 400) as img,
tweets.full_text,
tweets.created_at,
tweets.id as tweet_id,
users.screen_name,
'https://twitter.com/' || users.screen_name || '/status/' || tweets.id as tweet_url
from
media
join media_tweets on media.id = media_tweets.media_id
join tweets on media_tweets.tweets_id = tweets.id
join users on tweets.user = users.id
order by
tweets.id desc
"
|
https://simonwillison.net/e/8118 |
| 8117 |
Datasette on Codespaces, sqlite-utils API reference documentation and other weeknotes |
select
roadside_attractions.name,
roadside_attractions.address,
attraction_characteristic.name
from
roadside_attraction_characteristics
join roadside_attractions on roadside_attractions.pk = roadside_attraction_characteristics.attraction_id
join attraction_characteristic on attraction_characteristic.pk = roadside_attraction_characteristics.characteristic_id |
https://simonwillison.net/e/8117 |
| 8115 |
Django admin customization, JSON in our PostgreSQL |
select
jsonb_extract_path(
airtable_json, 'Reported by', 'name'
) as name,
count(*) as n
from call_report
group by name
order by n desc; |
https://simonwillison.net/e/8115 |
| 8111 |
A CSV export, JSON import workflow for bulk updating our data |
with extracted_idrefs as (
select
public_id, name,
(regexp_match(name, 'Walgreens.*#0*([0-9]{1,8})', 'i')) [1] as idref
from
location
where
regexp_match(name, 'Walgreens.*#0*([0-9]{1,8})', 'i') is not null
)
select
json_build_object(
'update',
json_object_agg(
public_id,
json_build_object('add', json_build_array('walgreens:' || idref), 'comment', name)
)
)
from
extracted_idrefs
where
public_id not in (
select
public_id
from
location
where
id in (
select
location_id
from
concordance_location
where
concordanceidentifier_id in (
select
id
from
concordance_identifier
where
authority = 'walgreens'
)
)
) |
https://simonwillison.net/e/8111 |
| 8111 |
A CSV export, JSON import workflow for bulk updating our data |
select
state.name,
count(*) as number_of_locations_with_no_county
from
location join state on state.id = location.state_id
where
location.county_id is null
group by
state.name |
https://simonwillison.net/e/8111 |
| 8111 |
A CSV export, JSON import workflow for bulk updating our data |
select
public_id, latitude, longitude
from
location
where
state_id = (
select id from state where name = 'Oregon'
)
and county_id is null |
https://simonwillison.net/e/8111 |
| 8111 |
A CSV export, JSON import workflow for bulk updating our data |
import csv
import httpx
import json
rows = list(csv.DictReader(open("select-public-id--latitude--lof05de6.csv")))
def county_for_point(latitude, longitude):
return httpx.get("https://us-counties.datasette.io/counties/county_for_latitude_longitude.json?_shape=array", params={
"latitude": latitude,
"longitude": longitude,
}).json()[0]['county_name']
counties = {}
# Progress bar:
from tqdm.notebook import tqdm
for row in tqdm(rows):
counties[row["public_id"]] = county_for_point(
row["latitude"], row["longitude"]
)
update = {
"update": {
key: {
"state": "OR",
"county": value
} for key, value in counties.items()
},
"revision_comment": "Added missing counties"
}
print(json.dumps(update)) |
https://simonwillison.net/e/8111 |