python_psycopg2_postgis.md

Python psycopg2 & PostGIS

Load some data into the database

From the OSGeo4W Shell

ogr2ogr -f PostgreSQL PG:"dbname=postgis host=localhost user=postgres password=postgres" -a_srs "EPSG:27700" county_region.shp -nlt MULTIPOLYGON

Use psycopg2 in the Python Interpreter

    import psycopg2
    import psycopg2.extras

    # Create a connection to the database
    conn = psycopg2.connect("host=localhost dbname=postgis user=postgres password=postgres")

    # Create a regular cursor which will return a list of rows with each row
    # being a tuple of values in column order
    cur = conn.cursor()

    # Execute a query and return all rows as a list
    cur.execute("SELECT ogc_fid, name, st_asewkt(st_centroid(wkb_geometry)) as geom FROM county_region limit 10")
    rows = cur.fetchall()

    print
    print "# Rows as a list:"
    print
    print rows

    colnames = [desc.name for desc in cur.description]

    print
    print "# Column names as a list:"
    print
    print colnames

# Explicitly close the cursor once we're done with it
cur.close()


# Create a DictCursor which returns a list of rows with each row being a
# dict whos keys are the column names and values the row values
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

# Execute another query, this time retieving the geometry as an EWKT string of
# the centroid
cur.execute("SELECT ogc_fid, name, st_asewkt(st_centroid(wkb_geometry)) as geom FROM county_region limit 10")
rows = cur.fetchall()
row = rows[0]

print
print "# Individual row values accessed via column name:"
print
print 'ogc_fid:', row['ogc_fid']
print 'name:', row['name']
print 'geom:', row['geom']

# Explicitly close the cursor once we're done with it
cur.close()

# Close the connection
conn.close()

Same as above but using with to auto-close the connection and cursor

import psycopg2
import psycopg2.extras

# Create a connection to the database
with psycopg2.connect("host=localhost dbname=postgis user=postgres password=postgres") as conn:

    # Create a regular cursor which will return a list of rows with each row
    # being a tuple of values in column order
    with conn.cursor() as cur:

        # Execute a query and return all rows as a list
        cur.execute("SELECT ogc_fid, name, st_asewkt(st_centroid(wkb_geometry)) as geom FROM county_region limit 10")
        rows = cur.fetchall()

        print
        print "# Rows as a list:"
        print
        print rows

        colnames = [desc.name for desc in cur.description]

        print
        print "# Column names as a list:"
        print
        print colnames

    # Create a DictCursor which returns a list of rows with each row being a
    # dict whos keys are the column names and values the row values
    with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:

        # Execute another query, this time retieving the geometry as an EWKT string of
        # the centroid
        cur.execute("SELECT ogc_fid, name, st_asewkt(st_centroid(wkb_geometry)) as geom FROM county_region limit 10")
        rows = cur.fetchall()
        row = rows[0]

        print
        print "# Individual row values accessed via column name:"
        print
        print 'ogc_fid:', row['ogc_fid']
        print 'name:', row['name']
        print 'geom:', row['geom']