SQLAlchemy - Update(中文)

Update

更新單個記錄

幫 LBJ 換個背號吧！

user = session.query(NBA_Player).filter_by(name='LBJ').first()
if user:
    user.number = 6 # 更新 number
    session.commit()  # 提交更新

批量更新記錄

把大家的背號，都換成 Kobe 的背號吧(We miss you, Kobe.)

session.query(NBA_Player).filter(NBA_Player.number < 24).update({NBA_Player.number: 24})
session.commit()  # 提交更新

完整 code 如下

from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

engine = create_engine('postgresql://myuser:mypassword@172.17.0.3:5432/mydatabase', echo=True)

Base = declarative_base()

# 定義 Model
class NBA_Player(Base):
    __tablename__ = 'nba_player'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String)
    number = Column(Integer)

    def __repr__(self):
        return f"<NBA_Player(name='{self.name}', number='{self.number}', id='{self.id}')>"

Base.metadata.create_all(engine)

# 創建會話類
Session = sessionmaker(bind=engine)

# 創建會話對象
session = Session()

# 查詢所有用戶
players = session.query(NBA_Player).all()
for p in players:
    print("player : ",p)


# 更新單個記錄
user = session.query(NBA_Player).filter_by(name='LBJ').first()
if user:
    user.number = 6 # 更新 number
    session.commit()  # 提交更新

players = session.query(NBA_Player).all()
for p in players:
    print("player : ",p)


# 批量更新記錄
# 將所有背號小於 24 的 player 的背號設置為 24
session.query(NBA_Player).filter(NBA_Player.number < 24).update({NBA_Player.number: 24})
session.commit()  # 提交更新


players = session.query(NBA_Player).all()
for p in players:
    print("player : ",p)