I've been working in a web app for a while and this is the first time I realize this problem, I think it could be related with how SQLAlchemy sessions are handled, so some clarification in simple term would be helpful. My configuration for work with flask sqlAlchemy is:
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy(app)
My problem: db.session.commit() sometimes doesn't save changes. I wrote some flask endpoints which are reached via the front end requests in the user browser. In this particular case, I'm editing a hotel "Booking" object altering the "Rooms" columns which is a Text field.
the function does the following:
1-Query the Booking object from the dates in the request
2- Edit the Rooms column of this Booking object
3- Commit the changes "db.session.commit()"
4- If a user has X functionality active, I make some checks calling a second function:
·4.1- This functions make some checks and query and edit another object in the database different from the "Booking" object I edited previously.
·4.2- At the end of this secondary function I call db.session.commit() "Note this changes always got saved correctly in the database"
·4.3- Return the results to the previous function
5- Return results to the front end ("just before this return, I print the Booking.Rooms to make sure it looks as it should, and it does... I even tried to make a second commit after the print but before the return... But after this, sometimes Booking.Rooms are updated as expected but some other times it doesn't... I noted if repeat the action many times it finally works, but given the intermediate function "described in point 4" saves all his changes correctly, this causes an inconsistency in the data and drives me mad because if I repeat the action and procedure in the function of point 4 worked correctly, I can't repeat the mod Rooms action...
So, I'm now really confused if this is something I don't understand from flask sessions, for what I understand, whenever I make a new request to flask, it's an isolated session, right? I mean, if 2 concurrent users are storing some changes in the database, a db.session.commit() from one of the users won't commit the changes from the other one, right?
Same way, if I call db.session.commit() in one request, that changes are stored in the database, and if after that "in the same request", I keep modding things, it's like another session, right? And the committed changes are there already safely stored? And I can still use previous objects for further modifications
Anyway, all of this shouldn't be a problem because after the commit() I print the Booking.Rooms and looks as expected... And some times it works getting stored correctly and some times it doesn't...
Also note: When I return this result to the client, the client makes instantly a second request to the server to request updated Booking data, and then the data is returned without this expected changes committed... I suppose flask handled all the commit() before it gets the second request "other way it wouldn't have returned the result previously..."
Can this be a limitation of the flask development server which can't handle correctly many requests and that when deployed with gunicorn it doesn't happen?
Any hint or clarification about Sessions would be nice, because this is pretty strange behaviour, especially that sometimes works and others don't...
And as requested here is the code, I know is not possible to reproduce, have a lot of setup behind and would need a lot of data to works as intended under same circumstances as in my case, but this should provide an overview of how the functions looks like and where are the commits I mention above. Any ideas of where can be the problem is very helpful.
#Main function hit by the frontend
@users.route('/url_endpoint1', methods=['POST'], strict_slashes=False)
@login_required
def add_room_to_booking_Api():
try:
bookingData = request.get_json()
roomURL=bookingData["roomSafeURL"]
targetBooking = bookingData["bookingURL"]
startDate = bookingData["checkInDate"]
endDate = bookingData["checkOutDate"]
roomPrices=bookingData["roomPrices"]
booking = Bookings.query.filter_by(SafeURL=targetBooking).first()
alojamiento = Alojamientos.query.filter_by(id=reserva.CodigoAlojamiento).first() #owner of the booking
room=Rooms.query.filter_by(SafeURL=roomURL).first()
roomsInBooking=ast.literal_eval(reserva.Habitaciones) #I know, should be json.loads() and json.dumps() for better performance probably...
#if room is available for given days add it to the booking
if CheckIfRoomIsAvailableForBooking(alojamiento.id, room, startDate, endDate, booking) == "OK":
roomsInBooking.append({"id": room.id, "Prices": roomPrices, "Guests":[]}) #add the new room the Rooms column of the booking
booking.Habitaciones = str(roomsInBooking)#save the new rooms data
print(booking.Habitaciones) # check changes applied
room.ReservaAsociada = booking.id # associate booking and room
for ocupante in room.Ocupantes: #associate people in the room with the booking
ocupante.Reserva = reserva.id
#db.session.refresh(reserva) # test I made to check if something changes but didn't worked
if some_X_function() == True: #if user have some functionality enabled
#db.session.begin() #another test which didn't worked
RType = WuBook_Rooms.query.filter_by(ParentType=room.Tipo).first()
RType=[RType] #convert to list because I resuse the function in cases with multiple types
resultAdd = function4(RType, booking.Entrada.replace(hour=0, minute=0, second=0), booking.Salida.replace(hour=0, minute=0, second=0))
if resultAdd["resultado"] == True: # "resultado":error, "casos":casos
return (jsonify({"resultado": "Error", "mensaje": resultAdd["casos"]}))
print(booking.Habitaciones) #here I still get expected result
db.session.commit()
#I get this return of averything correct in my frontend but not really stored in the database
return jsonify({"resultado": "Ok", "mensaje": "Room " + str(room.Identificador) + " added to the booking"})
else:
return (jsonify({"resultado": "Error", "mensaje": "Room " + str(room.Identificador) + " not available to book in target dates"}))
except Exception as e:
#some error handling which is not getting hit now
db.session.rollback()
print(e, ": en linea", lineno())
excepcion = str((''.join(traceback.TracebackException.from_exception(e).format()).replace("\n","</br>"), "</br>Excepcion emitida ne la línea: ", lineno()))
sendExceptionEmail(excepcion, current_user)
return (jsonify({"resultado":"Error","mensaje":"Error"}))
#function from point 4
def function4(RType, startDate, endDate):
delta = endDate - startDate
print(startDate, endDate)
print(delta)
for ind_type in RType:
calendarUpdated=json.loads(ind_type.updated_availability_by_date)
calendarUpdatedBackup=calendarUpdated
casos={}
actualizar=False
error=False
for i in range(delta.days):
day = (startDate + timedelta(days=i))
print(day, i)
diaString=day.strftime("%d-%m-%Y")
if day>=datetime.now() or diaString==datetime.now().strftime("%d-%m-%Y"): #only care about present and future dates
disponibilidadLocal=calendarUpdated[diaString]["local"]
yaReservadas=calendarUpdated[diaString]["local_booked"]
disponiblesChannel=calendarUpdated[diaString]["avail"]
#adjust availability data
if somecondition==True:
actualizar=True
casos.update({diaString:"Happened X"})
else:
actualizar=False
casos.update({diaString:"Happened Y"})
error="Error"
if actualizar==True: #this part of the code is hit normally and changes stored correctly
ind_type.updated_availability_by_date=json.dumps(calendarUpdated)
wubookproperty=WuBook_Properties.query.filter_by(id=ind_type.PropertyCode).first()
wubookproperty.SyncPending=True
ind_type.AvailUpdatePending=True
elif actualizar==False: #some error occured, revert changes
ind_type.updated_availability_by_date = json.dumps(calendarUpdatedBackup)
db.session.commit()#this commit persists
return({"resultado":error, "casos":casos}) #return to main function with all this chnages stored
