Django Database transaction rollback in Loop

User may import a excel and I want to check if the data are correct.

# Excel Data
| id | item | qty |
|:---- |:------:| -----:|
| 1  | item A    | 10 |
| 2  | item B    | 20 |
| 3  | item C    | 30 |
| 4  | item D    | 40 | <-- For example, Not enough qty to minus (only have 1)
| 5  | item E    | 50 |

# Database
| id | item | qty |
|:---- |:------:| -----:|
| 1  | item A    | 100 |
| 2  | item B    | 200 |
| 3  | item C    | 300 |
| 4  | item D    | 1 | <-- For example, Not enough qty to minus (Need 40)
| 5  | item E    | 500 |

I need to check the Database is that item has qty to minus, if yes then save the changes, if not, then rollback all changed data in this excel data (rollback to before import this excel) and return errors details to user.

def myFunction(self, request):
    try:
        error_details = []
        with transaction.atomic():
            for data in excal_data:
                result = checkIfVerify(data) # Here will be a function which will cause error 'You can't execute queries until the end of the 'atomic' block'
                if result is True:
                    serializer = modelSerailizer(data)
                    serializer.save()
                else:
                    error_details.append("some explanation...")
            if len(error_details) > 0:
                transaction.set_rollback(True)
                raise CustomError
    excpet CustomError:
        pass

    return Response(....)

# checkIfVerify(data)
def checkIfVerify(data):
    # this sql will need to join many tables which is hard to use ORM
    sql = ....
    results = []
    with connection.cursor() as cursor:
        cursor.execute(sql)
        results = cursor.fetchall()
        cursor.close()
        connection.close()
    if results .....:
        return True
    else:
        return False

But the problem seem to be i cannot able to use raw SQL execute inside the transaction.atomic() block, If I put transaction.atomic() inside the loop after the checking function, it not able to rollback all data. How should i do. Thanks

Back to Top