layer_times_postgres.py

#!/usr/bin/env python3
import os
import sys
import cgi
import json
from datetime import datetime
import psycopg2
from psycopg2 import sql


def bad_request(msg):
    print("""Status: 400 Bad Request
Content-type: text/plain
Access-Control-Allow-Origin: *

{}""".format(msg))
    sys.exit(1)


def get_connect_str():
    pw_filename = "__POSTGRES_PASSWORD_FILE__"
    connect_str = "host=__POSTGRES_HOST__ " \
                  "port=__POSTGRES_PORT__ " \
                  "dbname=__POSTGRES_DBNAME__ " \
                  "user=__POSTGRES_USER__"
    if not os.path.isfile(pw_filename) or '__' in connect_str:
        print("""Status: 500 Backend Misconfigured\n
Content-type: text/plain
Access-Control-Allow-Origin: *

Backend has not been configured properly and doesn't know how to communicate with the database.
Please contact site administrator.
    """)
        sys.exit(1)

    with open(pw_filename, 'r') as pw_file:
        password = pw_file.read().strip()
        connect_str += " password={}".format(password)
    return connect_str


connect_str = get_connect_str()
form = cgi.FieldStorage()
table_name = form.getvalue('layer')
start_time = form.getvalue('start_time')
end_time = form.getvalue('end_time')
num_times = form.getvalue('num_times', 0)
order = form.getvalue('order', 'ASC')

if table_name is None:
    bad_request("Missing required parameter 'layer'.")

if start_time is not None:
    try:
        start_time = datetime.strptime(start_time, "%Y-%m-%dT%H:%M:%S")
    except ValueError:
        bad_request("""Bad 'start_time' format. Expected: 'YYYY-MM-DDTHH:MM:SS'""")

if end_time is not None:
    try:
        end_time = datetime.strptime(end_time, "%Y-%m-%dT%H:%M:%S")
    except ValueError:
        bad_request("""Bad 'end_time' format. Expected: 'YYYY-MM-DDTHH:MM:SS'""")

try:
    num_times = int(num_times)
    if num_times < 0:
        raise ValueError("'num_times' must greater or equal to 0.")
except ValueError:
    bad_request("""Invalid integer for 'num_times'.""")

order = order.upper()
if order not in ('ASC', 'DESC'):
    bad_request("""'order' must be either 'ASC' or 'DESC'.""")

query_str = "SELECT start_time FROM {}"
if start_time is not None or end_time is not None:
    query_str += " WHERE "
if start_time is not None:
    query_str += "start_time >= '{}'".format(start_time.strftime('%Y-%m-%dT%H:%M:%S'))
if start_time is not None and end_time is not None:
    query_str += " AND "
if end_time is not None:
    query_str += "start_time <= '{}'".format(end_time.strftime('%Y-%m-%dT%H:%M:%S'))
query_str += " ORDER BY start_time {}".format(order)
if num_times != 0:
    query_str += " LIMIT {}".format(num_times)

try:
    conn = psycopg2.connect(connect_str)
    with conn:
        with conn.cursor() as cur:
            cur.execute(sql.SQL(query_str).format(sql.Identifier(table_name)))
            times = cur.fetchall()
except psycopg2.errors.DataBaseError:
    print("""Status: 500 Database Error
Content-type: text/plain
Access-Control-Allow-Origin: *

Error requesting time information from database.
""")
    sys.exit(1)

# postgres returned a list of tuples
times = [time_tuple[0] for time_tuple in times]

print("Content-Type: application/json")
print("Access-Control-Allow-Origin: *")
print()  # blank line, end of headers
print(json.dumps(times))