Dump basic https://glottolog.org languoid info into CSV file

languoids_csv.py

"""Dump basic https://glottolog.org languoid info to CSV file."""

import functools

import pandas as pd

ENGINE = 'postgresql://postgres@/glottolog3'

QUERY = '''
SELECT
  l.id AS glottocode,
  l.name,
  p.id AS parent_id,
  f.id AS family_id,
  ll.level,
  ll.category,
  l.latitude,
  l.longitude,
  (
    SELECT i.name
    FROM languageidentifier AS li
    JOIN identifier AS i
      ON i.pk = li.identifier_pk AND i.type = 'iso639-3'
    WHERE li.language_pk = l.pk
  ) AS iso_code,
  (
    SELECT STRING_AGG(SUBSTRING(link.url FROM '_([a-z]+)$'), ', ')
    FROM JSON_TABLE(l.jsondata, '$.links[*]' COLUMNS (
      url TEXT PATH '$.url ? (@ starts with "https://wals.info/languoid/lect/wals_code_")')) AS link
  ) AS wals_codes,
  ll.macroareas,
  (
    SELECT STRING_AGG(de.name, ', ' ORDER BY de.name)
    FROM parameter AS p
    JOIN valueset AS vs
      ON vs.parameter_pk = p.pk AND vs.language_pk = l.pk
    JOIN value AS v
      ON v.valueset_pk = vs.pk
    JOIN domainelement AS de
      ON de.pk = v.domainelement_pk
    WHERE p.id = 'country'
  ) AS countries,
  (
    SELECT de.name
    FROM parameter AS p
    JOIN valueset AS vs
      ON vs.parameter_pk = p.pk AND vs.language_pk = l.pk
    JOIN value AS v
      ON v.valueset_pk = vs.pk
    JOIN domainelement AS de
      ON de.pk = v.domainelement_pk
    WHERE p.id = 'aes'
  ) AS aes
FROM language AS l
JOIN languoid AS ll
  USING (pk)
LEFT JOIN language AS p
  ON ll.father_pk = p.pk
LEFT JOIN language AS f
  ON ll.family_pk = f.pk
WHERE l.active
ORDER BY l.id
'''.strip()


read_glottolog= functools.partial(pd.read_sql_query, con=ENGINE)

dataset = read_glottolog('SELECT id, name, created FROM dataset', index_col='id')
print(dataset.T, '', sep='\n')

df = read_glottolog(QUERY, index_col='glottocode')

df.info(memory_usage='deep')
assert df.index.is_unique
assert df.index.is_monotonic_increasing

df.to_csv('languoids.csv', encoding='utf-8')