dataset Documentation

Technical Documentation: pudo/dataset

ℹ️ Provenance: Multi-Tier Ingestion · CodeWiki Reference

Overview (`README.md`) Copy Section

dataset: databases for lazy people

In short, dataset makes reading and writing data in databases as simple as reading and writing JSON files.

Read the docs

To install dataset, fetch it with pip:

$ pip install dataset

Note: as of version 1.0, dataset is split into two packages, with the
data export features now extracted into a stand-alone package, datafreeze.
See the relevant repository here.

Api (`docs/api.rst`) Copy Section

API documentation

Connecting Copy Chapter

.. autofunction:: dataset.connect

Database Copy Chapter

.. autoclass:: dataset.Database
:members: tables, views, has_table, get_table, create_table, load_table, query, begin, commit, rollback, close
:special-members:

Table Copy Chapter

.. autoclass:: dataset.Table
:members: exists, columns, find, find_one, all, count, distinct, insert, insert_ignore, insert_many, update, update_many, upsert, upsert_many, delete, create_column, create_column_by_example, drop_column, create_index, drop, has_column, has_index
:special-members: len, iter

Data Export Copy Chapter

Note: Data exporting has been extracted into a stand-alone package, datafreeze. See the relevant repository here_.

.. _here: https://github.com/pudo/datafreeze

Index (`docs/index.rst`) Copy Section

.. dataset documentation master file, created by
sphinx-quickstart on Mon Apr 1 18:41:21 2013.
You can adapt this file completely to your liking, but it should at least
contain the root toctree directive.

dataset: databases for lazy people

.. toctree::
:hidden:

Although managing data in relational databases has plenty of benefits, they're
rarely used in day-to-day work with small to medium scale datasets. But why is
that? Why do we see an awful lot of data stored in static files in CSV or JSON
format, even though they are hard to query and update incrementally?

The answer is that programmers are lazy, and thus they tend to prefer the
easiest solution they find. And in Python, a database isn't the simplest
solution for storing a bunch of structured data. This is what dataset is
going to change!

dataset provides a simple abstraction layer that removes most direct SQL
statements without the necessity for a full ORM model - essentially, databases
can be used like a JSON file or NoSQL store.

A simple data loading script using dataset might look like this:

::

import dataset

db = dataset.connect('sqlite:///:memory:')

table = db['sometable']
table.insert(dict(name='John Doe', age=37))
table.insert(dict(name='Jane Doe', age=34, gender='female'))

john = table.find_one(name='John Doe')

Here is similar code, without dataset <https://gist.github.com/gka/5296492>_.

Features Copy Chapter

• Automatic schema: If a table or column is written that does not
  exist in the database, it will be created automatically.
• Upserts: Records are either created or updated, depending on
  whether an existing version can be found.
• Query helpers for simple queries such as :py:meth:all <dataset.Table.all> rows in a table or
  all :py:meth:distinct <dataset.Table.distinct> values across a set of columns.
• Compatibility: Being built on top of SQLAlchemy <https://www.sqlalchemy.org/>_, dataset works with all major databases, such as SQLite, PostgreSQL and MySQL.

Contents Copy Chapter

.. toctree::
:maxdepth: 2

install
quickstart
api
queries

Contributors Copy Chapter

dataset is written and maintained by Friedrich Lindenberg <https://github.com/pudo>,
Gregor Aisch <https://github.com/gka> and Stefan Wehrmeyer <https://github.com/stefanw>.
Its code is largely based on the preceding libraries sqlaload <https://github.com/okfn/sqlaload>
and datafreeze. And of course, we're standing on the shoulders of giants <https://www.sqlalchemy.org/>_.

Our cute little naked mole rat <https://www.youtube.com/watch?feature=player_detailpage&v=A5DcOEzW1wA#t=14s>_ was drawn by Johannes Koch <http://chechuchape.com/>_.

Install (`docs/install.rst`) Copy Section

Installation Guide

The easiest way is to install dataset from the Python Package Index <https://pypi.python.org/pypi/dataset/>_ using pip or easy_install:

.. code-block:: bash

$ pip install dataset

To install it manually simply download the repository from Github:

.. code-block:: bash

$ git clone git://github.com/pudo/dataset.git
$ cd dataset/
$ pip install -e .

Depending on the type of database backend, you may also need to install a
database specific driver package. For MySQL, this is PyMySQL, for Postgres
its psycopg2. SQLite support is integrated into Python.

Queries (`docs/queries.rst`) Copy Section

.. _advanced_filters:

Advanced filters

dataset provides two methods for running queries: :py:meth:table.find() <dataset.Table.find>
and :py:meth:db.query() <dataset.Database.query>. The table find helper method provides
limited, but simple filtering options::

