records-fork 0.5.5

SQL for Humans

Records: SQL for Humans™

---

☤ The Basics

---

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')

Grab one row at a time:

>>> rows[0]

<Record {"username": "model-t", "active": true, "name": "Henry Ford", "user_email": "[e-mail]", "timezone": "2016-02-06 22:28:23.894202"}>

Or iterate over them:

for r in rows:

    print(r.name, r.user_email)

Values can be accessed many ways: row.user_email, row['user_email'], or row[3].

Fields with non-alphanumeric characters (like spaces) are also fully supported.

Or store a copy of your record collection for later reference:

>>> rows.all()

[<Record {"username": ...}>, <Record {"username": ...}>, <Record {"username": ...}>, ...]

If you’re only expecting one result:

>>> rows.first()

<Record {"username": ...}>

Other options include rows.as_dict() and rows.as_dict(ordered=True).

☤ Features

---

• 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=:everything', everything=42).

• Queries can be passed as strings or filenames, parameters supported.

• Transactions: t = Database.transaction(); t.commit().

• Bulk actions: Database.bulk_query() & Database.bulk_query_file().

Records is proudly powered by SQLAlchemy

and Tablib.

☤ Data Export Functionality

---

Records also features full Tablib integration, and allows you to export

your results to CSV, XLS, JSON, HTML Tables, YAML, or Pandas DataFrames 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|[e-mail]|2016-02-06 22:28:23.894202

...

Comma Separated Values (CSV)

>>> print(rows.export('csv'))

username,active,name,user_email,timezone

model-t,True,Henry Ford,[e-mail],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: [e-mail], username: model-t}

...

JavaScript Object Notation (JSON)

>>> print(rows.export('json'))

[{"username": "model-t", "active": true, "name": "Henry Ford", "user_email": "[e-mail]", "timezone": "2016-02-06 22:28:23.894202"}, ...]

Microsoft Excel (xls, xlsx)

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

    f.write(rows.export('xls'))

Pandas DataFrame

>>> rows.export('df')

    username  active       name        user_email                   timezone

0    model-t    True Henry Ford [e-mail] 2016-02-06 22:28:23.894202

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 pipenv:

$ pipenv install records[pandas]

✨🍰✨

☤ Command-Line Tool

---

As an added bonus, a records command-line tool is automatically

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.5.1 (09-01-2017)

---

• Depend on tablib[pandas].

• Support for Bulk quies: Database.bulk_query() & Database.bulk_query_file().

v0.5.0 (11-15-2016)

---

• Support for transactions: t = Database.transaction(); t.commit()

v0.4.3 (02-16-2016)

---

• The cake is a lie.

v0.4.2 (02-15-2016)

---

• Packaging fix.

v0.4.1 (02-15-2016)

---

• Bugfix for Python 3.

v0.4.0 (02-13-2016)

---

• Refactored to be fully powered by SQLAlchemy!

• Support for all major databases (thanks, SQLAlchemy!).

• Support for non-alphanumeric column names.

• New Record class, for representing/accessing result rows.

• ResultSet renamed RecordCollection.

• Removed Interactive Mode from the CLI.

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.