Skip to main content

SQL for Humans

Project description

Records: SQL for Humans™

Records is a very simple, but powerful, library for making raw SQL queries to Postgres databases.

This common task can be surprisingly difficult with the standard tools available. This library strives to make this workflow as simple as possible, while providing an elegant interface to work with your query results.

We know how to write SQL, so let’s send some to our database:

import records

db = records.Database('postgres://...')
rows = db.query('select * from active_users')    # or db.query_file('sqls/active-users.sql')

☤ The Basics

Grab one row at a time:

>>> rows[0]
Record(username='model-t', name='Henry Ford', active=True, timezone=datetime.datetime(2016, 2, 6, 22, 28, 23, 894202), user_email='')

Or iterate over them:

for r in rows:
    spam_user(, email=r.user_email)

Or store them all for later reference:

>>> rows.all()
[Record(username=...), Record(username=...), Record(username=...), ...]

☤ Features

  • HSTORE support, if available.
  • Iterated rows are cached for future reference.
  • $DATABASE_URL environment variable support.
  • Convenience Database.get_table_names method.
  • Command-line records tool for exporting queries.
  • Safe parameterization: Database.query('life=%s', params=('42',))
  • Queries can be passed as strings or filenames, parameters supported.
  • Query results are iterators of standard Python dictionaries: {'column-name': 'value'}

Records is proudly powered by Psycopg2 and Tablib.

☤ Data Export Functionality

Records also features full Tablib integration, and allows you to export your results to CSV, XLS, JSON, HTML Tables, or YAML with a single line of code. Excellent for sharing data with friends, or generating reports.

>>> print rows.dataset
username|active|name      |user_email       |timezone
model-t |True  |Henry Ford||2016-02-06 22:28:23.894202
  • Comma Seperated Values (CSV)

    >>> print rows.export('csv')
    model-t,True,Henry Ford,,2016-02-06 22:28:23.894202
  • YAML Ain’t Markup Language (YAML)

    >>> print rows.export('yaml')
    - {active: true, name: Henry Ford, timezone: '2016-02-06 22:28:23.894202', user_email:, username: model-t}
  • JavaScript Object Notation (JSON)

    >>> print rows.export('json')
    [{"username": "model-t", "active": true, "name": "Henry Ford", "user_email": "", "timezone": "2016-02-06 22:28:23.894202"}, ...]
  • Microsoft Excel (xls, xlsx)

    with open('report.xls', 'wb') as f:

You get the point. All other features of Tablib are also available, so you can sort results, add/remove columns/rows, remove duplicates, transpose the table, add separators, slice data by column, and more.

See the Tablib Documentation for more details.

☤ Installation

Of course, the recommended installation method is pip:

$ pip install records

☤ Command-Line Tool

As an added bonus, a records command-line tool is automaticlaly included. Here’s a screenshot of the usage information:

☤ Thank You

Thanks for checking this library out! I hope you find it useful.

Of course, there’s always room for improvement. Feel free to open an issue so we can make Records better, stronger, faster.

v0.3.0 (02-11-2016)

  • New record command-line tool available!
  • Various improvements.

v0.2.0 (02-10-2016)

  • Results are now represented as Record, a namedtuples class with dict-like qualities.
  • New ResultSet.export method, for exporting to various formats.
  • Slicing a ResultSet now works, and results in a new ResultSet.
  • Lots of bugfixes and improvements!

v0.1.0 (02-07-2016)

  • Initial release.

Project details

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Files for records, version 0.3.0
Filename, size File type Python version Upload date Hashes
Filename, size records-0.3.0-py2.py3-none-any.whl (9.9 kB) File type Wheel Python version 2.7 Upload date Hashes View
Filename, size records-0.3.0.tar.gz (7.9 kB) File type Source Python version None Upload date Hashes View

Supported by

Pingdom Pingdom Monitoring Google Google Object Storage and Download Analytics Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN DigiCert DigiCert EV certificate StatusPage StatusPage Status page