Select rows from database using varying multiple WHERE conditions

Question

I have a map with lots drawn on it, when a person select one or more lots I want to grab the information of those lots from the database and return it. The lots are identified by IDs like the ones in "lots_list". At the moment I'm using a for loop to iterate over the list and fetch the data, passing the ID to a where clause with a placeholder, but the execution is fairly slow this way.

def getLotInfo(lots_list):

    lots = []
    for lot in lots_list:
        try:
            connection = psycopg2.connect(user=user,
                                          password=password,
                                          host=host,
                                          port=port,
                                          database=database)
            cursor = connection.cursor()

            Psql_Query = '''SELECT setor, quadra, lote, area_ocupada FROM iptu_sql_completo WHERE sql 
                            LIKE %s'''

            cursor.execute(Psql_Query, (lot,))
            lots.append(cursor.fetchone())
            print(lots)

        except (Exception, psycopg2.Error) as error:
            print("Error fetching data from PostgreSQL table", error)

        finally:
            # closing database connection.
            if (connection):
                cursor.close()
                connection.close()
                print("PostgreSQL connection is closed")

    return lots


lots_list = ["0830480002", "0830480003", "0830480004"]

Lots = getLotInfo(lots_list)

I tried to use psycopg2 execute_batch command

        Psql_Query = '''SELECT setor, quadra, lote, area_ocupada FROM 
                        iptu_sql_completo WHERE sql LIKE %s'''

        ppgextra.execute_batch(cursor, Psql_Query, SQLs)

        lots.append(cursor.fetchall())
        print(lots)

but I get the following error "not all arguments converted during string formatting" I am imagining that's because I should use a placeholder in the query for every item in the list, but if the list is ever changing in size, would there be a way to fix this? The IDs are not always sequential.

My question is: Is there a way to achieve better performance than using the for loop?

You are opening a new connection for each lot, create the connection and cursor before the loop. It might be helpful to optimize your queries like this: stackoverflow.com/questions/2245536/… — Maurice Meyer
– Maurice Meyer, Commented Apr 27, 2020 at 19:08

Erwin Brandstetter · Accepted Answer · 2020-05-07 01:00:41Z

1

Your current code is pretty much the worst case I had in mind here:

Maurice already mentioned the repeated connection overhead. But even with a single connection, this is far from ideal. Instead, run a single query and pass the whole list lots_list as Postgres array:

SELECT setor, quadra, lote, area_ocupada
FROM   iptu_sql_completo
WHERE  sql = ANY (%s);

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Calil Over a year ago

thanks Erwin, is there a way to get feedback from database if some records passed in the array are not found?

Erwin Brandstetter Over a year ago

@HelderCalil: Yes. Unnest the array and use a LEFT JOIN. Example: stackoverflow.com/a/8118448/939860

Collectives™ on Stack Overflow

Select rows from database using varying multiple WHERE conditions

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related