postgresql 无法循环存储过程中的嵌套字典：心理学g2

iecba09b 于 2023-01-13 发布在 PostgreSQL

关注(0)|答案(1)|浏览(146)

我尝试使用psycopg2中的存储过程将列表中嵌套字典的值添加到表中，这是我从JSON应用程序收到的模式：

[
    {
        "team_id": 236,
        "lineup": [
            {
                "player_id": 3043,
                "country": {
                    "id": 61,
                    "name": "Denmark"
                }
            }
        ]
    },
    {
        "team_id": 237,
        "lineup": [
            {
                "player_id": 3045,
                "country": {
                    "id": 62,
                    "name": "Italy"
                }
            }
        ]
    }
]

我试图将玩家的country添加到Postgres的一个表中，id的类型为integer，name的类型为VARCHAR，下面是我的存储过程：

'''
    CREATE PROCEDURE insert_country_by_lineups(data JSON)
    AS $$
    BEGIN
        FOR team IN SELECT * FROM json_array_elements(data) LOOP
            FOR player in SELECT * FROM json_array_elements(team->'lineup') LOOP
                INSERT INTO country(id,name) 
                VALUES (CAST(player->'country'->>'id' AS integer), player->'country'->>'name')
                ON CONFLICT DO NOTHING RETURNING id;
            END LOOP;
        END LOOP; 
    END;
    $$ LANGUAGE plpgsql;
    '''

但是，当我在光标上执行该过程时，我总是得到这样的结果：

loop variable of loop over rows must be a record variable or list of scalar variables
LINE 5:         FOR team IN SELECT * FROM json_array_elements(data...

我哪里做错了？

postgresql

来源：https://stackoverflow.com/questions/75021988/unable-to-loop-over-nested-dictionary-in-stored-procedure-psycopg2

1条答案

按热度按时间

bsxbgnwa1#

使用json模块获取国家id和name，然后使用psycopg2到INSERT到country表中。

import json
import psycopg2

con = psycopg2.connect(dbname="test", host='localhost', user='postgres', port=5432)
cur = con.cursor()

j = """[
    {
        "team_id": 236,
        "lineup": [
            {
                "player_id": 3043,
                "country": {
                    "id": 61,
                    "name": "Denmark"
                }
            }
        ]
    },
    {
        "team_id": 237,
        "lineup": [
            {
                "player_id": 3045,
                "country": {
                    "id": 62,
                    "name": "Italy"
                }
            }
        ]
    }
]"""

player_list = json.loads(j)
player_list
[{'team_id': 236,
  'lineup': [{'player_id': 3043, 'country': {'id': 61, 'name': 'Denmark'}}]},
 {'team_id': 237,
  'lineup': [{'player_id': 3045, 'country': {'id': 62, 'name': 'Italy'}}]}]

for row in player_list:
    country = row['lineup'][0]['country']
    id, name = country['id'], country['name']
    print(id, name)
    cur.execute('insert into country(id, name) values(%s, %s)', [id, name])
61 Denmark
62 Italy

con.commit()
cur.execute('select * from country')
cur.fetchall()
 [(61, 'Denmark'), (62, 'Italy')]

赞(0）回复(0）举报 2023-01-13

我来回答

postgresql 无法循环存储过程中的嵌套字典：心理学g2

1条答案

相关问题

热门标签

最新问答