元组列表-Python/PostgreSQL返回SETOF记录的类型

问题描述

所以从此代码:

from dosql import *
import cgi
import simplejson as json

def index(req, userID):
    userID = cgi.escape(userID)

    get = doSql()
    rec = get.execqry("select get_progressrecord('" + userID + "');", False)

    return json.dumps(rec)

请注意，变量rec从我在PostgreSQL中创建的此已定义函数接收到来自数据库的查询:

Notice that the variable rec, receives a query from the database, from this defined function I created in PostgreSQL:

create or replace function
    get_progressrecord(in int, out decimal(5,2), out decimal(5,2), out decimal(4,2), out text, out int, out decimal(4,2))
    returns setof record as

$$
    select height, weight, bmi, healthStatus, age, changePercentage from progressrecord
    where userID = $1;
$$
language 'sql';

现在，假设userID = 7，并且我的表在userID(7)处的值是:

Now, suppose that the userID = 7, and the value of my table at userID (7) is:

但是当我尝试获取该记录时，会收到以下消息:

But when I try to get that record, I receive this:

[["(300.00,30.00,3.33，体重不足，21,0.00)]

然后(通过全面的分析)我发现这是人物名单.意义， [(300.00,30.00,3.33，体重不足，21,0.00)] 是LIST中的元组[0]，并且(300.00,30.00,3.33，体重不足，21,0.00)是TUPLE中的元素[0].

To which I then found out (from thorough analysis), that this is a LIST OF TUPLES.Meaning,[(300.00,30.00,3.33,underweight,21,0.00)] is tuple[0] at the LIST, and(300.00,30.00,3.33,underweight,21,0.00) is element[0] at the TUPLE.

问题是，非常(300.00,30.00,3.33，underweight，21,0.00)被识别为一个字符串或其他任何字符串，并且深入到TUPLE的列表.还有其他方法可以提取每个元素(剪切字符串吗?)并将其放入适当的列表中?

The problem is, that very (300.00,30.00,3.33,underweight,21,0.00) is recognized as ONE string or whatsoever, and it is deep down into the LIST of TUPLE. Is there other ways I could extract each element (cutting the string?) and place it into a proper list?

赞: [300.00,30.00,3.33，体重不足，21,0.00]

非常感谢. :)

推荐答案

SELECT get_progressrecord(ID)将返回类型为record的单个列.

SELECT get_progressrecord(ID) will return a single column of type record.

SELECT * FROM get_progressrecord(ID)将返回多个列(匹配您的out参数).

SELECT * FROM get_progressrecord(ID) will return multiple columns (matching your out params).

顺便说一句，您的输出字段没有名称这一事实可能会使您的函数难以使用.我发现更容易使用RETURNS SETOF RECORD的另一种语法:

As an aside, the fact that your output fields have no names might make your function a little difficult to work with. There's also an alternative syntax for RETURNS SETOF RECORD which I find easier:

CREATE OR REPLACE FUNCTION get_progressrecord(int)
  RETURNS TABLE(
    height decimal(5,2),
    weight decimal(5,2),
    bmi decimal(4,2),
    healthStatus text,
    age int,
    changePercentage decimal(4,2)
  ) AS
  ...

这篇关于元组列表-Python/PostgreSQL返回SETOF记录的类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！