python – split query results into multiple objects based on one column

my program is querying a sqlite database, and thre result is like this (simplified) in the cursor ready to be fetched.

connection = sqlite3.connect(IMAGE_LOG_DB_PATH)
connection.isolation_level = None
cur = connection.cursor()
sql_query = "Select date, name, count(*) as sells from sellers group by date, name order by date asc;"
cur.execute(sql_query)
result = cur.fetchall()

2023-01-01 | John | 5
2023-01-01 | Mark | 10
2023-01-01 | Alex | 7
2023-01-02 | John | 4
2023-01-02 | Alex | 3
2023-01-03 | John | 3
2023-01-03 | Mark | 4
2023-01-03 | Alex | 3

I would need to split this into separate objects for each Name.

Object 'John':
2023-01-01 | John | 5
2023-01-02 | John | 4
2023-01-03 | John | 3

Object 'Mark':
2023-01-01 | Mark | 10
2023-01-03 | Mark | 4

Object 'Alex':
2023-01-01 | Alex | 7
2023-01-02 | Alex | 3
2023-01-03 | Alex | 3

it would be easy to do with a loop, and if the object exits, add the entry, if not create a new object. but what I have learned so far is that in Python for almost everything there is a handy tool that does things automatically and usually much faster than what my code can do. I have been reading into ORM, but its my understanding (correct me if I am wrong) that ORM replaces also the connection/query to the database and handles everything on its own. And it appears to be slower than the approach i am having right now.

What would be a proper way to do this?

>Solution :

The above result of splitting objects upon the name field is definitely possible.

We have a data structure like dict in python. which acts like an associative array. which internally makes sure that keys will be unique. this serves our purpose to save rows on basis of name.

More read about dict:

https://docs.python.org/3/tutorial/datastructures.html#dictionaries

Your improved code in this scenario will be below:

connection = sqlite3.connect(IMAGE_LOG_DB_PATH)
connection.isolation_level = None
cur = connection.cursor()
sql_query = "Select date, name, count(*) as sells from sellers group by date, name order by date asc;"
cur.execute(sql_query)
result = cur.fetchall()

assoc_dict = {}

for row in result:
name = row[1]  # over here we have a field of name
if name in assoc_dict:
   assoc_dict[name].append(row)
else:
   assoc_dict[name] = [row]

#logic goes like if name is already present in our assoc_dict then append more value or else create new list with name as the key

#to access these 3 objects use code below:
john = assoc_dict['John']
mark = assoc_dict['Mark']
alex = assoc_dict['Alex']
... so on

Hope this helps. Thanks.

Leave a Reply