Search my code examples

Run searches against all of the code examples I have ever included on my blog.

Owned by simonw, visibility: Public

Query parameters

SQL query
with results_stripped as (
  select id, title,
    replace(replace(replace(replace(replace(regexp_replace(
      (regexp_matches(body, '<pre>(.*?)</pre>', 'g'))[1],
        E'<[^>]+>', '', 'gi'
      ), '&quot' || chr(59), '"'), '&gt' || chr(59), '>'), '&lt' || chr(59), '<'), '&#039' || chr(59), chr(39)), '&amp' || chr(59), '&'
    ) as code
  from
    blog_entry
  where
    body ~ '<pre>.*<pre>'
)
select id, title, code, 'https://simonwillison.net/e/' || id as link from results_stripped
where code like '%%' || %(search)s || '%%' order by id desc limit 10

10 rows

id title code link
8586 Visualizing local election results with Datasette, Observable and MapLibre GL select Precinct_name, precincts.geometry, total_ballots, json_group_object( candidate_name, total_votes ) as votes_by_candidate from election_results join precincts on election_results.Precinct_name = precincts.precinct_id where Contest_title = "Granada Community Services District Members, Board of Directors" group by Precinct_name, precincts.geometry, total_ballots; https://simonwillison.net/e/8586
8586 Visualizing local election results with Datasette, Observable and MapLibre GL sql = ` select Precinct_name, precincts.geometry, Split_name, Reporting_flag, Update_count, Pct_Id, Pct_seq_nbr, Reg_voters, Turn_Out, Contest_Id, Contest_seq_nbr, Contest_title, Contest_party_name, Selectable_Options, candidate_id, candidate_name, Candidate_Type, cand_seq_nbr, Party_Code, total_ballots, total_votes, total_under_votes, total_over_votes, [Vote Centers_ballots], [Vote Centers_votes], [Vote Centers_under_votes], [Vote Centers_over_votes], [Vote by Mail_ballots], [Vote by Mail_votes], [Vote by Mail_under_votes], [Vote by Mail_over_votes] from election_results join precincts on election_results.Precinct_name = precincts.precinct_id where "Contest_title" = "Granada Community Services District Members, Board of Directors" limit 101;` https://simonwillison.net/e/8586
8586 Visualizing local election results with Datasette, Observable and MapLibre GL // Select the contest viewof contest = Inputs.select(contests, { label: "Choose a contest" }) // --- // And the candidate viewof candidate = Inputs.radio( candidates, { label: "Choose a candidate", value: candidates[0] } ) // --- // Show the map itself Plot.plot({ width, height: 600, legend: true, color: { scheme: "blues", legend: true }, projection: { type: "mercator", domain: data2 }, marks: [ Plot.geo(data2, { strokeOpacity: 0.1, fill: "ratio", tip: true }) ] }) # --- data2 = ({ type: "FeatureCollection", features: raw_data2.map((d) => ({ type: "Feature", properties: { precinct: d.Precinct_name, total_ballots: d.total_ballots, ratio: JSON.parse(d.votes_by_candidate)[candidate] / d.total_ballots }, geometry: JSON.parse(d.geometry) })) }) // --- raw_data2 = query( `select Precinct_name, precincts.geometry, total_ballots, json_grop_object( candidate_name, total_votes ) as votes_by_candidate from election_results join precincts on election_results.Precinct_name = precincts.precinct_id where Contest_title = :contest group by Precinct_name, precincts.geometry, total_ballots;`, { contest } ) // --- raw_data2 = query( `select Precinct_name, precincts.geometry, total_ballots, json_group_object( candidate_name, total_votes ) as votes_by_candidate from election_results join precincts on election_results.Precinct_name = precincts.precinct_id where Contest_title = :contest group by Precinct_name, precincts.geometry, total_ballots;`, { contest } ) // --- // Fetch the available contests contests = query("select distinct Contest_title from election_results").then( (d) => d.map((d) => d.Contest_title) ) // --- // Extract available candidates for selected contest candidates = Object.keys( JSON.parse(raw_data2[0].votes_by_candidate) ) // --- function query(sql, params = {}) { return fetch( `https://datasette-public-office-hours.datasette.cloud/data/-/query.json?${new URLSearchParams( { sql, _shape: "array", ...params } ).toString()}`, { headers: { Authorization: `Bearer ${secret}` } } ).then((r) => r.json()); } https://simonwillison.net/e/8586
8480 Optimizing Datasette (and other weeknotes) select count(*) from ( select * from libfec_SA16 limit 10001 ) https://simonwillison.net/e/8480
8480 Optimizing Datasette (and other weeknotes) select date(column_to_test) from ( select * from mytable limit 100 ) where column_to_test glob "????-??-*" https://simonwillison.net/e/8480
8480 Optimizing Datasette (and other weeknotes) select date(column_to_test) from ( select * from mytable ) where column_to_test glob "????-??-*" limit 100; https://simonwillison.net/e/8480
8382 Building search-based RAG using Claude, Datasette and Val Town // The SQL query from earlier const sql = `select blog_entry.id, blog_entry.title, blog_entry.body, blog_entry.created from blog_entry join blog_entry_fts on blog_entry_fts.rowid = blog_entry.rowid where blog_entry_fts match :search order by rank limit 10`; async function runSearch(keywords) { // Turn the keywords into "word1" OR "word2" OR "word3" const search = keywords.map(s => `"${s}"`).join(" OR "); // Compose the JSON API URL to run the query const params = new URLSearchParams({ search, sql, _shape: "array", }); const url = "https://datasette.simonwillison.net/simonwillisonblog.json?" + params; const result = await (await fetch(url)).json(); return result; } https://simonwillison.net/e/8382
8382 Building search-based RAG using Claude, Datasette and Val Town select blog_entry.id, blog_entry.title, blog_entry.body, blog_entry.created from blog_entry join blog_entry_fts on blog_entry_fts.rowid = blog_entry.rowid where blog_entry_fts match :search order by rank limit 10 https://simonwillison.net/e/8382
8380 Language models on the command-line llm embed-multi links \ -d simonwillisonblog.db \ --sql 'select id, link_url, link_title, commentary from blog_blogmark' \ -m 3-small --store https://simonwillison.net/e/8380
8380 Language models on the command-line select id, link_url, link_title, commentary from blog_blogmark https://simonwillison.net/e/8380
Copy and export data

Duration: 169.75ms