Оптимизация UPDATE в базу данных

Один запрос создать многомерный массив из второй таблице и подставлять данные для него в уже готовый запрос для первой таблицы. Тогда нам только 1 раз надо обращаться к справочнику, так его назовём. 

Как вариант. Прокладка + тригер. На коленке.

import psycopg2
from pprint import pprint

conn = psycopg2.connect(database='localdb', user='ххх', 
                        password='ххх', host='localhost')
cursor = conn.cursor()

conditions = [
    {
      "value": "name_3",
      "label": "tmp_condition",
      "checked": True
    },
    {
      "value": "name_1",
      "label": "IF EXISTS up_conditions()",
      "checked": True
    },
    {
      "value": "name_7",
      "label": "Demo",
      "checked": False
    }
  ]

print('\n===========Conditions') 
pprint(conditions)

print('\n===========Default')
s = """
               SELECT condition.*, form_conditions.*  from condition 
               JOIN form_conditions 
               ON form_conditions.condition_id = condition.id;"""

cursor.execute( s )
for row in cursor.fetchall():
    print(row)

cursor.execute('DROP TRIGGER IF EXISTS up_conditions ON tmp_tbl;')
cursor.execute('DROP FUNCTION IF EXISTS up_conditions();')
cursor.execute('DROP TABLE IF EXISTS tmp_tbl')
cursor.execute('''
create table tmp_tbl
(
     id     integer,
     v      varchar,
     l      varchar,
     c      boolean
);
               ''')
               
cursor.execute('''
CREATE FUNCTION up_conditions() RETURNS trigger AS $up_conditions$
    BEGIN
       UPDATE condition 
            SET display_name = NEW.l
            WHERE name = NEW.v; 
       UPDATE form_conditions 
            SET is_checked = NEW.c
            WHERE id = NEW.id; 
       RETURN NEW;
    END;
$up_conditions$ LANGUAGE plpgsql;
''')

cursor.execute('''
CREATE TRIGGER up_conditions AFTER INSERT OR UPDATE ON tmp_tbl
    FOR EACH ROW EXECUTE FUNCTION up_conditions();

''')

#======================


print('\n===========Tmp')
u = '''INSERT INTO tmp_tbl ( id, v, l, c ) VALUES 
                    ( 
                        (SELECT id FROM condition WHERE name=%s),
                        %s, %s, %s );'''

for c in conditions:
    cursor.execute(u, (  c['value'], c['value'], c['label'], c['checked']) )

conn.commit()

cursor.execute('select * from tmp_tbl')
for row in cursor.fetchall( ):
    print(row)

    
print('\n===========Result')
cursor.execute(s)
for row in cursor.fetchall( ):
    print(row)


cursor.close()
conn.close()
exit()

Результат:

(pyenv) [www@localhost ~]$ python /var/www/aweb/pyenv/pyd/scripts/pgtest.py

===========Conditions
[{'checked': True, 'label': 'tmp_condition', 'value': 'name_3'},
 {'checked': True, 'label': 'IF EXISTS up_conditions()', 'value': 'name_1'},
 {'checked': False, 'label': 'Demo', 'value': 'name_7'}]

===========Default
(1, 'name_1', 'Used 1', True, 1, 1, 1, True)
(2, 'name_2', 'Used 2', True, 2, 2, 2, True)
(3, 'name_3', 'Used 3', True, 3, 3, 3, True)
(4, 'name_4', 'Used 4', True, 4, 4, 4, True)
(5, 'name_5', 'Used 5', True, 5, 5, 5, True)
(6, 'name_6', 'Used 6', True, 6, 6, 6, True)
(7, 'name_7', 'Used 7', True, 7, 7, 7, True)
(8, 'name_8', 'Used 8', True, 8, 8, 8, True)
(9, 'name_9', 'Used 9', True, 9, 9, 9, True)

===========Tmp
(3, 'name_3', 'tmp_condition', True)
(1, 'name_1', 'IF EXISTS up_conditions()', True)
(7, 'name_7', 'Demo', False)

===========Result
(2, 'name_2', 'Used 2', True, 2, 2, 2, True)
(4, 'name_4', 'Used 4', True, 4, 4, 4, True)
(5, 'name_5', 'Used 5', True, 5, 5, 5, True)
(6, 'name_6', 'Used 6', True, 6, 6, 6, True)
(8, 'name_8', 'Used 8', True, 8, 8, 8, True)
(9, 'name_9', 'Used 9', True, 9, 9, 9, True)
(3, 'name_3', 'tmp_condition', True, 3, 3, 3, True)
(1, 'name_1', 'IF EXISTS up_conditions()', True, 1, 1, 1, True)
(7, 'name_7', 'Demo', True, 7, 7, 7, False)

мое текущее решение:

    def product_types_update(self, session, data):
        """
        Update data for relations tables
        :param data:
        :return:
        """
        if data:
            relation_true, relation_false = self.get_relation_data(self.condition_data)
            if relation_true:
                self.recipient_query(session, True, relation_true)
            if relation_false:
                self.recipient_query(session, False, relation_false)

    def product_query(self, session, statement, relation):
        query = update(FormProductTypes).values({"is_checked": statement})
        query = query.where(FormProductTypes.product_type_id == ProductType.id)
        query = query.where(FormProductTypes.form_id == self.form_id)
        query = query.where(ProductType.name.in_(relation))
        session.execute(query)

где data:

 def get_relation_data(self, data):
        relation_true = []
        relation_false = []
        for item in data:
            if item.get('checked'):
                relation_true.append(item.get('value'))
            else:
                relation_false.append(item.get('value'))
        return relation_true, relation_false

что трансформируется в такой запрос:

UPDATE form_conditions
SET is_checked=true
FROM condition
WHERE form_conditions.condition_id = condition.id
  AND form_conditions.form_id = 1
  AND condition.name IN ('new', 'used', 'demo');
я всегда выполню только 2 апдейта в базу, независимо от количества строк обновляемых

В результате я всегда выполню только 2 апдейта в базу независимо от количества обновляемых строк