Select within for

Asked

Viewed 121 times

1

Next, my doubt is more about logic anyway..
I have a python script in which I use psycopg2 to perform database searches. But I need to do this within a for.
I have full notion that doing a search within a for can affect and greatly in the performance of the code, besides getting something 'ugly''.
I’ve thought of other solutions such as bringing a general query and working with 1 loop within another to replace select but I think this would give me a high processing in the same way....
When I come across a situation like this the best way out 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 example code just so you understand how my code is.

python select logic
  • 2

    What you could do, is let SQL work in a relational way as it was optimized to do, that is, bring everything you need the query at once, ie, make a Join with this queue_members, and bring the columns you need, instead of using this asterisk, order the query so that the extensions are always below the right names, and do a single one, using conditional to know if it has been renamed. I think it gets more efficient.

  • Wow, something so basic.. kkkkkkkk. I get your idea and actually it’s to improve even more, to the point where I can get the select out of the for. I’m gonna redo this code and post it here to people who have a similar question

  • That’s right, that’s the idea, take a look at my answer :-)

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 what for 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 = {}

Forgive me, I don’t know python so I can’t write the conditionals right.

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

I hope it helps.

  • thanks for the @jsbueno fix :-)

  • gave a simplified in the code, but I think you understood the idea right?

  • 1

    I’ll mark how certain you have endeavored to help me and such. I understood your logicoa above and really the code works. But my problem is that in my code I have 50mil 'for' because I need to select within select to mount several JSON and this is giving a tool in the server processing and I wanted ideas to get out of these various loop repetition...

  • Anyway, studying des yesterday on the subject I came to the conclusion that I should review all the script I’ve assembled

  • Well, regardless of the size of the problem, try to keep that kind of thinking in mind, try to leave as many queries as possible together, to take advantage of the bank’s optimization. : -) good luck!

Browser other questions tagged python select logic

You are not signed in. Login or sign up in order to post.