SQLとSQLAlchemyの対比メモ

gistfile1.md

create table shop
(
	shop_id int,
	shop_name varchar(100),
	shop_location varchar(20)
	primary key (
		shop_id
	)
)
insert into shop
values
(1, 'honten', 'tokyo'),
(2, 'siten1', 'osaka'),
(3, 'siten2', 'nagoya')


create table product
(
	product_id int,
	product_name varchar(100),
	product_price decimal(10,0)
	primary key (
		product_id
	)
)
insert into product
values
(1, 'burger', 300),
(2, 'potato', 250),
(3, 'nugget', 200)


create table sales
(
	sales_id int,
	sales_date datetime,
	shop_id int,
	product_id int,
	quantity int
	primary key (
		sales_id
	)
)
insert into sales
values
(1, '2020/5/1', 1, 1, 1),
(2, '2020/5/1', 1, 2, 1),
(3, '2020/5/1', 2, 1, 3),
(4, '2020/5/1', 3, 3, 2),
(5, '2020/5/2', 1, 3, 1),
(6, '2020/5/2', 2, 2, 4),
(7, '2020/5/2', 1, 1, 2),
(8, '2020/5/2', 3, 2, 2),
(9, '2020/5/3', 1, 2, 3),
(10, '2020/5/3', 3, 3, 1),
(11, '2020/5/3', 2, 1, 5),
(12, '2020/5/3', 1, 2, 3)

SQLAlchemyの場合

    q = (
        session.query(
            Sales.shop_id,
            Shop.shop_name,
            func.sum(Sales.quantity).label('total_quantity'),
            func.sum(Sales.quantity * Product.product_price).label('total_amount')
        )
            .join(
            Shop,
            Sales.shop_id == Shop.shop_id
        )
            .join(
            Product,
            Sales.product_id == Product.product_id
        )
            .filter(Sales.shop_id == 1)
            .group_by(Sales.shop_id, Shop.shop_name)
    )

SQLの場合

    SELECT
        Sales.shop_id,
        Shop.shop_name,
        sum(Sales.quantity) as [total_quantity],
        sum(Sales.quantity * Product.product_price) as [total_amount]
    FROM
        Sales
    JOIN
        Shop
    ON
        Sales.shop_id = Shop.shop_id
    JOIN
        Shop
    ON
        Sales.product_id = Product.product_id
    WHERE
        Sales.shop_id = 1
    GROUP BY
        Sales.shop_id,
        Shop.shop_name

列名定義はres.column_descriptionsで取得

[{'aliased': False,
  'entity': <class 'model.Sales'>,
  'expr': <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x00000***********>,
  'name': 'shop_id',
  'type': Numeric()},
 {'aliased': False,
  'entity': <class 'model.Shop'>,
  'expr': <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x00000***********>,
  'name': 'shop_name',
  'type': String()},
 {'aliased': False,
  'entity': <class 'model.Sales'>,
  'expr': <sqlalchemy.sql.elements.Label object at 0x00000***********>,
  'name': 'total quantity',
  'type': Numeric()},
 {'aliased': False,
  'entity': <class 'model.Sales'>,
  'expr': <sqlalchemy.sql.elements.Label object at 0x00000***********>,
  'name': 'total amount',
  'type': Numeric()}]