Using Peewee Interactively

Peewee contains helpers for working interactively from a Python interpreter or something like a Jupyter notebook. For this example, we’ll assume that we have a pre-existing Sqlite database with the following simple schema:

  1. CREATE TABLE IF NOT EXISTS "event" (
  2.     "id" INTEGER NOT NULL PRIMARY KEY,
  3.     "key" TEXT NOT NULL,
  4.     "timestamp" DATETIME NOT NULL,
  5.     "metadata" TEXT NOT NULL);

To experiment with querying this database from an interactive interpreter session, we would start our interpreter and import the following helpers:

  • peewee.SqliteDatabase - to reference the “events.db”
  • playhouse.reflection.generate_models - to generate models from an existing database.
  • playhouse.reflection.print_model - to view the model definition.
  • playhouse.reflection.print_table_sql - to view the table SQL.

Our terminal session might look like this:

  1. >>> from peewee import SqliteDatabase
  2. >>> from playhouse.reflection import generate_models, print_model, print_table_sql
  3. >>>

The generate_models() function will introspect the database and generate model classes for all the tables that are found. This is a handy way to get started and can save a lot of typing. The function returns a dictionary keyed by the table name, with the generated model as the corresponding value:

  1. >>> db = SqliteDatabase('events.db')
  2. >>> models = generate_models(db)
  3. >>> list(models.items())
  4. [('events', <Model: event>)]
  6. >>> globals().update(models)  # Inject models into global namespace.
  7. >>> event
  8. <Model: event>

To take a look at the model definition, which lists the model’s fields and data-type, we can use the print_model() function:

  1. >>> print_model(event)
  2. event
  3.   id AUTO
  4.   key TEXT
  5.   timestamp DATETIME
  6.   metadata TEXT

We can also generate a SQL CREATE TABLE for the introspected model, if you find that easier to read. This should match the actual table definition in the introspected database:

  1. >>> print_table_sql(event)
  2. CREATE TABLE IF NOT EXISTS "event" (
  3.   "id" INTEGER NOT NULL PRIMARY KEY,
  4.   "key" TEXT NOT NULL,
  5.   "timestamp" DATETIME NOT NULL,
  6.   "metadata" TEXT NOT NULL)

Now that we are familiar with the structure of the table we’re working with, we can run some queries on the generated event model:

  1. >>> for e in event.select().order_by(event.timestamp).limit(5):
  2. ...     print(e.key, e.timestamp)
  3. ...
  4. e00 2019-01-01 00:01:00
  5. e01 2019-01-01 00:02:00
  6. e02 2019-01-01 00:03:00
  7. e03 2019-01-01 00:04:00
  8. e04 2019-01-01 00:05:00
  10. >>> event.select(fn.MIN(event.timestamp), fn.MAX(event.timestamp)).scalar(as_tuple=True)
  11. (datetime.datetime(2019, 1, 1, 0, 1), datetime.datetime(2019, 1, 1, 1, 0))
  13. >>> event.select().count()  # Or, len(event)
  14. 60

For more information about these APIs and other similar reflection utilities, see the Reflection section of the playhouse extensions document.

To generate an actual Python module containing model definitions for an existing database, you can use the command-line pwiz tool. Here is a quick example:

  1. $ pwiz -e sqlite events.db > events.py

The events.py file will now be an import-able module containing a database instance (referencing the events.db) along with model definitions for any tables found in the database. pwiz does some additional nice things like introspecting indexes and adding proper flags for NULL/NOT NULL constraints, etc.

The APIs discussed in this section:

More low-level APIs are also available on the Database instance: