Retrieve all information from facilities table.

# will be selected.
query = Facility.select()

Retrieve specific columns from a table

Retrieve names of facilities and cost to members.

SELECT name, membercost FROM facilities;

query = Facility.select(Facility.name, Facility.membercost)
# To iterate:
for facility in query:
    print(facility.name)

Control which rows are retrieved

Retrieve list of facilities that have a cost to members.

SELECT * FROM facilities WHERE membercost > 0

query = Facility.select().where(Facility.membercost > 0)

Control which rows are retrieved - part 2

Retrieve list of facilities that have a cost to members, and that fee is less than 1/50th of the monthly maintenance cost. Return id, name, cost and monthly-maintenance.

SELECT facid, name, membercost, monthlymaintenance
FROM facilities

query = (Facility
         .select(Facility.facid, Facility.name, Facility.membercost,
                 Facility.monthlymaintenance)
         .where(
             (Facility.membercost > 0) &
             (Facility.membercost < (Facility.monthlymaintenance / 50))))

query = Facility.select().where(Facility.name.contains('tennis'))
# OR use the exponent operator. Note: you must include wildcards here:

Matching against multiple possible values

How can you retrieve the details of facilities with ID 1 and 5? Try to do it without using the OR operator.

SELECT * FROM facilities WHERE facid IN (1, 5);

query = Facility.select().where(Facility.facid.in_([1, 5]))
# OR:
query = Facility.select().where((Facility.facid == 1) |
                                (Facility.facid == 5))

Classify results into buckets

How can you produce a list of facilities, with each labelled as ‘cheap’ or ‘expensive’ depending on if their monthly maintenance cost is more than $100? Return the name and monthly maintenance of the facilities in question.

SELECT name,
CASE WHEN monthlymaintenance > 100 THEN 'expensive' ELSE 'cheap' END
FROM facilities;

cost = Case(None, [(Facility.monthlymaintenance > 100, 'expensive')], 'cheap')
query = Facility.select(Facility.name, cost.alias('cost'))

Note

See documentation Case for more examples.

Working with dates

SELECT memid, surname, firstname, joindate FROM members
WHERE joindate >= '2012-09-01';

query = (Member
         .select(Member.memid, Member.surname, Member.firstname, Member.joindate)

How can you produce an ordered list of the first 10 surnames in the members table? The list must not contain duplicates.

query = (Member
         .select(Member.surname)
         .order_by(Member.surname)
         .limit(10)
         .distinct())

Combining results from multiple queries

You, for some reason, want a combined list of all surnames and all facility names.

lhs = Member.select(Member.surname)
rhs = Facility.select(Facility.name)
query = lhs | rhs

Queries can be composed using the following operators:

• | - UNION
• + - UNION ALL
• & - INTERSECT
• - - EXCEPT

Simple aggregation

You’d like to get the signup date of your last member. How can you retrieve this information?

SELECT MAX(join_date) FROM members;

query = Member.select(fn.MAX(Member.joindate))
# To conveniently obtain a single scalar value, use "scalar()":
# max_join_date = query.scalar()

More aggregation

SELECT firstname, surname, joindate FROM members
WHERE joindate = (SELECT MAX(joindate) FROM members);

# Use "alias()" to reference the same table multiple times in a query.
MemberAlias = Member.alias()
subq = MemberAlias.select(fn.MAX(MemberAlias.joindate))
query = (Member
         .select(Member.firstname, Member.surname, Member.joindate)