1

I'm using Python 2.7 together with Peewee. At this moment, I need to use Peewee to execute the following SQL query:

select 
    a,
    b,
    substring(c, 1, 3) as alias1, 
    count(substring(c, 1, 3)) as alias2
from Table
where <some where condition, not relevant here>
group by a, alias1

My first problem here is how to perform a substring with Peewee. I have searched the documentation and, so far, no lucky.

So, the basic question is: How do I perform a substring SQL function using Peewee? It would also be very nice if someone can give me some directions of how to perform the entire query above with Peewee.

Rick
  • 521
  • 5
  • 18

1 Answers1

3

Ok.

After searching and searching, I finally found it. It can be done by simply using fn.substr.

A reference to the function can be found here. Strangely, a documentation of this same function is not present in the fn documentation page (only the method over is documented there).

To answer my own question, the SQL query is going to be something like (not tested):

TableModel.select(
   a,
   b,
   fn.substr(c, 1, 3).alias('alias1'),
   fn.count(fn.substr(c, 1, 3)).alias('alias2')
) \
.where(<some where condition, not relevant here>) \
.group_by(a, fn.substr(c, 1, 3))

Hope this can help someone in the future.

Rick
  • 521
  • 5
  • 18
  • 1
    The peewee fn object is magic. You can call any SQL function using it, it is syntactic sugar for generating a SQL function call. So you can do `fn.STRFTIME(..., ...)` and even nested `fn` calls. – coleifer Feb 21 '16 at 14:05
  • Thanks @coleifer :). Not sure now if I misunderstood the documentation, or if the documentation didn't make it explicitly enough. – Rick Feb 22 '16 at 14:51