h5. The snippet for Python
Please note that if you are using the code, the indents are very important in Python.
{code}
# Description: Use of psycopg2 module to query database ICES
# ---------------------------------------------------------------------------
# Created by Gerrit Hendriksen (gerrit.hendriksen@deltares.nl)
# v1.0 created on 21-05-2012 (ddmmyyyy)
#
# Description: retrieving data per icessquare form the ICES database
# ---------------------------------------------------------------------------
# import modules
import psycopg2
# 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 i.statsq, year, month, avg(temperature) as avg_T, avg(salinity) as avg_Sal,sdepth_bin
FROM ocean o,
icessquares i
WHERE st_within(the_point,the_geom) AND
sea_region LIKE 'North Sea%'
AND year > 1980
AND temperature IS NOT NULL and salinity IS NOT NULL
GROUP BY i.statsq, year, month, sdepth_bin
ORDER BY statsq,year, month
"""
# execute the query
cur.execute(strSql)
# store the result of the query into Tuple c
c = cur.fetchall()
print (c)
# closes the connection
cur.close()
conn.close()
# do something with the query result
# ---------------------------------------------------------------------------
{code}
The entire code can be downloaded as [Python Code|^vectors_readfrom_postgresql_ICES.py].
h5. The snippet for R
{code}
## Opens connection to ICES oceanographic database (online PostGreSQL database, copy of original <2010)
## hosted by Deltares, The Netherlands
## Fetches table with selected entries. In this case table nox (nitrate+nitrite average per ICES rectangle for the North Sea
##
library(RPostgreSQL)
## loads the PostgreSQL driver
drv <- dbDriver("PostgreSQL")
## Open a connection
con <- dbConnect(drv, dbname="ICES", host="postgresx03.infra.xtr.deltares.nl", user="dbices", password="vectors")
## Submits a statement
rs <- dbSendQuery(con, "SELECT i.statsq, year, month, avg(temperature) as avg_T, avg(salinity) as avg_Sal,sdepth_bin
FROM ocean o,
icessquares i
WHERE st_within(the_point,the_geom) AND
sea_region LIKE 'North Sea%'
AND year > 1980
AND temperature IS NOT NULL and salinity IS NOT NULL
GROUP BY i.statsq, year, month, sdepth_bin
ORDER BY statsq,year, month")
## fetch all elements from the resultSet into a data.frame
df <- fetch(rs, n = -1)
## Check number of records
dim(df)
## write comma-separated data to file
write.table(df, file = "Filename.txt", sep = ",")
## Closes the connection
dbDisconnect(con)
## Frees all the resources on the driver
dbUnloadDriver(drv)
{code}Please try out and adjust to your needs and of course share it with the OpenEarth community. You are challenged to share your modifications.
...