SQLite for scientists
Year: 2024
Type: article
Abstract: Excel is not a database. As such, it lacks the data integrity constraints and flexible analysis methods that databases provide. Nevertheless, it is widely used across science to store and process tables of data. The aim of this presentation is to use SQLite, which is an open source database used in billions of applications, to demonstrate how easy it can be to use a real database instead.I will use data from the Global Volcanism Program to show how data are stored, queried, filtered and analysed using Structured Query Language (SQL) methods such as GROUP BY and JOIN. I will also show how "relations" can be used to build a sample database that separates concepts such as collection site and avoids repetition.The tooling around SQLite has improved greatly in the last decade. In this presentation, I use DbBrowser for SQLite as a general front-end, QGIS for custom forms and Datasette for sharing data across a network. Finally, I will introduce `etlhelper`. This is a Python library that we have developed at the British Geological Survey to simplify data transfer into and out of databases (including SQLite). This makes using SQLite in scripted workflows even easier. A recording of this session is available on YouTube: https://youtu.be/7JHhII6ZKz0
Author: Stevenson, John
Language: English
Subfield: Information Systems
Field: Computer Science
Domain: Physical Sciences
Open Access status: green
OpenAlex ID: W6949329178
FWCI:
Cites:
Cited by:
Related to: