The avatar for @fgregg
Forest Gregg
Public
Edited
Jan 6, 2023
Fork of Hello, Datasette
•
1 fork
Importers
Insert cell
Insert cell
congress
select "World!" as Hello;
Insert cell
Insert cell
Insert cell
Insert cell
congress = new DatasetteClient(
"https://ds.agarcia.dev/congress/congress-members"
)
Insert cell
Insert cell
Insert cell
congress
SELECT bioguide,
first_name,
last_name,
CAST(strftime('%Y', DATE(birthday)) AS INTEGER) AS birth_year
FROM members
WHERE birth_year between 1981 AND 1996
ORDER BY birth_year DESC
Insert cell
Insert cell
Insert cell
viewof party = Inputs.select(["Democrat", "Republican", "Independent"], {
label: "Political party"
})
Insert cell
congress
SELECT
terms.type,
terms.state,
terms.party,
full_name,
terms.start as start,
terms.end
FROM members
LEFT JOIN terms ON terms.member = members.bioguide
GROUP BY bioguide
HAVING
date(max(terms.end)) > date("now")
and terms.type = "sen"
and terms.party = ${party}
ORDER BY state ASC
Insert cell
Insert cell
partyMembers
Insert cell
Insert cell
covid = new DatasetteClient("https://covid-19.datasettes.com/covid")
Insert cell
covid
select *
from latimes_county_totals
where date = "2021-09-01"
Insert cell
Insert cell
congress
WITH dates(date) AS (
SELECT DISTINCT start FROM terms WHERE start >= "1975-01-01"
)
, members_gen as (
SELECT
members.*,
members.birthday < '1928-01-01' as other,
members.birthday between '1928-01-01' and '1945-12-31' as silent,
members.birthday between '1946-01-01' and '1964-12-31' as boomer,
members.birthday between '1965-01-01' and '1980-12-31' as x,
members.birthday between '1981-01-01' and '1996-12-31' as millenial
FROM members
)

SELECT
dates.date,
sum(other) as num_other,
sum(silent) as num_silent,
sum(boomer) as num_boomer,
sum(x) as "num_Gen X",
sum(millenial) as num_millenial
from terms
inner join dates on terms.start <= dates.date and terms.end > dates.date
left join members_gen on members_gen.bioguide = terms.member
where terms.type = 'rep'
group by dates.date
Insert cell
repsGenerations
Insert cell
Insert cell
chartData = d3.merge(
repsGenerations.map((d) =>
["num_other", "num_silent", "num_boomer", "num_Gen X", "num_millenial"].map(
(k) => ({
date: new Date(d.date),
count: d[k],
generation: k
})
)
)
)
Insert cell
Insert cell
import { StackedAreaChart } from "@d3/stacked-area-chart"
Insert cell
import { Swatches } from "@d3/color-legend"
Insert cell
Insert cell
Insert cell
chart = StackedAreaChart(chartData, {
x: (d) => d.date,
y: (d) => d.count,
z: (d) => d.generation,
colors: ["#ccc", "#2b6455", "#53887b", "#ddc172", "#c79a27"],
yLabel: "↑ # House Representatives"
})
Insert cell
Insert cell
Insert cell
Insert cell
class DatasetteClient {
constructor(baseUrl) {
this.baseUrl = baseUrl;
}

_getFetchURL(sql, params, type = "json") {
const searchParams = new URLSearchParams();
searchParams.append("sql", sql);
if (params)
for (const key in params) {
searchParams.append(key, params[key]);
}
return `${this.baseUrl}.${type}?${searchParams.toString()}`;
}

_parseTemplate(strings, ...values) {
let sql = "";
const params = {};
let paramsI = 0;
for (const [i, s] of strings.entries()) {
if (i < values.length) {
// If an array is given inside ${}, then create a new parameter
// for each element and join the param names together in SQL
// ex "select 1 in ${[1,2,3]}" ->
// {sql: "select 1 in (:p0,p1,:p2)", params:{p0:1, p1:2, p2:3} }
if (Array.isArray(values[i])) {
const sqlArrayParts = [];
for (const v of values[i]) {
const param = `p${paramsI++}`;
sqlArrayParts.push(`:${param}`);
params[param] = v;
}
sql += `${s}(${sqlArrayParts.join(",")})`;
}
// Otherwise just replace element with string
else {
const param = `p${paramsI++}`;
sql += `${s}:${param}`;
params[param] = values[i];
}
} else {
sql += s;
}
}

return { sql, params };
}

_element(name, props, children) {
if (arguments.length === 2) (children = props), (props = undefined);
const element = document.createElement(name);
if (props !== undefined) for (const p in props) element[p] = props[p];
if (children !== undefined)
for (const c of children) element.appendChild(c);
return element;
}

_text(value) {
return document.createTextNode(value);
}

async query(query, params) {
const fetchUrl = this._getFetchURL(query, params, "csv");
return d3.csv(fetchUrl, d3.autoType);
}

async geoquery(query, params) {
const fetchUrl = this._getFetchURL(query, params, "geojson");
const result = await fetch(fetchUrl).then((r) => r.json());
if (typeof result.ok !== "undefined" && !result.ok)
throw Error(result.error);
return result;
}

async _row_count(query, params) {
const count_query = `select count(*) as num_rows from (${query.replace(
/(\s*;?\s*)*$/,
""
)})`;
const count_data = await this._page(count_query, params);
return count_data[0].num_rows;
}

async _page(query, params) {
const fetchUrl = this._getFetchURL(query, params);
const result = await fetch(fetchUrl).then((r) => r.json());
if (typeof result.ok !== "undefined" && !result.ok)
throw Error(result.error);

const data = result.rows.map((row) =>
row.reduce((res, field, index) => {
res[result.columns[index]] = field;
return res;
}, {})
);

data.truncated = result.truncated;

return data;
}

// have to redefine bc Datasette doesnt use non-named parameters ("?")
async describe(object) {
const rows = await (object === undefined
? this.query(`SELECT name FROM sqlite_master WHERE type = 'table'`)
: this.query(`SELECT * FROM pragma_table_info(:p0)`, { p0: object }));
if (!rows.length) throw new Error("Not found");
const { columns } = rows;
return this._element("table", { value: rows }, [
this._element("thead", [
this._element(
"tr",
columns.map((c) => this._element("th", [this._text(c)]))
)
]),
this._element(
"tbody",
rows.map((r) =>
this._element(
"tr",
columns.map((c) => this._element("td", [this._text(r[c])]))
)
)
)
]);
}

async sql(strings, ...values) {
const { sql, params } = this._parseTemplate(strings, ...values);
return this.query(sql, params);
}
}
Insert cell
congress
-- testing error handling
select * from membersx;
Insert cell

Purpose-built for displays of data

Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
Learn more