Page History

# Description: Use of psycopg2 module to query database ICES
# ---------------------------------------------------------------------------

# import modules
import psycopg2
import matplotlib.pyplot as plt

# create connection to ices database
conn = psycopg2.connect("dbname=ICES host=postgresx03.infra.xtr.deltares.nl user=dbices password=vectors")

# create a cursor object called cur
cur = conn.cursor()

# construct a query string
strSql = """
select year,month,to_number(day, '9999'),cphl
from ocean
where (select st_within(the_point,the_geom) from icessquareicessquares where statsq = '31F2')
and cphl IS NOT NULL and sdepth < 10 and year = 2003
order by year,month,day,cphl
"""

# execute the query
cur.execute(strSql)

# store the result of the query into Tuple c
c = cur.fetchall()

# closes the connection
cur.close()

# now store day and avg(cphl) in two separate arrays
cphl = []
days = []

for i in range(len(c)):
    days.append((c[i])[2])
    cphl.append((c[i])[3])

# plot the
plt.xlabel('days')
plt.plot(cphl)
plt.show()