• A simplified API for working with relational data, along the lines of working with JSON.
• An easy way to export relational data as JSON or CSV.
• An easy way to import JSON or CSV data into a relational database.

A minimal data-loading script might look like this:

You can export or import data using and thaw():

# Export table content to the `users.json` file.
db.freeze(table.all(), format='json', filename='users.json')
# Import data from a CSV file into a new table. Columns will be automatically
# created for each field in the CSV file.
new_table = db['stats']
new_table.thaw(format='csv', filename='monthly_stats.csv')

objects are initialized by passing in a database URL of the format dialect://user:password@host/dbname. See the Database URL section for examples of connecting to various databases.

# Create an in-memory SQLite database.
db = DataSet('sqlite:///:memory:')

Storing data

To store data, we must first obtain a reference to a table. If the table does not exist, it will be created automatically:

# Get a table reference, creating the table if it does not exist.
table = db['users']

We can now insert() new rows into the table. If the columns do not exist, they will be created automatically:

table.insert(name='Huey', age=3, color='white')
table.insert(name='Mickey', age=5, gender='male')

To update existing entries in the table, pass in a dictionary containing the new values and filter conditions. The list of columns to use as filters is specified in the columns argument. If no filter columns are specified, then all rows will be updated.

# Update the gender for "Huey".
table.update(name='Huey', gender='male', columns=['name'])
# Update all records. If the column does not exist, it will be created.
table.update(favorite_orm='peewee')

To import data from an external source, such as a JSON or CSV file, you can use the method. By default, new columns will be created for any attributes encountered. If you wish to only populate columns that are already defined on a table, you can pass in strict=True.

Using transactions

DataSet supports nesting transactions using a simple context manager.

table = db['users']
with db.transaction() as txn:
    table.insert(name='Charlie')
    with db.transaction() as nested_txn:
        table.update(name='Charlie', favorite_orm='django', columns=['name'])
        # jk/lol
        nested_txn.rollback()

You can use the tables() method to list the tables in the current database:

>>> print db.tables
['sometable', 'user']

And for a given table, you can print the columns:

>>> table = db['user']
>>> print table.columns
['id', 'age', 'name', 'gender', 'favorite_orm']

We can also find out how many rows are in a table:

>>> print len(db['user'])
3

Reading data

To retrieve all rows, you can use the all() method:

# Retrieve all the users.
users = db['user'].all()
# We can iterate over all rows without calling `.all()`
for user in db['user']:
    print user['name']

Specific objects can be retrieved using and find_one().

To export data, use the method, passing in the query you wish to export:

peewee_users = db['user'].find(favorite_orm='peewee')
db.freeze(peewee_users, format='json', filename='peewee_users.json')

API

class DataSet(url)

The DataSet class provides a high-level API for working with relational databases.

• tables

• __getitem__(table_name)

  Provide a reference to the specified table. If the table does not exist, it will be created.

• query(sql[, params=None[, commit=True]])

  Parameters:	sql (str) – A SQL query. params (list) – Optional parameters for the query. commit (bool) – Whether the query should be committed upon execution.
  Returns:	A database cursor.

  Execute the provided query against the database.

• ()

  Create a context manager representing a new transaction (or savepoint).

• freeze(query[, format=’csv’[, filename=None[, file_obj=None[, \*kwargs*]]]])

• thaw(table[, format=’csv’[, filename=None[, file_obj=None[, strict=False[, \*kwargs*]]]]])

  Parameters:	table (str) – The name of the table to load data into. filename – Filename to read data from. file_obj – File-like object to read data from. strict (bool) – Whether to store values for columns that do not already exist on the table. kwargs – Arbitrary parameters for import-specific functionality.

• connect()

  Open a connection to the underlying database. If a connection is not opened explicitly, one will be opened the first time a query is executed.

• close()

  Close the connection to the underlying database.

class Table(dataset, name, model_class)

Provides a high-level API for working with rows in a given table.

• columns

  Return a list of columns in the given table.

• A dynamically-created Model class.

• create_index(columns[, unique=False])

  Create an index on the given columns:

  # Create a unique index on the `username` column.
  db['users'].create_index(['username'], unique=True)

• insert(\*data*)

  Insert the given data dictionary into the table, creating new columns as needed.

• update(columns=None, conjunction=None, \*data*)

  Update the table using the provided data. If one or more columns are specified in the columns parameter, then those columns’ values in the data dictionary will be used to determine which rows to update.

  # Update all rows.
  db['users'].update(favorite_orm='peewee')
  # Only update Huey's record, setting his age to 3.
  db['users'].update(name='Huey', age=3, columns=['name'])

• find(\*query*)

  Query the table for rows matching the specified equality conditions. If no query is specified, then all rows are returned.

  peewee_users = db['users'].find(favorite_orm='peewee')

• find_one(\*query*)

  Return a single row matching the specified equality conditions. If no matching row is found then None will be returned.

  huey = db['users'].find_one(name='Huey')

• all()

  Return all rows in the given table.

• delete(\*query*)

  Delete all rows matching the given equality conditions. If no query is provided, then all rows will be deleted.

• freeze([format=’csv’[, filename=None[, file_obj=None[, \*kwargs*]]]])

• thaw([format=’csv’[, filename=None[, file_obj=None[, strict=False[, \*kwargs*]]]]])

  Parameters:	format – Input format. By default, csv and json are supported. filename – Filename to read data from. file_obj – File-like object to read data from. strict (bool) – Whether to store values for columns that do not already exist on the table.