Время создания: 12.05.2017 17:04
Раздел: Python - Модули - peewee
Запись: xintrea/mytetra_db_mcold/master/base/1494597862oqrirl641l/text.html на raw.githubusercontent.com
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:
db = SqliteDatabase(':memory:')
name = CharField()
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.
Так же в этом разделе: