Child pages
  • PostgreSQL access with Python
Skip to end of metadata
Go to start of metadata
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
  • No labels