Storing the query result in a list variable in plpython function

Question

I am very new to postgresql and writing functions so bear with me. I need to transform a Python script into a postgresql function and I intend to use PL/Python for the purpose. However I am having some problems in doing so. When executing the function I receive an error:

ERROR: TypeError: unsupported operand type(s) for +: 'int' and 'dict' SQL state: XX000 Context: Traceback (most recent call last): PL/Python function "ellipse", line 5, in meanX=float(sum(Xarray))/len(Xarray) if len(Xarray) > 0 else float('nan') PL/Python function "ellipse"

As to my knowledge, the query stores the result in dictionary which then results in this error (since I am trying to operate with list in the script). At least I think this can be the problem. So my question would be - is there a way to store the query result in a list variable?

CREATE OR REPLACE FUNCTION ellipse()
returns setof ellipse_param as $$

Xarray=plpy.execute("select laius from proov")
Yarray=plpy.execute("select pikkus from proov")
meanX=float(sum(Xarray))/len(Xarray) if len(Xarray) > 0 else float('nan')
meanY=float(sum(Yarray))/len(Yarray) if len(Yarray) > 0 else float('nan')
Xdevs=[]
Ydevs=[]
for x in Xarray:
    dev=x-meanX
    Xdevs.append(dev)
    dev=0
for y in Yarray:
    dev=y-meanY
    Ydevs.append(dev)
    dev=0

sumX=0 
sumY=0
for x in Xdevs:
    sumX+=x**2
for y in Ydevs:
    sumY+=y**2
Xaxes=sqrt(sumX/len(Xdevs))
Yaxes=sqrt(sumY/len(Ydevs))
A=sumX-sumY
B=sqrt(A**2+(((float(sum([a*b for a,b in zip(Xdevs,Ydevs)])))**2)*4))
C=float(sum([a*b for a,b in zip(Xdevs,Ydevs)]))*2
rotation=(atan(((A+B)/C)))
Sx=sqrt(((float(sum([(a*cos(rotation)-b*sin(rotation))**2 for a,b in  zip(Xdevs,Ydevs)])))/(len(Xdevs)-2))*2)
Sy=sqrt(((float(sum([(c*sin(rotation)+d*cos(rotation))**2 for c,d in zip(Xdevs,Ydevs)])))/(len(Xdevs)-2))*2)
return meanX, meanY, rotation, Xaxes, Yaxes
$$ LANGUAGE plpython3u;

Answer 1

plpy.execute will give you a list of dict, so you want something like

sum([x['laius'] for x in Xarray])

More info in the docs here http://www.postgresql.org/docs/devel/static/plpython-database.html

Edit: I read too quickly and skimmed over your entire function - you may want to put the list constructor higher up, probably right after executing your queries, so that you have a list of values to use later on (I didn't notice how much of the later code assumes the data are in a simple list).