Reputation: 47296

How to update SQLAlchemy row entry?

Assume table has three columns: username, password and no_of_logins.

When user tries to login, it's checked for an entry with a query like

user = User.query.filter_by(username=form.username.data).first()

If password matches, he proceeds further. What I would like to do is count how many times the user logged in. Thus whenever he successfully logs in, I would like to increment the no_of_logins field and store it back to the user table. I'm not sure how to run update query with SqlAlchemy.

Upvotes: 270

Answers (7)

Nima Soroush

Reputation: 12834

There are several ways to UPDATE using SQLAlchemy:

user.no_of_logins += 1
session.commit()

session.query(User).\
    filter(User.username == form.username.data).\
    update({'no_of_logins': User.no_of_logins + 1})
session.commit()

conn = engine.connect()
stmt = User.update().\
    values(no_of_logins=User.no_of_logins + 1).\
    where(User.username == form.username.data)
conn.execute(stmt)

setattr(user, 'no_of_logins', user.no_of_logins + 1)
session.commit()

Upvotes: 567

DINA TAKLIT

Reputation: 8418

If you are using fastapi with sqlachemy you can update the row using SqlAlchecmy core like so (applicable of other python sqlalchmey code):

Update the complete record:

from sqlalchemy import update as sqlalchemy_update

 query = sqlalchemy_update(Evaluation).where(
            Evaluation.id == id).values(**evaluation.dict())
        await db.execute(query)
        await db.commit()

evaluation is the pydantic schema of the model Evaluation

PATCH (update a specific columns of the row): use an object the wrap the column you want to update with it is value example:

     query = sqlalchemy_update(DimensionsEvaluation).where(DimensionsEvaluation.evaluation_id == evluation_id).where(
                DimensionsEvaluation.dimension_id == id).values({
                    "selected": True
                })
            await db.execute(query)

Upvotes: 5

David Mendes

Reputation: 367

just because this is the first result that comes up on google, I wanted to share a more scalable way to update a row with SQLAlchemy. It's based on the previous answers and I currently use it with an interface, allowing me to do table.update(**kwargs) through all my CRUD tables.

class User(Base):

    __tablename__ = 'users'

    user_id = Column(Integer, primary_key=True)
    first_name = Column(String)
    last_name = Column(String)

    def __init__(self, first_name: str, last_name: str, user_id: int = None):
        self.firtst_name = first_name
        self.last_name = last_name
        self.user_id = user_id

    def update(self, first_name: str, last_name: str, user_id: int = None, **kwargs):
        self.firtst_name = first_name
        self.last_name = last_name
        self.user_id = user_id
        
    def __repr__(self):
        return self.firtst_name

the update method is built into the ORM Table declaration itself. That way a table knows how it's meant to update itself. This may come in useful for future UPSERT operations.

with new_session() as post_session:
    post_data = request.form.to_dict(flat=False)
    client = Client.query.filter_by(id=post_data["id"]).first()
    client.update(**post_data)
    post_session.add(client)

the actual updating then becomes very simple. Generalizing from here is also easy.

Upvotes: 2

MarredCheese

Reputation: 20891

Examples to clarify the important issue in accepted answer's comments

I didn't understand it until I played around with it myself, so I figured there would be others who were confused as well. Say you are working on the user whose id == 6 and whose no_of_logins == 30 when you start.

# 1 (bad)
user.no_of_logins += 1
# result: UPDATE user SET no_of_logins = 31 WHERE user.id = 6

# 2 (bad)
user.no_of_logins = user.no_of_logins + 1
# result: UPDATE user SET no_of_logins = 31 WHERE user.id = 6

# 3 (bad)
setattr(user, 'no_of_logins', user.no_of_logins + 1)
# result: UPDATE user SET no_of_logins = 31 WHERE user.id = 6

# 4 (ok)
user.no_of_logins = User.no_of_logins + 1
# result: UPDATE user SET no_of_logins = no_of_logins + 1 WHERE user.id = 6

# 5 (ok)
setattr(user, 'no_of_logins', User.no_of_logins + 1)
# result: UPDATE user SET no_of_logins = no_of_logins + 1 WHERE user.id = 6

The point

By referencing the class instead of the instance, you can get SQLAlchemy to be smarter about incrementing, getting it to happen on the database side instead of the Python side. Doing it within the database is better since it's less vulnerable to data corruption (e.g. two clients attempt to increment at the same time with a net result of only one increment instead of two). I assume it's possible to do the incrementing in Python if you set locks or bump up the isolation level, but why bother if you don't have to?

A caveat

If you are going to increment twice via code that produces SQL like SET no_of_logins = no_of_logins + 1, then you will need to commit or at least flush in between increments, or else you will only get one increment in total:

# 6 (bad)
user.no_of_logins = User.no_of_logins + 1
user.no_of_logins = User.no_of_logins + 1
session.commit()
# result: UPDATE user SET no_of_logins = no_of_logins + 1 WHERE user.id = 6

# 7 (ok)
user.no_of_logins = User.no_of_logins + 1
session.flush()
# result: UPDATE user SET no_of_logins = no_of_logins + 1 WHERE user.id = 6
user.no_of_logins = User.no_of_logins + 1
session.commit()
# result: UPDATE user SET no_of_logins = no_of_logins + 1 WHERE user.id = 6

Upvotes: 69

Andrew Lt

Reputation: 303

I wrote telegram bot, and have some problem with update rows. Use this example, if you have Model

def update_state(chat_id, state):
    try:
        value = Users.query.filter(Users.chat_id == str(chat_id)).first()
        value.state = str(state)
        db.session.flush()
        db.session.commit()
        #db.session.close()
    except:
        print('Error in def update_state')

Why use db.session.flush()? That's why >>> SQLAlchemy: What's the difference between flush() and commit()?

Upvotes: 8