CODEWITHSUNDEEP
pythonpython-3.xsqlite
Angom
Angom

Reputation: 763

How to write sqlite query to avoid loop in python for one-to-many database model?

I have two object called RECEIPT and PARTICULAR.

Attributes of RECEIPT object are:

Attributes of PARTICULAR object are:

Also I have their respective tables. receiptNo is the primary key of RECEIPT table and it is the foreign key in PARTICULAR table. So for a receipt there are multiple particulars.

I want to fetch data to populate RECEIPT object. To achieve this I can first run select query to RECEIPT table and by iterating the result using a for loop I can run another query to fetch the PARTICULAR table. Here I am calling the DB twice.

To avoid calling DB twice I tried joins also as:

SELECT * FROM RECEIPT r,PARTICULAR p WHERE r.RECEIPT_NO = p.RECEIPT_NO

However as it returns repetitive data for the RECEIPT, i.e. for each PARTICULAR row corresponding RECEIPT data are also fectching. This RECEIPT data are repetitive as multiple particularId shares same receiptNo. Due to thisI am unable to load the data properly to the RECEIPT object (Or may be I dont know how to load such resulset to the respective objects)

My actual requirement is to load RECEIPT object by forming PARTICULAR list for each receipt.

Is using the for loop and calling DB twice the only way to achieve it? Suggest me an efficient way to achieve this

Upvotes: 2

Views: 216

Answers (1)

Myrtue
Myrtue

Reputation: 316

I think querying the data from the database with the JOIN approach is the most efficient way to do it.

If you make sure to ORDER BY "RECEIPT_NO" you just have to loop through the list once in python, only creating a new Receipt object every time you reach a new "RECEIPT_NO".

So the SQL becomes:

SELECT * FROM RECEIPT r,PARTICULAR p WHERE r.RECEIPT_NO = p.RECEIPT_NO ORDER BY RECEIPT_NO

And the python code could look like

data = query("SELECT * FROM RECEIPT r,PARTICULAR p WHERE r.RECEIPT_NO = p.RECEIPT_NO ORDER BY RECEIPT_NO")

last_receipt_nr = ""
for row in data:
    if last_receipt_nr == row.RECEIPT_NO:
        # Replace with code initializing a new Receipt object
        last_receipt_nr = row.RECEIPT_NO
    #Replace with code initializing a new Particular object

Upvotes: 1

Related Questions