File size: 6,117 Bytes
97dd320 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 | """Importa o Darwin Core Archive da Flora e Funga do Brasil para PostgreSQL.
Uso local:
python backend/scripts/import_ffb_dwca.py --dwca data/ffb.zip
Uso com download:
python backend/scripts/import_ffb_dwca.py --url "https://ipt.jbrj.gov.br/jbrj/archive.do?r=lista_especies_flora_brasil"
Observação: confirme o nome dos arquivos dentro do DwC-A antes de rodar em produção.
Normalmente o core é Taxon e há extensões como Distribution/VernacularName.
"""
from __future__ import annotations
import argparse
import csv
import tempfile
import urllib.request
import zipfile
from pathlib import Path
from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session
from app.config import get_settings
TAXON_COLUMNS = {
'taxonID': 'taxon_id',
'parentNameUsageID': 'parent_name_usage_id',
'acceptedNameUsageID': 'accepted_name_usage_id',
'scientificName': 'scientific_name',
'canonicalName': 'canonical_name',
'scientificNameAuthorship': 'scientific_name_authorship',
'kingdom': 'kingdom',
'phylum': 'phylum',
'class': 'class_name',
'order': 'order_name',
'family': 'family',
'genus': 'genus',
'specificEpithet': 'specific_epithet',
'infraspecificEpithet': 'infraspecific_epithet',
'taxonRank': 'taxon_rank',
'taxonomicStatus': 'taxonomic_status',
'nomenclaturalStatus': 'nomenclatural_status',
'nameAccordingTo': 'name_according_to',
}
def download(url: str, output: Path) -> Path:
output.parent.mkdir(parents=True, exist_ok=True)
urllib.request.urlretrieve(url, output)
return output
def find_table(extract_dir: Path, candidates: list[str]) -> Path:
names = {item.lower() for item in candidates}
for path in extract_dir.rglob('*'):
if path.is_file() and path.name.lower() in names:
return path
raise FileNotFoundError(f'Nenhuma tabela encontrada entre: {candidates}')
def read_tsv(path: Path):
with path.open('r', encoding='utf-8-sig', newline='') as handle:
yield from csv.DictReader(handle, delimiter='\t')
def import_taxon(db: Session, path: Path, source_version: str | None = None) -> int:
db.execute(text('TRUNCATE ffb_distribution, ffb_taxon RESTART IDENTITY CASCADE'))
total = 0
for row in read_tsv(path):
mapped = {db_col: row.get(dwca_col) for dwca_col, db_col in TAXON_COLUMNS.items()}
mapped['source_version'] = source_version
if not mapped.get('taxon_id'):
continue
db.execute(text("""
INSERT INTO ffb_taxon (
taxon_id, parent_name_usage_id, accepted_name_usage_id, scientific_name, canonical_name,
scientific_name_authorship, kingdom, phylum, class_name, order_name, family, genus,
specific_epithet, infraspecific_epithet, taxon_rank, taxonomic_status,
nomenclatural_status, name_according_to, source_version
) VALUES (
:taxon_id, :parent_name_usage_id, :accepted_name_usage_id, :scientific_name, :canonical_name,
:scientific_name_authorship, :kingdom, :phylum, :class_name, :order_name, :family, :genus,
:specific_epithet, :infraspecific_epithet, :taxon_rank, :taxonomic_status,
:nomenclatural_status, :name_according_to, :source_version
) ON CONFLICT (taxon_id) DO UPDATE SET
scientific_name = EXCLUDED.scientific_name,
canonical_name = EXCLUDED.canonical_name,
family = EXCLUDED.family,
genus = EXCLUDED.genus,
specific_epithet = EXCLUDED.specific_epithet,
taxonomic_status = EXCLUDED.taxonomic_status,
accepted_name_usage_id = EXCLUDED.accepted_name_usage_id
"""), mapped)
total += 1
if total % 5000 == 0:
db.commit()
db.commit()
return total
def import_distribution(db: Session, path: Path) -> int:
total = 0
for row in read_tsv(path):
taxon_id = row.get('coreid') or row.get('taxonID') or row.get('id')
if not taxon_id:
continue
db.execute(text("""
INSERT INTO ffb_distribution (
taxon_id, location_id, locality, state_province, establishment_means,
occurrence_status, raw
) VALUES (
:taxon_id, :location_id, :locality, :state_province, :establishment_means,
:occurrence_status, '{}'::jsonb
)
"""), {
'taxon_id': taxon_id,
'location_id': row.get('locationID'),
'locality': row.get('locality'),
'state_province': row.get('stateProvince'),
'establishment_means': row.get('establishmentMeans'),
'occurrence_status': row.get('occurrenceStatus'),
})
total += 1
if total % 5000 == 0:
db.commit()
db.commit()
return total
def main() -> None:
parser = argparse.ArgumentParser()
parser.add_argument('--dwca', type=Path)
parser.add_argument('--url')
parser.add_argument('--source-version')
args = parser.parse_args()
if not args.dwca and not args.url:
raise SystemExit('Informe --dwca ou --url')
with tempfile.TemporaryDirectory() as tmp:
tmpdir = Path(tmp)
archive = args.dwca or download(args.url, tmpdir / 'ffb_dwca.zip')
with zipfile.ZipFile(archive) as zf:
zf.extractall(tmpdir / 'dwca')
extract_dir = tmpdir / 'dwca'
taxon_path = find_table(extract_dir, ['taxon.txt', 'Taxon.txt'])
dist_path = None
try:
dist_path = find_table(extract_dir, ['distribution.txt', 'Distribution.txt'])
except FileNotFoundError:
pass
engine = create_engine(get_settings().database_url)
with Session(engine) as db:
taxon_total = import_taxon(db, taxon_path, args.source_version)
dist_total = import_distribution(db, dist_path) if dist_path else 0
print(f'Importados {taxon_total} táxons e {dist_total} distribuições.')
if __name__ == '__main__':
main()
|