Home > Mobile >  Update a value based on Join FlaskSqlALchemy
Update a value based on Join FlaskSqlALchemy

Time:08-13

I have 2 tables in db.

class Vehicle(db.Model):
    __tablename__ = "vehicles"

    id = db.Column(db.Integer, primary_key=True, nullable=False)
    num_plate = db.Column(db.String(7), nullable=False, unique=True)
    type = db.Column(db.String, nullable=False)
    suspicious = db.Column(db.Boolean, nullable=False, default=False)

    def __repr__(self) -> str:
        return f"Vehicle(number playe={self.num_plate}, type={self.type}, suspicious={self.suspicious})"

class Registered(db.Model):
    """
    Registered User
    """

    __tablename__ = "registered"

    regid = db.Column(db.Integer, primary_key=True, nullable=False)
    name = db.Column(db.String, nullable=False)
    cnic = db.Column(db.String, nullable=False)
    contactno = db.Column(db.String, nullable=False)
    gender = db.Column(db.String, nullable=False)
    dor = db.Column(db.DateTime, nullable=False)
    doe = db.Column(db.DateTime, nullable=False)
    vehicle_id = db.Column(
        db.Integer, db.ForeignKey("vehicles.id"), unique=True, nullable=False
    )

Now I want to update a specific person's car num_plate. Let's say person with regid 3.

I can get his data.

reg_visitor = (
    Registered.query.filter_by(regid=3)
    .join(Vehicle)
    .add_columns(
        Registered.name,
        Registered.cnic,
        Registered.contactno,
        Registered.gender,
        Registered.dor,
        Registered.doe,
        Vehicle.num_plate,
        Vehicle.suspicious,
    )
).first()

It is of type

<class 'sqlalchemy.engine.row.Row'>

If I print it's attribute, it gives correct answer.

print(reg_visitor.num_plate)

output is

ANB 127

Now If I want to update this attribute, I can not.

reg_visitor.num_plate = "ABC1234"

ERROR:

    reg_visitor.num_plate = "ABC1234"
  File "/home/ahmad/Desktop/FYP/venv/lib/python3.7/site-packages/sqlalchemy/engine/row.py", line 219, in __setattr__
    raise AttributeError("can't set attribute")
AttributeError: can't set attribute

My Flask-SQL Alchemy version is '2.5.1', and SQLAlchemy version is 1.4.29.

I do not want to mess up with my versions, is there any other way to update the attribute based on join?

It works well on single tables though.

vehicle = Vehicle.query.filter_by(num_plate="ANB 127").first()
vehicle.num_plate = "ABC123"
db.session.commit()
vehicle = Vehicle.query.filter_by(num_plate="ABC123").first() # works well
print(vehicle)

CodePudding user response:

As you pointed out, the result of your query reg_visitor is a sqlalchemy.engine.row.Row, meaning it does not map to a single class.

When you try to update an attribute in it, SQLAlchemy doesn't know how it could update the tables based on the query.

You need to query the Vehicle directly (like again you've shown) to be able to update.

  • Related