Answer a question

I am trying to compare two datetimes and it's showing sql syntax error

from flask import Flask
from flask import request, jsonify
from datetime import datetime
import pymysql

app = Flask(__name__)

class Database:
    def __init__(self):
        host = "localhost"
        user = "test"
        password = "test123"
        db = "test"
        self.con = pymysql.connect(host=host, user=user, password=password, db=db, cursorclass=pymysql.cursors.DictCursor)
        self.cur = self.con.cursor()


@app.route('/getCameraTypeAndTime/<string:date1>/<string:date2>', methods=['GET'])
def getCameraTypeAndTime(date1,date2):
    start = datetime.strptime(date1, '%Y-%m-%d %H:%M:%S')
    end = datetime.strptime(date2, '%Y-%m-%d %H:%M:%S')
    db=Database()
    result1=db.cur.execute("Select camera, COUNT(*) as freq from face_log group by camera where timestamp>start and timestamp_end<end")
    return jsonify({'result': result1})


if __name__ == '__main__':
    app.run(debug=True)

The format in sql is datetime but it won't compare

Answers

The main problem is that you are trying to compare column values to START and END MySQL keywords not your start and end variables. You need to make a query with placeholders and pass your variables to it like this (according to PyMySQL documentation):

db.cur.execute("""
    SELECT camera, COUNT(*) AS freq FROM face_log
    WHERE timestamp > %s and timestamp_end < %s
    GROUP BY camera
""", start, end)

Also note that GROUP BY part of the SQL query must be after WHERE part.