111.SQLAlchemy中别名的使用

from random import randint, choice
from unicodedata import name
from sqlalchemy import Column, Integer, String, func, and_

from util_db import Base, Session

class City(Base):
    __tablename__ = 't_city'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32))
    pid = Column(Integer)

    def __repr__(self) -> str:
        return f'<City: id={self.id} name={self.name} pid={self.pid}>'

def create_data():
    Base.metadata.drop_all()
    Base.metadata.create_all()
    city1 = City(id=1, name="河北省", pid = 0)
    city2 = City(id=10, name="石家庄", pid = 1)
    city3 = City(id=11, name="天津市", pid = 1)
    city4 = City(id=2, name="广东省", pid = 0)
    city5 = City(id=21, name="深圳市", pid = 2)
    city6 = City(id=22, name="广州市", pid = 2)
    with Session() as ses:
        ses.add(city1)
        ses.add(city2)
        ses.add(city3)
        ses.add(city4)
        ses.add(city5)
        ses.add(city6)
        ses.commit()

# 查询每个省下有什么城市，pid的定义与所在城市id相同
def query_data():
    from sqlalchemy.orm import aliased
    # 给City取别名为c
    c= aliased(City)
    with Session() as ses:
        rs = ses.query(City,c.name,c.id).join(c,c.pid == City.id).all()
        for i in rs:
            print(i)


if __name__ == "__main__":
    create_data()
    query_data()