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

engine = create_engine("mysql+pymysql://root:root@127.0.0.1/t1")

Base = declarative_base()

class Father(Base):
    __tablename__ = "father"

    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(40),unique=True)
    age = Column(Integer)

class Son(Base):
    __tablename__ = 'son'

    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(40),unique=True)
    age = Column(Integer)

    father_id = Column(Integer,ForeignKey('father.id'))

Base.metadata.create_all(engine)

MySession = sessionmaker(bind=engine)
session = MySession()

# f = Father(name='ld',age=21)
# session.add(f)
# session.commit()
# 注意这里需要先将father数据插入，然后son才会获得该正确的father_id号
#
# s1 = Son(name='ww',age=1,father_id=1)
# s2 = Son(name='wb',age=0,father_id=1)
#
# session.add_all([f,s1,s2])
# session.commit()
ret = session.query(Father.name,Son.id).join(Son).all()#列表,取出多个会是列表
for i in ret:
    print(i.name,i.id)

#别名使用label
ret = session.query(Father.name,Son.name.label('sname')).join(Son).first()#第一个元素
print(ret.name,ret.sname)