results = table.find(column={operator: value})
# e.g.:
results = table.find(name={'like': '%mole rat%'})

A special form is using keyword searches on specific columns::

results = table.find(value=5)
# equal to:
results = table.find(value={'=': 5})

# Lists, tuples and sets are turned into `IN` queries:
results = table.find(category=('foo', 'bar'))
# equal to:
results = table.find(value={'in': ('foo', 'bar')})

The following comparison operators are supported:

============== ============================================================
Operator Description
============== ============================================================
gt, > Greater than
lt, < Less than
gte, >= Greater or equal
lte, <= Less or equal
!=, <>, not Not equal to a single value
in Value is in the given sequence
notin Value is not in the given sequence
like, ilike Text search, ILIKE is case-insensitive. Use % as a wildcard
notlike Like text search, except check if pattern does not exist
between, .. Value is between two values in the given tuple
startswith String starts with
endswith String ends with
============== ============================================================

Querying for a specific value on a column that does not exist on the table
will return no results.

You can also pass SQLAlchemy core expressions_ directly into the
:py:meth:table.find() <dataset.Table.find> method as positional arguments.
Access the underlying SQLAlchemy table via table.table and its columns
via table.table.columns::

from sqlalchemy import or_

# Get a column object:
city = table.table.columns.city
# Use a SQLAlchemy clause:
results = table.find(city.ilike('amsterda%'))

# Combine with OR:
country = table.table.columns.country
results = table.find(or_(city == 'Amsterdam', country == 'Germany'))

# Combine SQLAlchemy clauses with keyword filters:
results = table.find(city.ilike('new%'), country='US')

These clauses also work with :py:meth:table.count() <dataset.Table.count>,
:py:meth:table.find_one() <dataset.Table.find_one>, and
:py:meth:table.delete() <dataset.Table.delete>.

Queries using raw SQL Copy Chapter

To run more complex queries with JOINs, or to perform GROUP BY-style
aggregation, you can also use :py:meth:db.query() <dataset.Database.query>
to run raw SQL queries instead. This also supports parameterisation to avoid
SQL injections::

statement = 'SELECT user, COUNT(*) c FROM photos GROUP BY user'
for row in db.query(statement):
    print(row['user'], row['c'])

# With parameter binding:
results = db.query('SELECT * FROM users WHERE age > :min_age', min_age=21)

For fully programmatic, composable query building, consider using
SQLAlchemy core expressions_ directly.

.. _SQLAlchemy core expressions: https://docs.sqlalchemy.org/en/21/tutorial/data_select.html#tutorial-selecting-data

Quickstart (`docs/quickstart.rst`) Copy Section

Quickstart

Hi, welcome to the twelve-minute quick-start tutorial.

Connecting to a database Copy Chapter

At first you need to import the dataset package :) ::

import dataset

To connect to a database you need to identify it by its URL <https://docs.sqlalchemy.org/en/latest/core/engines.html#engine-creation-api>_, which basically is a string of the form "dialect://user:password@host/dbname". Here are a few examples for different database backends::

connecting to a SQLite database

db = dataset.connect('sqlite:///mydatabase.db')

connecting to a MySQL database with user and password

db = dataset.connect('mysql://user:password@localhost/mydatabase')

connecting to a PostgreSQL database

db = dataset.connect('postgresql://scott:tiger@localhost:5432/mydatabase')

It is also possible to define the URL as an environment variable called DATABASE_URL
so you can initialize database connection without explicitly passing an URL::

db = dataset.connect()

Depending on which database you're using, you may also have to install
the database bindings to support that database. SQLite is included in
the Python core, but PostgreSQL requires psycopg2 to be installed.
MySQL can be enabled by installing the mysql-db drivers.

Storing data Copy Chapter

To store some data you need to get a reference to a table. You don't need
to worry about whether the table already exists or not, since dataset
will create it automatically::

get a reference to the table 'user'

table = db['user']

Now storing data in a table is a matter of a single function call. Just
pass a dict_ to insert. Note that you don't need to create the columns
name and age – dataset will do this automatically::

Insert a new record.

table.insert(dict(name='John Doe', age=46, country='China'))

dataset will create "missing" columns any time you insert a dict with an unknown key

table.insert(dict(name='Jane Doe', age=37, country='France', gender='female'))

.. _dict: https://docs.python.org/3/library/stdtypes.html#dict

Updating existing entries is easy, too::

table.update(dict(name='John Doe', age=47), ['name'])

The list of filter columns given as the second argument filter using the
values in the first column. If you don't want to update over a
particular value, just use the auto-generated id column.

Using Transactions Copy Chapter

