Как отправить список данных в postgresql из python, а затем выполнить итерацию через этот список в функцию insert postgresql?

#python #sql #database #postgresql #insert

#python #sql #База данных #postgresql #вставить

Вопрос:

У меня уже есть код на python, который отправляет данные в функцию postgresql, которую я также создал, но теперь я хочу отправить список этой функции, но я не уверен, как на самом деле создать такую функцию postgresql.

Функция Postgresql, которая вставит одну строку, выглядит следующим образом. Но я хочу отправить список данных из python, а затем выполнить цикл по этому списку внутри этой функции.

         CREATE OR REPLACE FUNCTION asg.insert_asg(
        recommendation_account text,
        recommendation_region text,
        recommendation_time text,
        asg_name text,
        asset_id text,
        launch_configuration_name text,
        min_size integer,
        max_size integer,
        desired_capacity integer,
        default_cooldown integer,
        availability_zones text,
        load_balancers_classic text,
        load_balancers_classic_missing text[],
        load_balancers_target_groups text,
        load_balancers_target_groups_missing text[],
        instances text[],
        create_time text,
        tags text[],
        termination_policies text,
        new_instances_protected_from_scale_in boolean,
        cloud_fit boolean,
        recommendations_content text[])
        RETURNS integer
        LANGUAGE 'plpgsql'
        COST 100
        VOLATILE PARALLEL UNSAFE
    AS $BODY$
    DECLARE 
            new_asg_id integer;
            
    BEGIN
        INSERT INTO asg.asg(recommendation_account, recommendation_region, recommendation_time, asg_name, asset_id, launch_configuration_name, min_size, max_size, desired_capacity, default_cooldown, availability_zones, load_balancers_classic, load_balancers_target_groups, create_time, termination_policies, new_instances_protected_from_scale_in, cloud_fit, load_balancers_classic_missing, load_balancers_target_groups_missing, instances, tags)
            VALUES(recommendation_account, recommendation_region, recommendation_time, asg_name, asset_id, launch_configuration_name, min_size, max_size, desired_capacity, default_cooldown, availability_zones, load_balancers_classic, load_balancers_target_groups, create_time, termination_policies, new_instances_protected_from_scale_in, cloud_fit, load_balancers_classic_missing, load_balancers_target_groups_missing, instances, tags)
            RETURNING asg.asg_id
            INTO new_asg_id;
        
        INSERT INTO asg.recommendations(recommendations_content, asg_id)
            VALUES (recommendations_content, new_asg_id);
        
        RETURN new_asg_id;
    END;
    $BODY$;

ALTER FUNCTION asg.insert_asg(text, text, text, text, text, text, integer, integer, integer, integer, text, text, text[], text, text[], text[], text, text[], text, boolean, boolean, text[])
    OWNER TO postgres;
 

Код Python выглядит следующим образом

 def insert_asg():
    #INSERT db from input json file to posgre sql table
    json_inputs = read_input_json(request.get_json())
    print(json_inputs)

    try:
        conn = connect()
        cur = conn.cursor()

        inserted_rows = 0
        query = "select asg.insert_list_asg("
        for json_input in json_inputs:    
            inserted_rows  = 1
            for param in json_input:            #For Loop for transfering string that was read from json file, to string that can be applied to postgre function
                if type(param) is list:
                    param = (str(param)).replace("'", "")
                    param = (str(param)[1:-2]).replace("{", "[").replace("}","]")
                    param = "{"   param   "}"

                if type(param) is not str:
                    param = str(param)
                else:
                    param = "'"   param   "'"

                if param == "None":
                    param = "null"
                
                query = query   param   ", "

            commas = query.rfind(",") #This will return the index of the last comma, and in the next line we will remove it as that is last paramater we sent to postgres function
            query = query[:commas]
        query  = ")"

        print(query)   
        # cur.execute(query)
        # conn.commit()   
        
        cur.close()
        return json.dumps({'success':True, 'inserted_rows':inserted_rows}), 200, {'ContentType':'application/json'} 
 

На самом деле мне не нужна помощь с кодом python, только с postgresql, но я добавил его туда, просто если кому-то нужно взглянуть на него.

Версия postgresql — 12.4, а версия python — 3.7

Комментарии:

1. Ваш код можно использовать для SQL-инъекции. Вы уверены, что хотите этого? И как выглядит ваш объект json? Вы можете поместить его в свою функцию базы данных и позволить PostgreSQL обрабатывать его за вас

2. Не беспокойтесь о внедрении sql. Вы говорите, что я могу отправить необработанный json с несколькими объектами внутри (один объект == одна строка), и postgresql может это обработать? Вот как это выглядит: pastebin.com/WuY8YRQx

3. Это недопустимый объект json. Но да, вы можете сделать это в SQL.

Ответ №1:

Небольшой пример с допустимым json:

 WITH i(j) AS (
    SELECT
        '{
        "recommendation_account": "foo",
        "recommendation_region": "None",
        "recommendation_time": "13:39:00",
        "recommendations": ["bar","baz"]
        }'::json
), step_1 AS (
    INSERT INTO asg.asg (
        recommendation_account,
        recommendation_region,
        recommendation_time)        
    SELECT  NULLIF(j #>> '{recommendation_account}', 'None')
            , NULLIF(j #>> '{recommendation_region}', 'None')
            , (NULLIF(j #>> '{recommendation_time}', 'None'))::time -- Will fail when not valid
    FROM    i
    RETURNING asg.asg_id
)
INSERT INTO asg.recommendations(asg_id, recommendations_content)
SELECT  asg_id,
        recommendations_content
FROM    step_1
    ,   i
    ,   json_array_elements_text(j #> '{recommendations}') jae(recommendations_content)
RETURNING id;
 

Но вы должны быть уверены, что у вас есть действительный объект json, и все содержимое также соответствует типам данных в ваших таблицах.