SQL in GRASS

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL in GRASS

Uwe Fischer

Hello list,

 

I tried the following expression in a Python script, but it does not work (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'))")

 

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);

 

What could be the reason that it does not work in GRASS/in the script?

 

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.  :-(

 

 

Thanks a lot for help, Uwe

 


_______________________________________________
grass-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user
Reply | Threaded
Open this post in threaded view
|

Re: 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
_______________________________________________
grass-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]>:

>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:[hidden email]] Im Auftrag
>von Moritz Lennert
>Gesendet: Dienstag, 2. Juni 2020 15:07
>An: [hidden email]
>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
>[hidden email]
>https://lists.osgeo.org/mailman/listinfo/grass-user
_______________________________________________
grass-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user