Select within for

0

Next, my question is more about logic anyway ..
I have a python script in which I use psycopg2 to perform searches in the database. But I need to do this within a for. I have full notion that doing a search within a for can affect and much in code performance, besides getting something 'ugly'.
I already thought of other solutions like for example to bring a general query and work with 1 loop inside another to replace the select but I believe that it would give me a high processing the same way ....
When I am faced with a situation of which the best exit to avoid a select to each for loop?

for busca_filas in rows_sip:
            if busca_filas[1] == 'QUEUE':
                dict_filas.update({"fisico": busca_filas[2]})
                dict_filas.update({"virtual": busca_filas[4]})
                cursor.execute("SELECT * FROM queue_members where queue_name = '" + busca_filas[2] + "';")
                busca_ramais_filas = cursor.fetchall()
                for item_ramais_filas in busca_ramais_filas:
                    dict_detalhe_filas.update({'nome_fila': item_ramais_filas[0]})
                    dict_detalhe_filas.update({'ramal': item_ramais_filas[3]})
                    dict_detalhe_filas.update({'pausado': item_ramais_filas[4]})
                    list_detalhe_filas.append(dict_detalhe_filas)
                    dict_detalhe_filas = {}
                dict_filas.update({'ramais': list_detalhe_filas})
                list_filas.append(dict_filas)
                list_detalhe_filas = []
                dict_filas = {}

I created this sample code just for you to understand how my code is.

    
asked by anonymous 12.12.2017 / 12:32

1 answer

1

Do the query containing everything you need at once.

Select [coluna que tem o valor 'Queue'], 
       [Coluna que tem o valor de 'Fisico'], 
       [Coluna que tem o valor de 'Virtual'], 
       queue_name, 
       nome_fila, 
       ramal, 
       pausado
From [tabela princial] A
Left join queue_members B on B.queue_name = A.[coluna que tem o nome]
Where [coluna que tem o valor 'Queue'] = 'Queue'
Order by queue_name

After doing the query, do it on it.

//declare uma variavel para o nome. Ex.: _nome
for busca_filas in rows_sip:
            if _nome == "":
                _nome = busca_filas[3]                
            elif _nome != busca_filas[3]:
                _nome = busca_filas[3]
                dict_filas.update({'ramais': list_detalhe_filas})
                list_filas.append(dict_filas)
                list_detalhe_filas = []
                dict_filas = {}

            dict_filas.update({"fisico": busca_filas[1]})
            dict_filas.update({"virtual": busca_filas[2]})  
            dict_detalhe_filas.update({'nome_fila': item_ramais_filas[4]})
            dict_detalhe_filas.update({'ramal': item_ramais_filas[5]})
            dict_detalhe_filas.update({'pausado': item_ramais_filas[6]})
            list_detalhe_filas.append(dict_detalhe_filas)
            dict_detalhe_filas = {}

Please forgive me, I do not know python so I can not write the conditionals right.

If you can separate into methods, you can not repeat code where you fill in the Extensions.

I hope it helps.

    
12.12.2017 / 13:50