0

I'm looking for the "standard" way of modifying a single row from a given table. (I want to modify several values within the row.)

I've found many ways and I struggle understanding the impact of this choice. This answer is an example of what I'm not looking for but also an example of the many ways of doing the same thing.

SQLAlchemy seems to be a well though tool so I guess there aren't several way just as a design consequence, there must be a benefit/cost for every solution. I'm looking for this information but I can't find it anywhere.

For the sake of the example, consider I want to update the profile of a given user with ID user_id. I can for example write the two following statements that (in appearance) the same effect:

profile = {'display_name': 'test', 'age': 34} 

# 1)
user_profile = query.get(user_id)
for key, value in profile.items():
    setattr(user_profile, key, value)
database.session.add(user_profile)

# 2)
user_profile = query.filter_by(id=user_id)
user_profile.update({**profile})

# 3)
with database.engine.connect() as conn:
    stmt = (
        sqlalchemy.update(models.UserProfile)
                  .values(**profile)
                  .where(models.UserProfile.id == db_user_id)
    )
    conn.execute(stmt)

In general what are the performances impact of all the different ways of updating a single row using SQLAlchemy?

cglacet
  • 8,873
  • 4
  • 45
  • 60
  • If considering only DB performance, 1) and 2) are identical, only resulting in an actual DB transaction when you run `session.commit()`. 3) is where the DB performance of SQLA might be under consideration, since it deals with the difference beween the ORM abstraction and raw-SQL operation. At that stage, choice of SQL provider, engine, and operating platform become somewhat relevant. – CodeMantle Jan 31 '21 at 14:16
  • If you're curious you can use `create_engine(…, echo=True)` to see logging output of the statements that SQLAlchemy is generating. – Gord Thompson Jan 31 '21 at 16:44

0 Answers0