#!/usr/bin/python
import pgdb
import matplotlib
import matplotlib.pyplot as plt

params = {'host':'localhost', 'user': 'postgres', 'database':'postgres', 'password':'admin'}

connection1 = pgdb.Connection(**params)
connection1.autocommit=False
cursor1 = connection1.cursor()


def query():
    # Here we test some concurrency issues.
    xy = "select FLOOR(year/10)*10 AS decade, AVG(population), STDDEV(population) from popdata GROUP BY decade ORDER BY decade;"
    print("U1: (start) "+ xy)
    cursor1.execute(xy)
    data = cursor1.fetchall()
    connection1.commit()

    x = []
    y = []
    y_err = []

    for r in data:
        # you access ith component of row r with r[i], indexing starts with 0
        # check for null values represented as "None" in python before conversion and drop
        # row whenever NULL occurs
        print("Considering tuple", r)

        if r[0] is not None and r[1] is not None and r[2] is not None:
            x.append(int(r[0]))
            y.append(int(r[1]))
            y_err.append(int(r[2]))
        elif r[2] is None:
            x.append(int(r[0]))
            y.append(int(r[1]))
            y_err.append(0)
        else:
            print("Dropped tuple ", r)
    print("x:", x)
    print("y:", y)
    return[x, y, y_err]


def close():
    connection1.close()


# when calling python filename.py the following functions will be executed:
[x, y, y_err] = query()
# plt.scatter(x, y)
plt.figure()
plt.errorbar(x, y, yerr=y_err)
plt.show()  # display figure if you run this code locally
plt.savefig("figure.png") # save figure as image in local directory
close()