0

If I have a list of (sometimes) repeating ids and I want to query a table based on this list, is there a better way to do it, other than the following example:

def get_sensors_by_ids(self, ids: List[int]) -> List[SensorModel]:
    
    query = (
        "SELECT * FROM sensor "
        "WHERE id = %s"
    )
    sensor_models = []
    with self.connection.cursor(dictionary=True, buffered=True) as cursor:
        for id in ids:
            cursor.execute(query, (id,))
            result = cursor.fetchone()
            sensor_models.append(
                SensorDatabaseModel.parse_obj(result).to_sensor_model() 
                )
    return sensor_models

In the case above I want to return exactly one SensorModel instance per given ids item. ids could have the same value for every item, sometimes repeating values or all unique values, eg

ids = [1, 1, 1, 1, 1, 1, 1, 1, ... ]
ids = [1, 1, 1, 1, 2, 2, 2, 2, ... ]
ids = [1, 2, 3, 4, 5, 6, 7, 8, ... ]
ids = [1, 22, 313, 4, 65, 16, 347, 228, ... ]

The code above works and I don't expect huge amount of data in the tables, but I still don't like the solution above where all is done in separate queries.

Roland Deschain
  • 2,211
  • 19
  • 50
  • 1
    Two ids - xonverting the ids list to set and changing the query to query for all ids in one query (i.e where id in (....)) – Tom Ron Jun 27 '23 at 11:48
  • @TomRon I hope I follow corrrectly... Using `WHERE IN (...)` returns only one record per unique id. I would still have to go though my list of id and map the results accordingly. Might still be faster, not sure – Roland Deschain Jun 27 '23 at 12:00
  • Correct, even if your list has duplicates, MySQL matches only once per distinct value. This is one way to implement a [semi-join](https://www.jooq.org/doc/latest/manual/sql-building/table-expressions/joined-tables/join-type-semi/), for example. – Bill Karwin Jun 27 '23 at 14:21
  • You will have to write it like `WHERE id IN (%s, %s, %s, %s, ...)` and make sure there are the same number of `%s` placeholders as the elements in the list. You cannot bind a list to a single placeholders, because each placeholder is for one scalar value. I wrote a Python example here: https://stackoverflow.com/questions/71620900/how-to-pass-tuple-with-one-element-as-param-in-sql-query-python/71621827#71621827 – Bill Karwin Jun 27 '23 at 14:22
  • But you can convert the list in advance, i.e. - `'('+' ,' .join([str(x) for x in ids]) +')'` and just replace with this result – Tom Ron Jun 27 '23 at 15:34
  • Hi, thanks everyone and sorry for the late reply. I had @BillKarwin solution before, as mentioned with this I still have to map the ids to the records in code since I get only unique records back from my query. – Roland Deschain Jun 27 '23 at 15:38
  • For context: the whole problem comes from using one data model per table, meaning for all foreign keys I have to query those tables separately with the ids I got from the parent table and then compile the full models with correct properties from those multiple queries. This makes for simpler query statements and filters, but the multiple queries give me a bit of a headache tbh. – Roland Deschain Jun 27 '23 at 15:42

0 Answers0