Gabbar Singh Gabbar Singh - 8 months ago 125
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

localhost/events/20171222
What should be right way to handle this?

Code:

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')
#print(type(date_obj))
#print(date_obj)

cur.execute("""
SELECT event_id, timestamp
FROM event_tbl
WHERE timestamp < date_obj
ORDER BY timestamp
LIMIT 25
""")

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:

cur.execute("""
    SELECT event_id, timestamp
    FROM event_tbl
    WHERE timestamp < '{}'
    ORDER BY timestamp
    LIMIT 25
    """.format(date_obj))`
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download