from datetime import datetime
from sqlalchemy import create_engine, Column, Integer, ForeignKey, Numeric, DateTime, func
from sqlalchemy.orm import Session, relationship
from sqlalchemy.ext.declarative import declarative_base
# 创建引擎和会话
engine = create_engine('sqlite://', echo=True)
session = Session(bind=engine)
# 定义表结构
Base = declarative_base(bind=engine)
class CostCenter(Base):
__tablename__ = 'cost_center'
id = Column(Integer, primary_key=True)
class Expense(Base):
__tablename__ = 'expense'
id = Column(Integer, primary_key=True)
cost_center_id = Column(Integer, ForeignKey(CostCenter.id), nullable=False)
value = Column(Numeric(8, 2), nullable=False, default=0)
date = Column(DateTime, nullable=False)
cost_center = relationship(CostCenter, backref='expenses')
# 创建表
Base.metadata.create_all()
# 添加数据
session.add_all([
CostCenter(expenses=[
Expense(value=10, date=datetime(2014, 8, 1)),
Expense(value=20, date=datetime(2014, 8, 1)),
Expense(value=15, date=datetime(2014, 9, 1)),
]),
CostCenter(expenses=[
Expense(value=45, date=datetime(2014, 8, 1)),
Expense(value=40, date=datetime(2014, 9, 1)),
Expense(value=40, date=datetime(2014, 9, 1)),
]),
CostCenter(expenses=[
Expense(value=42, date=datetime(2014, 7, 1)),
]),
])
# 执行查询
base_query = session.query(
Expense.date,
func.sum(Expense.value).label('total')
).join(Expense.cost_center
).group_by(Expense.date)
# 第一次查询,考虑成本中心 1
for row in base_query.filter(CostCenter.id.in_([1])).all():
print('{}: {}'.format(row.date.date(), row.total))
# 第二次查询,考虑成本中心 1、2 和 3
for row in base_query.filter(CostCenter.id.in_([1, 2, 3])).all():
print('{}: {}'.format(row.date.date(), row.total))
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.