How to update a table using SqlAlchemy with python

Question

Hi I am new to SQLAlchemy using python. I want to update a table called Cars and wanted some feedbacks if maybe there is another way of updating a table. I am not sure if execute() is good way of updating since it calls for a query. I have searched everywhere, but just trying a simple query and update.

Here is a graph I am following:

Here is my code:

from cars.models import Cars
from cars.models import CarType
from sqlalchemy.orm import session
from sqlalchemy.sql.expression import update
from sqlalchemy.sql.sqltypes import String, Text

# To update cars
def update_cars_models(session):
  db = session

  try:
    state = db.query(Cars).filter(Cars.state == "PRE_PROCESS")

    for row in state.all():
      if row.state == "PRE_PROCESS":

        # To add CarType object data
        car_update = CarType(1, "Subaru", "WHITE", "PRE_PROCESS")
        db.add(car_update)
        db.commit()

        # To update Cars object
        row.state = Cars.State.PROCESS #not sure if this how you use update
        db.commit()

  except SQLAlchemyError as error:
    print(f"Error querying: {error}")

  finally:
    db.close()

if __name__ == "__main__":

    # To update cars
    update_cars_models(session)

Hashir Irfan · Accepted Answer · 2021-11-30 06:38:29Z

1

You do not have to query all the rows and then edit each row in a loop. Your code depicts that you want to update those rows whose state is "PRE_PROCESS"

car_update = { state = "PROCESS" }
rows_updated = (db.session.query
        .filter(Car.state == "PRE_PROCESS")
        .update(car_update)
    )

If you want to update a row for a specific ID, you can write "Car.id == 1" in filter

answered Nov 30, 2021 at 6:38

Hashir Irfan

3341 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bebe Over a year ago

I attached a graph I am following, so this Car state should be updated to PROCESS after CarType state is on PRE_PROCESS, your block .update(car_update) will get trigger been out of the loop?

Hashir Irfan Over a year ago

You don't need to have loop. The .filter(Car.state == "PRE_PROCESS") will get all the rows whose state is "PRE_PROCESS" and update to "PROCESS"

Bebe Over a year ago

Ok, but CarType will get added into the database as soon as Cars is under PRE_PROCESS, so not been in the loop will still work?

Bebe Over a year ago

ah maybe I can do a join?

Collectives™ on Stack Overflow

How to update a table using SqlAlchemy with python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related