import psycopg2
uname = "dbices"
pwd = "vectors"
host = "postgresx03.infra.xtr.deltares.nl"
dbname= "ICES"
# function the gets the data from a database using connection strings and a string with a SQL command.
def getdata(dbname,host,uname,pwd,strSql):
conn = psycopg2.connect("dbname="+dbname+" host="+host+" user="+uname+" password="+pwd)
cur = conn.cursor()
arr = []
cur.execute(strSql)
arr = cur.fetchall()
conn.close()
return arr
# Some examples of requests
# - get the average of dissolved oxygen from the ICES database for ICESSQUARE 32F3
strSql = """select avg(doxy)
from ocean o,icessquares i
where st_contains(i.the_geom,o.the_point)
and year = 2005
and i.statsq= '32F3'
"""
arrdoxy = getdata(dbname,host,uname,pwd,strSql)
print arrdoxy
# - get the all points from the ICES database for ICESSQUARE 32F3
# In GIS terms, select by theme operation
strSql = """select st_x(the_point),st_y(the_point)
from ocean o,icessquares i
where st_contains(i.the_geom,o.the_point)
and year = 2005
and i.statsq= '32F3'
"""
arrpoints = getdata(dbname,host,uname,pwd,strSql)print arrpoints |