# Sqlalchemy 常用寫法

## 只取得一個結果

### 強制查詢結果只有一個,如果有兩個就會跳錯

session.query(Schema).filter(some where).one()

### 有多個只拿第一個

session.query(Schema).filter(some where).first()

## where .. and .. or

### 基本 where

profiles = session.query(profile.name).filter(profile.email == email).all()

### and where

profiles = session.query(profile.name).filter(and_(profile.email == email, profile.password == password_hash))

### or where

profiles = session.query(profile.name).filter(or_(profile.email == email, profile.password == password_hash))

## 取得Entity Class 的 PK(主鍵) 欄位

### 方法一 : 探詢每個 columns

def get_entity_pk_key(entity_clazz: type) -> InstrumentedAttribute:
    column_names = entity_clazz.__table__.columns
    for c in column_names:
        if c.primary_key:
            return getattr(entity_clazz, c.name)
    raise KeyError('not find pk')

> 如果是複合鍵,就有可能查到兩個

### 方法二 : 透過table info 取得

## query 後的 result 轉 dict

row = session.query(User.id, User.username, User.email)\
    .filter(and_(User.id == id, User.username == username)).first()

print(dict(zip(row.keys(), row))) # as a dict

## 排序

        routing_list: List[ShipmentRouting] = self.session.query(ShipmentRouting).filter(
            ShipmentRouting.shipment_number.in_(self.shipment_number_list)).order_by(
            asc(ShipmentRouting.routing_sequence))

###### tags: `python`