Compare datetime in peewee sql Python

Question

Apparently, I could not compare the date in the peewee SQL.

START_DATE = datetime.datetime(2015, 7, 20, 0, 0, 0)    
customer_records = Customers.select().\
                    join(Current_Insurers, on=(Customers.current_insurer == Current_Insurers.id)).\
                    switch(Current_Insurers).\
                    join(Insurers, on=(Current_Insurers.insurer == Insurers.id)).\
                    where(Customers.pol_type == "PC" & \
                          Current_Insurers.effective_date ==  START_DATE )

Where Customers, Current_Insurers, Insurers are three class. The result is always 0 records. But if I removed the datetime condition from the sql and compare as follows

 customer_records = Customers.select().\
                        join(Current_Insurers, on=(Customers.current_insurer == Current_Insurers.id)).\
                        switch(Current_Insurers).\
                        join(Insurers, on=(Current_Insurers.insurer == Insurers.id)).\
                        where(Customers.pol_type == "PC" 
for r in customer_records:
    if(r.current_insurer.effective_date == START_DATE):
        print(r.policy_id)

Surprisingly we can compare now and print out customers.

What do I need to do to add the datetime condition in the peewee sql?

Many thanks,

Answer 1

Apparently, I could not compare the date in the peewee SQL.

That's completely incorrect. Do you honestly think that the library would be that broken??

The problem is Python operator precedence. You need to wrap the equality expressions with parentheses. So you where clause should look like this instead:

where((Customers.pol_type == "PC") & \
      (Current_Insurers.effective_date ==  START_DATE))

Additionally, it's typically only necessary to call switch() when you have multiple joins to a single model.

Put together, your query should be:

query = (Customers
         .select()
         .join(Current_Insurers, on=(Customer.current_insurer == Current_Insurers.id))
         .join(Insurers, on=(Current_Insurers.insurer == Insurer.id))
         .where(
             (Customers.pol_type == "PC") &
             (Current_Insurers.effective_date ==  START_DATE)))