Gabbar Singh Gabbar Singh - 2 months ago 37
reST (reStructuredText) Question

issue while creating an endpoint in Flask using Python

I'm trying to write a microservice for a RESTful web service.
I've a database created in 'Postgresql' and currently using Flask and psycopg2 (for conversion of db-object to json object).
Following is a part of my code, but for some reason I'm getting an error. The URI i'm trying to establish is somewhat like this

What should be right way to handle this?


app = Flask(__name__)
conn = psycopg2.connect("dbname='postgresdb'")
cur = conn.cursor(cursor_factory=RealDictCursor)

@app.route('/events/<dated>', methods=['GET'])
def getDatedEvents(dated):
date_obj = datetime.strptime(dated, '%Y%m%d')

SELECT event_id, timestamp
FROM event_tbl
WHERE timestamp < date_obj
ORDER BY timestamp

return json.dumps(cur.fetchall(), default=json_serial)

Error Output:

psycopg2.ProgrammingError: column "date_obj" does not exist
LINE 4: WHERE timestamp < date_obj

localhost - - [22/Dec/2017 17:22:29] "GET /events/20161020 HTTP/1.1" 500 -

Answer Source

You need to modify your query. Currently you are comparing timestamp with string date_obj which is why postgreSQL is throwing you error, since it cannot compare timestamp with string. Use string formatting to pass your date_obj in query:

    SELECT event_id, timestamp
    FROM event_tbl
    WHERE timestamp < '{}'
    ORDER BY timestamp
    LIMIT 25
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download