Records: SQL for Humans™

[amotl]

• Slogan: Just write SQL. No bells, no whistles.
• Description: Records is a very simple but powerful library for making raw SQL queries to most relational databases.
• What's Inside: Records is powered by SQLAlchemy and Tablib, and written in Python.
• Repository: https://github.com/kennethreitz/records
• PyPI: https://pypi.org/project/records/
• See also: dataset: databases for lazy people #48

[amotl]

Using »Records« with »CrateDB«

Hi again. We added two folders to the CrateDB Examples Repository, each one including a few examples, that use the Records library and program together with CrateDB. It works well, apparently including support for all special data types of CrateDB.

• Demonstrate and verify the records library with CrateDB
• Demonstrate and verify the records program with CrateDB

Building upon all the powers of SQLAlchemy, both the API and CLI interfaces of Records are very convenient, providing perfect DWIM on your fingertips, and making them an excellent dynamic duo. Thanks a stack, @kennethreitz.

Install

# Install Records with support for CrateDB.
pipx install records sqlalchemy-cratedb

# Quickly spin up an instance of CrateDB.
docker run --rm --name=cratedb --publish=4200:4200 --publish=5432:5432 crate -Cdiscovery.type=single-node

Synopsis

Shell

export DATABASE_URL="crate://"

Text

$ records "SELECT * FROM sys.summits WHERE region ILIKE :region" region="ortler%"

classification|coordinates         |country|first_ascent|height|mountain             |prominence|range            |region
--------------|--------------------|-------|------------|------|---------------------|----------|-----------------|-----------
II/C-28.I-A   |[10.54472, 46.50889]|IT     |1804        |3905  |Ortler               |1953      |South Tyrol      |Ortler Alps
II/C-28.I-A   |[10.56833, 46.47861]|IT     |1854        |3851  |Königspitze          |424       |S-Tyrol/Sondrio  |Ortler Alps
II/C-28.I-A   |[10.61667, 46.44389]|IT     |1865        |3769  |Monte Cevedale       |531       |Sondrio/Trentino |Ortler Alps
II/C-28.I-A   |[10.56722, 46.37778]|IT     |1865        |3678  |Punta San Matteo     |369       |Sondrio/Trentino |Ortler Alps

YAML

# $ records "SELECT * FROM sys.summits WHERE region ILIKE :region" yaml region="ortler%"
- classification: II/C-28.I-A
  coordinates: [10.54472, 46.50889]
  country: IT
  first_ascent: 1804
  height: 3905
  mountain: Ortler
  prominence: 1953
  range: South Tyrol
  region: Ortler Alps

CSV

$ records "SELECT * FROM sys.summits WHERE region ILIKE :region" csv region="ortler%"

classification,coordinates,country,first_ascent,height,mountain,prominence,range,region
II/C-28.I-A,"[10.54472, 46.50889]",IT,1804,3905,Ortler,1953,South Tyrol,Ortler Alps
II/C-28.I-A,"[10.56833, 46.47861]",IT,1854,3851,Königspitze,424,S-Tyrol/Sondrio,Ortler Alps
II/C-28.I-A,"[10.61667, 46.44389]",IT,1865,3769,Monte Cevedale,531,Sondrio/Trentino,Ortler Alps

Python

import records, pprint

db = records.Database("crate://")
pprint.pprint(db.query("SELECT * FROM sys.summits ORDER BY height DESC LIMIT 3").all())

[<Record {"classification": "I/B-07.V-B", "coordinates": [6.86444, 45.8325], "country": "FR/IT", "first_ascent": 1786, "height": 4808, "mountain": "Mont Blanc", "prominence": 4695, "range": "U-Savoy/Aosta", "region": "Mont Blanc massif"}>,
 <Record {"classification": "I/B-09.III-A", "coordinates": [7.86694, 45.93694], "country": "CH", "first_ascent": 1855, "height": 4634, "mountain": "Monte Rosa", "prominence": 2165, "range": "Valais", "region": "Monte Rosa Alps"}>,
 <Record {"classification": "I/B-09.V-A", "coordinates": [7.85889, 46.09389], "country": "CH", "first_ascent": 1858, "height": 4545, "mountain": "Dom", "prominence": 1046, "range": "Valais", "region": "Mischabel"}>]

/cc @karynzv, @WalBeh, @seut, @matriv, @surister, @hlcianfagna, @hammerhead, @wierdvanderhaar, @simonprickett, @zolbatar