SQL Functions, Subqueries and “Raw expressions”

Suppose you need to want to get a list of all users whose username begins with a. There are a couple ways to do this, but one method might be to use some SQL functions like LOWER and SUBSTR. To use arbitrary SQL functions, use the special fn() object to construct queries:

# Select the user's id, username and the first letter of their username, lower-cased

query = User.select(User, fn.Lower(fn.Substr(User.username, 1, 1)).alias('first_letter'))

# Alternatively we could select only users whose username begins with 'a'

a_users = User.select().where(fn.Lower(fn.Substr(User.username, 1, 1)) == 'a')

>>> for user in a_users:

...    print user.username

There are times when you may want to simply pass in some arbitrary sql. You can do this using the special SQL class. One use-case is when referencing an alias:

# We'll query the user table and annotate it with a count of tweets for

# the given user

query = User.select(User, fn.Count(Tweet.id).alias('ct')).join(Tweet).group_by(User)

# Now we will order by the count, which was aliased to "ct"

query = query.order_by(SQL('ct'))

There are two ways to execute hand-crafted SQL statements with peewee:

1. Database.execute_sql() for executing any type of query
2. RawQuery for executing SELECT queries and returning model instances.

Example:

db = SqliteDatabase(':memory:')

class Person(Model):

    name = CharField()

    class Meta:

        database = db

# let's pretend we want to do an "upsert", something that SQLite can

# do, but peewee cannot.

for name in ('charlie', 'mickey', 'huey'):

    db.execute_sql('REPLACE INTO person (name) VALUES (?)', (name,))

# now let's iterate over the people using our own query.

for person in Person.raw('select * from person'):

    print person.name  # .raw() will return model instances.