Flask SQLAlchemy has been a problem with querying databases for a number of partners recently

Flask – SQLAlchemy is used to query database records by date in ORM mode

My database mytable has a string type field pay_date, then we need to query today’s data record, normal we SQL statement to do

select * from mytable where DATE(create_date) == '2020-12-08'
Copy the code

Solution 1 fills in the datetime and then drops it for comparison

My actual requirement in this scenario is that I need to query the database records based on the start and end dates

# Pass in the start and end dates of string, Start_date = "2020-12-08 00:00:00" end_date = "2020-12-08 23:59:59" record_list = db.session.query(CcbDanbian).filter(CcbDanbian.pay_date.between(start_date , end_date ))).all()Copy the code

Scenario 2 uses the SQL CAST function

Cast (expression AS data_type) cast (expression AS data_type)

  • Parameter description:
  • Expression: Any valid SQServer expression.
  • AS: Used to separate two parameters, before AS is the data to be processed and after AS is the data type to be converted.
  • Data_type: Data type provided by the target system, including BIGint and SQL_Variant. User-defined data types cannot be used.
  • The CAST function is acceptable in the following cases: (1) The two expressions have exactly the same data type. (2) Two expressions can be implicitly converted. (3) The data type must be explicitly converted.
Start_date = "2020-12-08 00:00:00" end_date = "2020-12-08" start_date = datetime.strptime(start_date, "%Y-%m-%d") end_date += " 23:59:59" end_date = datetime.strptime(end_date, "%Y-%m-%d %H:%M:%S") records = db.session.query(CcbDanbian).filter(or_(and_(db.cast(CcbDanbian.pay_date, db.DATE) <= db.cast(end_date, db.DATE), db.cast(CcbDanbian.pay_date, db.DATE) >= db.cast(start_date, db.DATE)), and_(db.cast(CcbDanbian.refund_date, db.DATE) <= db.cast(end_date, db.DATE), db.cast(CcbDanbian.refund_date, db.DATE) >= db.cast(start_date, db.DATE)) )).all()Copy the code

Option 3 uses the SQL EXTRACT function

Ccb_danbian_exist_order = db.session.query(CcbDanbian).filter(extract('day'), CcbDanbian.pay_date) == 8, extract('year', CcbDanbian.pay_date) == 2020, extract('month', CcbDanbian.pay_date) == 12,).all()Copy the code