3

I am using Python with SQLAlchemy (and GeoAlchemy in my particular case), and I have a query that results in a single column of row objects. I wish to extract particular values from these row objects, but SQLAlchemy interprets the row object as a single entity (and returns them as strings, if I am correct). How can I get these individual values back more cleanly without parsing them in Python?

My real life use case: The PostGIS extension of PostgreSQL provides a function called ST_IsValidDetail. This function is designed to return a valid_detail row, which consists of boolean valid, a string reason, and a geometry location where the invalidity occurs. I have left off the PostGIS tag since I feel this question is more general than that. My query is something like SELECT ST_IsValidDetail('POINT(1 1)'::GEOMETRY); (with a more complicated geometry, of course).

jpmc26
  • 28,463
  • 14
  • 94
  • 146

2 Answers2

2

You can use:

SELECT (ST_IsValidDetail(the_value)).* FROM the_table;

... but unfortunately PostgreSQL actually executes the ST_IsValidDetail function once for each row. As a workaround you can mangle the query a little more, materializing via a common table expression then extracting the tuples in a second pass:

WITH interim_result(v) AS (
    SELECT ST_IsValidDetail(the_value) FROM the_table
)
SELECT (v).* FROM interim_result;

The parens around (v) are required to tell the parser you're referring to a value, not to a table name.

Demo:

CREATE OR REPLACE FUNCTION multirows(x IN integer, a OUT integer, b OUT integer, c OUT integer) AS
$$
BEGIN
    RAISE NOTICE 'multirows(%) invoked', x;
    a := x;
    b := x+1;
    c := x+2;
    RETURN;
END;
$$ LANGUAGE plpgsql;

craig=> SELECT multirows(x) FROM generate_series(1,2) x;
NOTICE:  multirows(1) invoked
NOTICE:  multirows(2) invoked
 multirows 
-----------
 (1,2,3)
 (2,3,4)
(2 rows)

craig=> SELECT (multirows(x)).* FROM generate_series(1,2) x;
NOTICE:  multirows(1) invoked
NOTICE:  multirows(1) invoked
NOTICE:  multirows(1) invoked
NOTICE:  multirows(2) invoked
NOTICE:  multirows(2) invoked
NOTICE:  multirows(2) invoked
 a | b | c 
---+---+---
 1 | 2 | 3
 2 | 3 | 4
(2 rows)




craig=> WITH interim(v) AS (SELECT multirows(x) FROM generate_series(1,2) x)
SELECT (v).* FROM interim;
NOTICE:  multirows(1) invoked
NOTICE:  multirows(2) invoked
 a | b | c 
---+---+---
 1 | 2 | 3
 2 | 3 | 4
(2 rows)
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Thank you very much for your effort. Unfortunately, I was looking for a way to do this through Python, specifically the SQLAlchemy ORM. Sorry for the confusion; please let me know if you have any suggestions on making the question more clear. – jpmc26 May 16 '13 at 02:37
  • Usually folks using ORMs can turn the native SQL approach back into their ORM's query-language dialect. I tend to set out how it works in Pg its self in case it helps. – Craig Ringer May 16 '13 at 03:09
  • True; I make a habit or writing my queries directly against the database first, too. I already knew about the `.columnname` syntax, although I didn't know about the `.*` syntax. I have no idea how those translate into SQLAlchemy, though. – jpmc26 May 16 '13 at 03:30
  • 1
    I happened across this today, and a long time ago (but after this question), I've come to realize how ridiculous and unmaintainable it was that I was trying to build queries with even moderate complexity using SQLAlchemy objects. I should not have been so afraid of using raw SQL in code. Properly parameterized, it does not carry any security risks and is much more flexible and maintainable. As such, I've changed the accepted answer to this one. To run raw SQL, see [here](http://stackoverflow.com/a/22084672/1394393). – jpmc26 Sep 16 '15 at 05:16
  • @jpmc26 Yep. ORMs are great for automating the easy stuff, but it's nice to be able to bypass them when you have tricky, performance-critical, complicated, or platform-specific code. – Craig Ringer Sep 16 '15 at 05:43
2

I have found a way that I took from the example on the ST_IsValidDetail page. Apparently, the following syntax is valid:

SELECT gid, reason(ST_IsValidDetail(the_geom)), ST_AsText(location(ST_IsValidDetail(the_geom)))

Note the reason and location "calls" wrapped around the function call; the name of the columns in the row returned by ST_IsValidDetail are treated almost like functions. It turns out you can trick SQLAlchemy into doing the same thing. (Assume session is a previously set up Session object and db_geom is a GeoAlchemy geometry object.)

from sqlalchemy import func as sqlfunc
result = session.query(sqlfunc.reason(sqlfunc.ST_IsValidDetail(db_geom)), sqlfunc.ST_AsText(sqlfunc.location(sqlfunc.ST_IsValidDetail(db_geom)))).one()

result[0] will contain the reason, and result[1] will contain the WKT of the location. (We can use label to give the columns actual names.)

To trim it down without using the PostGIS functions:

from sqlalchemy import func as sqlfunc
result = session.query(sqlfunc.columnname(sqlfunc.myrowfunc('some input string')).label('mylabel')).one()

This makes SQLAlchemy think that columnname is a function and sends SQL to the database in the form

SELECT columnname(myrowfunc('some input string')) AS mylabel;

I haven't experimented with it yet, but if there is a way to get SQLAlchemy to consider our row to be the table we're selecting FROM, that may work as well. (See the very bottom of the ST_IsValidDetail page.)

jpmc26
  • 28,463
  • 14
  • 94
  • 146