The avatar for @ambassadors
Observable Ambassadors
The Ambassador program is made up of a group of inclusive, collaborative, and curious minds in the Observable community.
Public
Database Clients
Edited
Mar 15, 2023
5 forks
Importers
41 stars
Insert cell
Insert cell
covid
select "World!" as Hello;
Insert cell
Insert cell
Insert cell
Insert cell
congress = new DatasetteClient(
"https://congress-legislators.datasettes.com/legislators"
)
Insert cell
Insert cell
Insert cell
congress
SELECT id_bioguide,
name_first,
name_last,
CAST(strftime('%Y', DATE(bio_birthday)) AS INTEGER) AS birth_year
FROM legislators
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
legislator_terms.type,
legislator_terms.state,
legislator_terms.party,
legislators.name,
legislator_terms.start as start,
legislator_terms.end
FROM legislators
LEFT JOIN legislator_terms ON legislators.id = legislator_terms.legislator_id
GROUP BY id_bioguide
HAVING
date(max(legislator_terms.end)) > date("now")
and legislator_terms.type = "sen"
and legislator_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"
ORDER BY new_confirmed_cases DESC
Insert cell
Insert cell
congress
WITH dates(date) AS (
SELECT DISTINCT start FROM legislator_terms WHERE start >= "1975-01-01"
)
, members_gen as (
SELECT
legislators.*,
legislators.bio_birthday < '1928-01-01' as other,
legislators.bio_birthday between '1928-01-01' and '1945-12-31' as silent,
legislators.bio_birthday between '1946-01-01' and '1964-12-31' as boomer,
legislators.bio_birthday between '1965-01-01' and '1980-12-31' as x,
legislators.bio_birthday between '1981-01-01' and '1996-12-31' as millenial
FROM legislators
)
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 legislator_terms
inner join dates on legislator_terms.start <= dates.date and legislator_terms.end > dates.date
left join members_gen on members_gen.id = legislator_terms.legislator_id
where legislator_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, _shape = "array") {
const searchParams = new URLSearchParams();
searchParams.append("sql", sql);
if (params)
for (const key in params) {
searchParams.append(key, params[key]);
}
searchParams.append("_shape", _shape);
return `${this.baseUrl}.json?${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);
const data = await fetch(fetchUrl).then((r) => r.json());
if (typeof data.ok !== "undefined" && !data.ok) throw Error(data.error);
return Object.assign(data, {
columns: data.length ? Object.keys(data[0]) : []
});
}

// 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