[GRASS-user] SQL in GRASS

[Moritz Lennert]

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