[GRASS-user] python db.execute Unable to open Database

GRASS GRASS user
,
1

Hi,

I am writing a short python script to create a PostGIS table, loop through a csv file, run a series of grass commands, then save the final vector polygons to the PostGIS table. All of the PostGIS commands were being issued by the db.execute.

In the code below I got the message:
ERROR: Unable to open database <host=localhost,dbname=ss_basin_test>

As seen in the 30th line I tried to send user & password along with the database info but that gave an error as well. When I tried to run db.login it said I had to use the “overwrite” flag. If I overwrite the login info this script I will have to reset it when I am done and I am not sure how to do that.

How do I connect to the db with db.execute and send the user/password information?
The first half of my code is below.

thanks,

Phil

IMPORT CSV & LOOP THROUGH COORDINATES FOR r.water.outlet

import os
import sys

#set up GRASS environment variables
sys.path.append(os.path.join(os.environ[‘GISBASE’], ‘etc’, ‘python’))
import grass.script as g
import grass.script.setup as gsetup
gisbase = os.environ[‘GISBASE’]

gisdb = ‘E:\DEM_12_5\grassdata’
location = ‘dem_12_5’
mapset = ‘PERMANENT’

first join the new snap table’s cat column to the original attribute table

v.out.ascii input=th_ss_aem_snap_r5@PERMANENT type=point output=C:\data\th_ss_aem_snap_r5.csv columns=COD_MUESTR format=point separator=comma precision=20

txtfile = ‘c:/data/th_ss_aem_snap_r5.csv’
txtfileWKT = ‘c:/data/th_ss_aem_snap_r5_WKT.txt’
pgsrid = ‘32618’

pg_user = ‘postgres’
pg_user_pwd = ‘xxxx’
pg_schema = ‘basin’
pg_tbl_name = ‘samp_basin’
pg_host = ‘localhost’
pg_dbname = ‘ss_basin_test’

txt_pg_tbl_create = 'CREATE TABLE ’ + pg_tbl_name + ’ (x double precision, y double precision, samp_id character varying(254) PRIMARY KEY, geom geometry(POLYGON, ’ + pgsrid + ‘) )’

#txt_pg_db = ‘host=’ + pg_host + ‘,dbname=’ + pg_dbname + ‘,user=’ + pg_user + ‘,password=’ + pg_user_pwd
txt_pg_db = ‘host=’ + pg_host + ‘,dbname=’ + pg_dbname

gsetup.init(gisbase, gisdb, location, mapset)

Create PostGIS Polygon Table

g.run_command(‘db.login’, driver=‘pg’, user=pg_user, password=pg_user_pwd )

g.run_command(‘db.execute’, sql=txt_pg_tbl_create , driver=‘pg’, database=txt_pg_db, schema=pg_schema)

2

If you want the GRASS vector to be exported to a PostGIS table (including geometry) then you probably should be using v.out.ogr. IIUC db.execute is intended to save only the attributes to PostgreSQL.

···

On 02/11/2016 00:25, Phillip Allen wrote:

Hi,

I am writing a short python script to create a PostGIS table, loop through a csv file, run a series of grass commands, then save the final vector polygons to the PostGIS table. All of the PostGIS commands were being issued by the db.execute.

In the code below I got the message:
ERROR: Unable to open database <host=localhost,dbname=ss_basin_test>

As seen in the 30th line I tried to send user & password along with the database info but that gave an error as well. When I tried to run db.login it said I had to use the “overwrite” flag. If I overwrite the login info this script I will have to reset it when I am done and I am not sure how to do that.

How do I connect to the db with db.execute and send the user/password information?
The first half of my code is below.

thanks,

Phil

IMPORT CSV & LOOP THROUGH COORDINATES FOR r.water.outlet

import os
import sys

#set up GRASS environment variables
sys.path.append(os.path.join(os.environ[‘GISBASE’], ‘etc’, ‘python’))
import grass.script as g
import grass.script.setup as gsetup
gisbase = os.environ[‘GISBASE’]

gisdb = ‘E:\DEM_12_5\grassdata’
location = ‘dem_12_5’
mapset = ‘PERMANENT’

first join the new snap table’s cat column to the original attribute table

v.out.ascii input=th_ss_aem_snap_r5@PERMANENT type=point output=C:\data\th_ss_aem_snap_r5.csv columns=COD_MUESTR format=point separator=comma precision=20

txtfile = ‘c:/data/th_ss_aem_snap_r5.csv’
txtfileWKT = ‘c:/data/th_ss_aem_snap_r5_WKT.txt’
pgsrid = ‘32618’

pg_user = ‘postgres’
pg_user_pwd = ‘xxxx’
pg_schema = ‘basin’
pg_tbl_name = ‘samp_basin’
pg_host = ‘localhost’
pg_dbname = ‘ss_basin_test’

txt_pg_tbl_create = 'CREATE TABLE ’ + pg_tbl_name + ’ (x double precision, y double precision, samp_id character varying(254) PRIMARY KEY, geom geometry(POLYGON, ’ + pgsrid + ‘) )’

#txt_pg_db = ‘host=’ + pg_host + ‘,dbname=’ + pg_dbname + ‘,user=’ + pg_user + ‘,password=’ + pg_user_pwd
txt_pg_db = ‘host=’ + pg_host + ‘,dbname=’ + pg_dbname

gsetup.init(gisbase, gisdb, location, mapset)

Create PostGIS Polygon Table

g.run_command(‘db.login’, driver=‘pg’, user=pg_user, password=pg_user_pwd )

g.run_command(‘db.execute’, sql=txt_pg_tbl_create , driver=‘pg’, database=txt_pg_db, schema=pg_schema)

_______________________________________________
grass-user mailing list
[grass-user@lists.osgeo.org](mailto:grass-user@lists.osgeo.org)
[http://lists.osgeo.org/mailman/listinfo/grass-user](http://lists.osgeo.org/mailman/listinfo/grass-user)

-- 
Micha Silver
Arava Drainage Authority
+972-523-665918