functions, use the special R object to construct queries: # select the users' id, username and the first letter of their username, lower-cased query = User.select(['id', 'username', R('LOWER(SUBSTR(username # now filter this list to include only users whose username begins with "a" a_users = query.where(R('first_letter=%s', 'a')) >>> for user in a_users: ... print user.first_letter, user.username This a_users = User.filter(R('LOWER(SUBSTR(username, 1, 1)) = %s', 'a')) We can write subqueries as part of a SelectQuery, for example counting the number of entries on a blog: entry_query = R('(SELECT COUNT(*)

functions, use the special R object to construct queries: # select the users’ id, username and the first letter of their username, lower-cased query = User.select([’id’, ’username’, R(’LOWER(SUBSTR(username # now filter this list to include only users whose username begins with "a" a_users = query.where(R(’first_letter=%s’, ’a’)) >>> for user in a_users: ... print user.first_letter, user.username This a_users = User.filter(R(’LOWER(SUBSTR(username, 1, 1)) = %s’, ’a’)) We can write subqueries as part of a SelectQuery, for example counting the number of entries on a blog: entry_query = R(’(SELECT COUNT(*)

functions, use the special R object to construct queries: # select the users' id, username and the first letter of their username, lower-cased query = User.select(['id', 'username', R('LOWER(SUBSTR(username # now filter this list to include only users whose username begins with "a" a_users = query.where(R('first_letter=%s', 'a')) >>> for user in a_users: ... print user.first_letter, user.username This a_users = User.filter(R('LOWER(SUBSTR(username, 1, 1)) = %s', 'a')) We can write subqueries as part of a SelectQuery, for example counting the number of entries on a blog: entry_query = R('(SELECT COUNT(*)

functions, use the special R object to construct queries: # select the users’ id, username and the first letter of their username, lower-cased query = User.select([’id’, ’username’, R(’LOWER(SUBSTR(username # now filter this list to include only users whose username begins with "a" a_users = query.where(R(’first_letter=%s’, ’a’)) >>> for user in a_users: ... print user.first_letter, user.username This a_users = User.filter(R(’LOWER(SUBSTR(username, 1, 1)) = %s’, ’a’)) We can write subqueries as part of a SelectQuery, for example counting the number of entries on a blog: entry_query = R(’(SELECT COUNT(*)

expressing the same User.select( User, fn.Count(Tweet.id).alias(’ct’) ).join(Tweet).group_by(User).order_by(R(’ct desc’)) # do an atomic update Counter.update(count=Counter.count + 1).where( Counter.url == request request.url) Let’s find all the users whose username starts with ‘a’ or ‘A’: # 1.0 User.select().where(R(’LOWER(SUBSTR(username, 1, 1)) = %s’, ’a’)) # 2.0 User.select().where(fn.Lower(fn.Substr(User.username are times when you may want to simply pass in some arbitrary sql. You can do this using the special R class. One use-case is when referencing an alias: # we’ll query the user table and annotate it with

interested in more? Check out flask-peewee [https://github.com/coleifer/flask-peewee] - a flask plugin that provides a django-like Admin interface, RESTful API, Authentication and more for your peewee (surname, firstname). SELECT m.firstname, m.surname, r.firstname, r.surname FROM members AS m LEFT OUTER JOIN members AS r ON (m.recommendedby = r.memid) ORDER BY m.surname, m.firstname MA = Member.alias() DISTINCT m.firstname || ' ' || m.surname AS member, (SELECT r.firstname || ' ' || r.surname FROM cd.members AS r WHERE m.recommendedby = r.memid) AS recommended FROM members AS m ORDER BY member;

interested in more? Check out flask-peewee [https://github.com/coleifer/flask-peewee] - a flask plugin that provides a django-like Admin interface, RESTful API, Authentication and more for your peewee connect(database, **kwargs) def get_tables(self): res = self.execute('SHOW TABLES;') return [r[0] for r in res.fetchall()] Other things the database handles that are not covered here include: last_insert_id() (surname, firstname). SELECT m.firstname, m.surname, r.firstname, r.surname FROM members AS m LEFT OUTER JOIN members AS r ON (m.recommendedby = r.memid) ORDER BY m.surname, m.firstname MA = Member.alias()

interested in more? Check out flask-peewee [https://github.com/coleifer/flask-peewee] - a flask plugin that provides a django-like Admin interface, RESTful API, Authentication and more for your peewee (surname, firstname). SELECT m.firstname, m.surname, r.firstname, r.surname FROM members AS m LEFT OUTER JOIN members AS r ON (m.recommendedby = r.memid) ORDER BY m.surname, m.firstname MA = Member.alias() DISTINCT m.firstname || ' ' || m.surname AS member, (SELECT r.firstname || ' ' || r.surname FROM cd.members AS r WHERE m.recommendedby = r.memid) AS recommended FROM members AS m ORDER BY member;

interested in more? Check out flask-peewee [https://github.com/coleifer/flask-peewee] - a flask plugin that provides a django-like Admin interface, RESTful API, Authentication and more for your peewee (surname, firstname). SELECT m.firstname, m.surname, r.firstname, r.surname FROM members AS m LEFT OUTER JOIN members AS r ON (m.recommendedby = r.memid) ORDER BY m.surname, m.firstname MA = Member.alias() DISTINCT m.firstname || ' ' || m.surname AS member, (SELECT r.firstname || ' ' || r.surname FROM cd.members AS r WHERE m.recommendedby = r.memid) AS recommended FROM members AS m ORDER BY member;

companion blog post. Note: Like these snippets and interested in more? Check out flask-peewee - a flask plugin that provides a django-like Admin interface, RESTful API, Authentication and more for your peewee foodb.connect(database, **kwargs) def get_tables(self): res = self.execute('SHOW TABLES;') return [r[0] for r in res.fetchall()] Other things the database handles that are not covered here include: • last_insert_id() (surname, firstname). SELECT m.firstname, m.surname, r.firstname, r.surname FROM members AS m LEFT OUTER JOIN members AS r ON (m.recommendedby = r.memid) ORDER BY m.surname, m.firstname MA = Member.alias()