SQLAlchemy SELECT sentence

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


# ベースモデルを作成
Base = declarative_base()


# クラス定義
class Item(Base):
    __tablename__ = 'items'

    id = Column(Integer, primary_key=True)
    itemcode = Column(String, unique=True)
    name = Column(String)
    price = Column(Integer)
    cost_price = Column(Integer)
    maker_id = Column(Integer)


# エンジンを作成
engine = create_engine('sqlite:///db/sql_training.sqlite')

# DBと通信するセッションオブジェクトの作成
# sqlite3におけるconnectionオブジェクトに近い
Session = sessionmaker(bind=engine)
session = Session()

# SELECT count(id) FROM items
# scalar関数は、取得した結果の最初の要素かNoneを返す
cnt = session.query(func.count(Item.id)).scalar()
print(cnt)

# SELECT maker_id, count(id) AS cnt FROM items GROUP BY maker_id
groups = session.query(
    Item.maker_id,
    func.count(Item.id).label('cnt')
).group_by(Item.maker_id).all()

for v in groups:
    print(v.maker_id, v.cnt)

# HAVING
groups = session.query(
    Item.maker_id,
    func.count(Item.id).label('cnt')).group_by(Item.maker_id).having(
    func.count(Item.id) >= 150).all()

for v in groups:
    print(v.maker_id, v.cnt)

# SUM, MIN, MAX
result = session.query(
    func.count(Item.id).label('cnt'),
    func.sum(Item.price).label('p_total'),
    func.max(Item.price).label('p_max'),
    func.min(Item.price).label('p_min')
).filter(Item.maker_id == 1).first()

print(result)

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


# ベースモデルを作成
Base = declarative_base()


# クラス定義
class Customer(Base):
    __tablename__ = 'customers'

    id = Column(Integer, primary_key=True)
    code = Column(String(10), nullable=False)
    name = Column(String(40), nullable=False)
    zipcode = Column(String(10), nullable=False)
    address = Column(String(40), nullable=False)
    tel = Column(String(15), nullable=False)
    fax = Column(String(15), nullable=False)
    rank = Column(String(10), nullable=False)
    user_id = Column(Integer, nullable=False)
    seikyu_id = Column(Integer, nullable=False)


class Earning(Base):
    __tablename__ = 'earnings'

    id = Column(Integer, primary_key=True)
    invoice_no = Column(String(15), nullable=False)
    billdate = Column(Date, nullable=False, index=True)
    ordersum = Column(Integer, nullable=False)
    tax = Column(Integer, nullable=False)
    total = Column(Integer, nullable=False)

    customer_id = Column(Integer, nullable=False)


# エンジンを作成
engine = create_engine('sqlite:///db/sql_training.sqlite')

# DBと通信するセッションオブジェクトの作成
# sqlite3におけるconnectionオブジェクトに近い
Session = sessionmaker(bind=engine)
session = Session()

# JOIN
result = session.query(Earning.id,
                       Earning.total,
                       Customer.name).join(Customer,
                                           Earning.customer_id == Customer.id).limit(5)
for row in result:
    print(row.id, row.total, row.name)