You can group a set of database updates in a transaction. In that case, all updates
are committed at once or, in case of exception, all of them are reverted. Transactions
are supported through a context manager, so they can be used through a with
statement::

with dataset.connect() as tx:
    tx['user'].insert(dict(name='John Doe', age=46, country='China'))

You can get same functionality by invoking the methods :py:meth:begin() <dataset.Table.begin>,
:py:meth:commit() <dataset.Table.commit> and :py:meth:rollback() <dataset.Table.rollback>
explicitly::

db = dataset.connect()
db.begin()
try:
    db['user'].insert(dict(name='John Doe', age=46, country='China'))
    db.commit()
except:
    db.rollback()

Nested transactions are supported too::

db = dataset.connect()
with db as tx1:
    tx1['user'].insert(dict(name='John Doe', age=46, country='China'))
    with db as tx2:
        tx2['user'].insert(dict(name='Jane Doe', age=37, country='France', gender='female'))

Closing connections Copy Chapter

When you're done with a database, call :py:meth:close() <dataset.Database.close>
to release all connections back to the pool and dispose of the engine::

db = dataset.connect('sqlite:///mydb.db')
# ... do work ...
db.close()

This is especially important in multi-threaded applications or when using
connection-pooled databases (PostgreSQL, MySQL), where open connections
can accumulate and exhaust the pool.

Inspecting databases and tables Copy Chapter

When dealing with unknown databases we might want to check their structure
first. To start exploring, let's find out what tables are stored in the
database:

print(db.tables)
[u'user']

Now, let's list all columns available in the table user:

print(db['user'].columns)
[u'id', u'country', u'age', u'name', u'gender']

Using len() we can get the total number of rows in a table:

print(len(db['user']))
2

Reading data from tables Copy Chapter

Now let's get some real data out of the table::

users = db['user'].all()

If we simply want to iterate over all rows in a table, we can omit :py:meth:all() <dataset.Table.all>::

for user in db['user']:
print(user['age'])

We can search for specific entries using :py:meth:find() <dataset.Table.find> and
:py:meth:find_one() <dataset.Table.find_one>::

All users from China

chinese_users = table.find(country='China')

Get a specific user

john = table.find_one(name='John Doe')

Find multiple at once

winners = table.find(id=[1, 3, 7])

Find by comparison operator

elderly_users = table.find(age={'>=': 70})
possible_customers = table.find(age={'between': [21, 80]})

Use the underlying SQLAlchemy directly

elderly_users = table.find(table.table.columns.age >= 70)

See :ref:advanced_filters for details on complex filters.

Using :py:meth:distinct() <dataset.Table.distinct> we can grab a set of rows
with unique values in one or more columns::

Get one user per country

db['user'].distinct('country')

Finally, you can use the row_type parameter to choose the data type in which
results will be returned::

import dataset
from stuf import stuf

db = dataset.connect('sqlite:///mydatabase.db', row_type=stuf)

Now contents will be returned in stuf objects (basically, dict
objects whose elements can be accessed as attributes (item.name) as well as
by index (item['name']).

Running custom SQL queries Copy Chapter

Of course the main reason you're using a database is that you want to
use the full power of SQL queries. Here's how you run them with dataset::

result = db.query('SELECT country, COUNT(*) c FROM user GROUP BY country')
for row in result:
print(row['country'], row['c'])

The :py:meth:query() <dataset.Table.query> method can also be used to
access the underlying SQLAlchemy core API <https://docs.sqlalchemy.org/en/21/orm/queryguide/query.html>_, which allows for the
programmatic construction of more complex queries::

table = db['user'].table
statement = table.select(table.c.name.like('%John%'))
result = db.query(statement)

Limitations of dataset Copy Chapter

The goal of dataset is to make basic database operations simpler, by expressing
some relatively basic operations in a Pythonic way. The downside of this approach
is that as your application grows more complex, you may begin to need access to
more advanced operations and be forced to switch to using SQLAlchemy proper,
without the dataset layer (instead, you may want to play with SQLAlchemy's ORM).

When that moment comes, take the hit. SQLAlchemy is an amazing piece of Python
code, and it will provide you with idiomatic access to all of SQL's functions.

Some of the specific aspects of SQL that are not exposed in dataset, and are
considered out of scope for the project, include:

• Foreign key relationships between tables, and expressing one-to-many and
  many-to-many relationships in idiomatic Python.
• Python-wrapped JOIN queries.
• Creating databases, or managing DBMS software.
• Support for Python 2.x

There's also some functionality that might be cool to support in the future, but
that requires significant engineering:

• Async operations
• Database-native UPSERT semantics

--- METRICS ---

• Files Extracted: 6
• Estimated Token Budget: ~4032 tokens
• Canonical Reference: https://codewiki.google/github.com/pudo/dataset