Glad to hear that. Please keep threads in the mailing list. Moritz
Am 2. Juni 2020 18:59:15 MESZ schrieb Uwe Fischer <gisfi...@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-boun...@lists.osgeo.org] Im Auftrag >von Moritz Lennert >Gesendet: Dienstag, 2. Juni 2020 15:07 >An: grass-user@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@lists.osgeo.org >https://lists.osgeo.org/mailman/listinfo/grass-user _______________________________________________ grass-user mailing list grass-user@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/grass-user