[GRASS-user] SQL in GRASS

[Moritz Lennert]

Glad to hear that. Please keep threads in the mailing list.

Moritz

Am 2. Juni 2020 18:59:15 MESZ schrieb Uwe Fischer <gisfisch at t-online.de>:
>Hello Moritz,
>
>thanks again, it works now. Indeed I had to many useless quotes. Maybe
>it came from former attempts I did with db.execute statements, where I
>needed a lot of quotes. But I could have seen it myself, comparing with
>the SpatiaLite statement that worked fine.
>
>Thanks you.
>
>Mit freundlichen Grüßen,
>UWE FISCHER
>
>--
>
>Ingenieurbüro Fischer
>Esbecker Str. 8
>31036 Eime
>Tel.: 05182/8325
>Mobil: 0172/8876934
>
>-----Ursprüngliche Nachricht-----
>Von: grass-user [mailto:grass-user-bounces at lists.osgeo.org] Im Auftrag
>von Moritz Lennert
>Gesendet: Dienstag, 2. Juni 2020 15:07
>An: grass-user at lists.osgeo.org
>Betreff: Re: [GRASS-user] SQL in GRASS
>
>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
>_______________________________________________
>grass-user mailing list
>grass-user at lists.osgeo.org
>https://lists.osgeo.org/mailman/listinfo/grass-user