CODEWITHSUNDEEP
mysqldatetimepython-3.xpeewee
Duy Bui
Duy Bui

Reputation: 1396

Compare datetime in peewee sql Python

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,

Upvotes: 1

Views: 1305

Answers (2)

m12lrpv
m12lrpv

Reputation: 1287

I came here because I had the same problem abd subsequently the same question.

The cause of my issue was that mariaDB was stripping the milliseconds when the original insert was done and python/peewee was passing in the milliseconds in the predicate on the later update. Very frustrating.

Upvotes: 0

coleifer
coleifer

Reputation: 26245

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)))

Upvotes: 3

Related Questions