Peewee’s high-level Model and Field APIs are built upon
lower-level Table and Column counterparts. While these
lower-level APIs are not documented in as much detail as their high-level
counterparts, this document will present an overview with examples that should
hopefully allow you to experiment.

There are two ways we can declare Table objects for working with
these tables:

# Explicitly declare columnsPerson=Table('person',('id','first','last'))Note=Table('note',('id','person_id','content','timestamp'))# Do not declare columns, they will be accessed using magic ".c" attributeReminder=Table('reminder')

Typically we will want to bind() our tables to a database. This
saves us having to pass the database explicitly every time we wish to execute a
query on the table:

We store an expression in a variable (name), then use it in the query.

We call SQL functions using fn.<function>(...) passing arguments as if
it were a normal Python function.

The alias() method is used to specify the name used for
a column or calculation.

As a more complex example, we’ll generate a list of all people and the contents
and timestamp of their most recently-published note. To do this, we will end up
using the Note table twice in different contexts within the same query, which
will require us to use a table alias.

# Start with the query that calculates the timestamp of the most recent# note for each person.NA=Note.alias('na')max_note=(NA.select(NA.person_id,fn.MAX(NA.timestamp).alias('max_ts')).group_by(NA.person_id).alias('max_note'))# Now we'll select from the note table, joining on both the subquery and# on the person table to construct the result set.query=(Note.select(Note.content,Note.timestamp,Person.first,Person.last).join(max_note,on=((max_note.c.person_id==Note.person_id)&(max_note.c.max_ts==Note.timestamp))).join(Person,on=(Note.person_id==Person.id)).order_by(Person.first,Person.last))forrowinquery.namedtuples():print(row.first,row.last,':',row.timestamp,'-',row.content)

In the join predicate for the join on the max_note subquery, we can reference
columns in the subquery using the magical “.c” attribute. So,
max_note.c.max_ts is translated into “the max_ts column value from the
max_note subquery”.

We can also use the “.c” magic attribute to access columns on tables that do
not explicitly define their columns, like we did with the Reminder table.
Here’s a simple query to get all reminders for today, along with their
associated note content:

people=[{'first':'Bob','last':'Foo'},{'first':'Herb','last':'Bar'},{'first':'Nuggie','last':'Bar'}]# Inserting multiple rows returns the ID of the last-inserted row.last_id=Person.insert(people).execute()# We can also specify row tuples, so long as we tell Peewee which# columns the tuple values correspond to:people=[('Bob','Foo'),('Herb','Bar'),('Nuggie','Bar')]Person.insert(people,columns=[Person.first,Person.last]).execute()

update() queries accept either keyword arguments or a
dictionary mapping column to value, just like insert().

Examples:

# "Bob" changed his last name from "Foo" to "Baze".nrows=(Person.update(last='Baze').where((Person.first=='Bob')&(Person.last=='Foo')).execute())# Use dictionary mapping column to value.nrows=(Person.update({Person.last:'Baze'}).where((Person.first=='Bob')&(Person.last=='Foo')).execute())

You can also use expressions as the value to perform an atomic update. Imagine
we have a PageView table and we need to atomically increment the page-view
count for some URL:

# Do an atomic update:(PageView.update({PageView.count:PageView.count+1}).where(PageView.url==some_url).execute())

delete() queries are simplest of all, as they do not accept any
arguments:

# Delete all notes created before 2018, returning number deleted.n=Note.delete().where(Note.timestamp<datetime.date(2018,1,1)).execute()

Because DELETE (and UPDATE) queries do not support joins, we can use subqueries
to delete rows based on values in related tables. For example, here is how you
would delete all notes by anyone whose last name is “Foo”:

# Get the id of all people whose last name is "Foo".foo_people=Person.select(Person.id).where(Person.last=='Foo')# Delete all notes by any person whose ID is in the previous query.Note.delete().where(Note.person_id.in_(foo_people)).execute()