Using Peewee Interactively

Peewee contains helpers for working interactively from a Python interpreter orsomething like a Jupyter notebook. For this example, we’ll assume that we havea 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 interpretersession, 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 anexisting 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 andgenerate model classes for all the tables that are found. This is a handy wayto get started and can save a lot of typing. The function returns a dictionarykeyed 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>)]
  5.  
  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 anddata-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 youfind that easier to read. This should match the actual table definition in theintrospected 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, wecan 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
  9.  
  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))
  12.  
  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 extensionsdocument.

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

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

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

The APIs discussed in this section:

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