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

One platform to build and deploy the best data apps

Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.
Learn more