MyTetra Share
Делитесь знаниями!
SQL Functions, Subqueries and “Raw expressions” / стандартные SQL запросы в peewee
Время создания: 12.05.2017 17:04
Раздел: Python - Модули - peewee
Запись: xintrea/mytetra_db_mcold/master/base/1494597862oqrirl641l/text.html на

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 =, fn.Lower(fn.Substr(User.username, 1, 1)).alias('first_letter'))

# Alternatively we could select only users whose username begins with 'a'
a_users =, 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 =, fn.Count('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.


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  # .raw() will return model instances.
Так же в этом разделе:
MyTetra Share v.0.58
Яндекс индекс цитирования