On 2/06/20 14:42, Uwe Fischer wrote:
Hello list,
I tried the following expression in a Python script, but it does not work
Please be more specific than just saying "it does not work". Do you see an error message ? Wrong results in the table ?
(I need to subtract the lowest value for column „srtmh“ from all other values for that item and write the result to column „strmh2“):
grass.run_command('v.db.update', map='dgnpt', column='srtmh2', qcolumn="('srtmh' - (select min('srtmh') from 'dgnpt'))")
I think your quoting is off.
For me such a command works. E.g. in the NC demo dataset:
g.copy vect=censusblk_swwake,test
v.db.addcolumn test col="test double precision"
and then in python:
import grass.script as g
g.run_command('v.db.update', map='test', column='test', value="'HH_SIZE'-(SELECT avg('HH_SIZE') FROM test)")
g.run_command('v.db.update', map='test', column='test', value="HH_SIZE-(SELECT avg(HH_SIZE) FROM test)")
However, if I quote like you do:
g.run_command('v.db.update', map='test', column='test', value="'HH_SIZE'-(SELECT avg('HH_SIZE') FROM 'test')")
the result is all zeroes.
This tells the database that all words in single quotes are strings, not db entity names.
The SQL expression itself seems to be ok, because it works in SpatiaLite in the following form:
update dgnpt set srtmh2 = srtmh-(select min(srtmh) from srtmp);
Try running
update dgnpt set srtmh2 = 'srtmh'-(select min('srtmh') from 'srtmp')
You probably won't get what you expect, either.
And by the way, when I try scripts I often get a message „Process
ended with non-zero return code 1. See errors in the (error) output.“
But what is that error output? Where can I read the error message in detail? Sorry for that question, but I found no hints in the manual pages. 
Generally, you have to look further up for the actual error, at the beginning of the error message. E.g. when I run the above command but using an incorrect table name:
g.run_command('v.db.update', map='test', column='test', value="HH_SIZE-(SELECT avg(HH_SIZE) FROM test2)")
I get:
*****************************
DBMI-SQLite erreur de pilote :
Error in sqlite3_prepare():
no such table: test2
DBMI-SQLite erreur de pilote :
Error in sqlite3_prepare():
no such table: test2
ERREUR : Error while executing: 'UPDATE test SET test=HH_SIZE-(SELECT
avg(HH_SIZE) FROM test2)'
Traceback (most recent call last):
File "/usr/lib/grass78/scripts/v.db.update", line 129, in <module>
sys.exit(main())
File "/usr/lib/grass78/scripts/v.db.update", line 120, in main
grass.write_command('db.execute', input='-', database=database, driver=driver, stdin=cmd)
File "/usr/lib/grass78/etc/python/grass/script/core.py", line 588, in write_command
return handle_errors(returncode, returncode, args, kwargs)
File "/usr/lib/grass78/etc/python/grass/script/core.py", line 342, in handle_errors
raise CalledModuleError(module=None, code=code,
grass.exceptions.CalledModuleError: Module run None db.execute input=- database=/home/mlennert/GRASSDATA/nc_spm_08_grass7/user1/sqlite/sqlite.db driver=sqlite ended with error
Process ended with non-zero return code 1. See errors in the (error) output.
*****************************
I see the 'Process ended with non-zero return code 1" and going further up I see:
ERREUR : Error while executing: 'UPDATE test SET test=HH_SIZE-(SELECT
avg(HH_SIZE) FROM test2)'
and even further up:
DBMI-SQLite erreur de pilote :
Error in sqlite3_prepare():
no such table: test2
Moritz