CODEWITHSUNDEEP
python-3.xpeewee
Saelyth
Saelyth

Reputation: 1734

How to make this SQL query in peewee

I am porting my code from sqlite3 to peewee and so far it works great, however I can't figure out how to port this query:

query = ("SELECT * FROM things 
WHERE Fecha substr(Fecha,7)||'/'||substr(Fecha,4,2)||'/'||substr(Fecha,1,2) 
BETWEEN ? AND ?", my_field, my_second_field)

On peewee I've got this far:

where_condition = database.fecha.fn.Substr(database.fecha, 7)
                 .concat('/')
                 .concat(database.fecha.fn.Substr(database.fecha, 4,2))
                 .concat('/')
                 .concat(database.fecha.fn.Substr(database.fecha, 1,2))
                 .between(my_field, my_second_field)
database.select().where(where_condition)

But it doesn't works and I'm clueless on how to concatenate substrings on peewee.

>>> 'TextField' has no attribute 'fn'

UPDATE: It's very important that where_condition is an external variable, as it's just 1 filter of many. I make different filters on a different method and then I pass them together to where as a tuple (I think the fn function doesn't works because of it). The full code of that is:

for record in database.select().where(*self.build_where_conditions())
    # Do something

def build_where_conditions(self):
    where_condition = []
    # first_thing
    if self.checkfirst_thing.isChecked():
       where_condition.append(database.first_thing == first_thing)
    # something
    if self.checksomething.isChecked():
        where_condition.append(database.something == something)
    # client
    if self.checkclient.isChecked():
        where_condition.append(database.client == client)
    # Serie
    if self.checkSerie.isChecked():
        where_condition.append(database.code.startswith("{0}{1}".format("18", serie)))
    # Code
    if self.checkCode.isChecked():
        where_condition.append(database.code.between(
            "{0}{1}".format("18", code1),
            "{0}{1}".format("18", code2)))
    # NOTE: Must always return a tuple
    if len(where_condition) >= 1:
        return where_condition
    return (None,)

Any suggestion on how to do this properly is appreciated.

Upvotes: 2

Views: 357

Answers (1)

coleifer
coleifer

Reputation: 26225

"fn" should be by itself. It's not an attribute (just like the error says...duh).

from peewee import fn

where_condition = fn.Substr(TheModel.the_text_field, 7)
                 .concat('/')
                 .concat(fn.Substr(TheModel.the_text_field, 4,2))
                 .concat('/') ... etc

Upvotes: 1

Related Questions