0

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?

sharkyenergy
  • 3,842
  • 10
  • 46
  • 97
  • As an option, make three queries for each name and get three objects at once. https://stackoverflow.com/questions/37558417/how-to-execute-many-select-statements-at-once-using-python-sqlite – Сергей Кох Mar 30 '23 at 07:59
  • @СергейКох problem is the queries are not three.. they can be any nuber based on the names, that are not defined. – sharkyenergy Mar 30 '23 at 08:07

1 Answers1

2

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.

Read more read dict here: 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
Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
Levin
  • 36
  • 4
  • uhm, little extra question: is there a way to have an entry with "mark=0" for the day 2023-01-02? – sharkyenergy Mar 30 '23 at 08:11
  • Please correct the indents in your answer. – Сергей Кох Mar 30 '23 at 08:15
  • Sure Sergie. @sharkyenergy you need to add extra if the condition to a date column. if matched add an item in row, row['mark']=0 – Levin Mar 30 '23 at 08:24
  • not sure I understand what you mean. i guess you need to have the list of all possible names first hand, else you cannot know what rows to add the first day.. – sharkyenergy Mar 30 '23 at 08:31
  • @sharkyenergy please elaborate on what you mean by mark=0. this mark is a entry of name field or a custom field which you want to add in result objects? – Levin Mar 30 '23 at 08:36
  • as you see in the result of the query, mark did not sell on january 2nd, thus there is no entry for him. I would need all new objects, to have the same dates, and if there has been no selling that day, then the column 3 should be 0. so that in the end, all 3 objects have the same number of rows, with the same days in it. – sharkyenergy Mar 30 '23 at 10:33
  • @sharkyenergy got it. sadly in the program "for row in result:" part the line only iterates over the resultset returned by SQL query. You will need more custom code for such result. – Levin Mar 30 '23 at 11:38