Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite
1

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

share|improve this question

2 Answers

up vote 2 down vote

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)
share|improve this answer
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 at 2: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 at 3: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 at 3:30
up vote 1 down vote accepted

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

share|improve this answer
1  
Every time I see things like this it reminds me of why I avoid ORMs when I can ;-) – Craig Ringer May 16 at 3:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.