python - 使用不同的数据格式 DD-MMM-YY 运行 Mysql 查询

Question

1. 下面的查询工作正常

select * 
  from order_details 
 where date between '2021-02-01' and '2021-02-27';

2. 为了支持以下 API 请求

http://127.0.0.1:5000/BotMetrics/get/?fromdate=03-MAR-21&todate=16-MAR-21

注意：这里的日期值将以 DD-MMM-YY 的形式出现在外部

我想在查询下运行但不返回任何结果

select * 
  from order_details 
 where date between '03-MAR-21' and '16-MAR-21';

API 代码

@app.route('/BotMetrics/get/' ,methods=['get'])
def user():
    fromdate = request.args.get('fromdate',None)
    todate = request.args.get('todate',None)
    # environment =  request.args.get('environment',None)
    con = Get_hdb()
    cursor1 = con.cursor(pymysql.cursors.DictCursor)
    print(fromdate)
    print(todate)
    cursor1.execute("select * from order_details where date between %s and %s",(fromdate,todate))
    row = cursor1.fetchall()
    length = len(row)
    print(length)
    resp = jsonify({'"BOT-OC"':length})
    resp.status_code = 200
    return resp

日期字段目前以以下格式存储

标签： pythonmysqlpymysql

Answer 1

进行了以下代码更改以解决问题

 fromdate = request.args.get('fromdate',None)
 todate = request.args.get('todate',None)
 fromdate_new =  datetime.strptime(fromdate, '%d-%b-%y').strftime('%Y-%m-%d')
 todate_new =   datetime.strptime(todate, '%d-%b-%y').strftime('%Y-%m-%d')