# -*- coding: utf-8 -*-

from sqlalchemy import Column, String, create_engine,ForeignKey,Text,INTEGER

from sqlalchemy.orm import sessionmaker,relationship

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

engine = create_engine('mysql+pymysql://root:root@localhost:3306/test')

DBSession = sessionmaker(bind=engine)

class UserSheet(Base):

    __tablename__='user'

    id=Column(INTEGER,autoincrement=True,primary_key=True)

    name=Column(String(32),unique=True)

    books=relationship('BookSheet')

    detail=relationship('UserInfo',uselist=False)

class BookSheet(Base):

    __tablename__='book'

    id = Column(INTEGER, autoincrement=True, primary_key=True)

    title=Column(String(32))

    author=Column(INTEGER,ForeignKey('user.id'))

    # publisher=Column(String(32),ForeignKey('publisher.id'))

    def __str__(self):

        return self.title

class UserInfo(Base):

    __tablename__ = 'userinfo'

    id = Column(INTEGER, autoincrement=True, primary_key=True)

    name=Column(INTEGER,ForeignKey('user.id'))

    addr=Column(String(32))

#Base.metadata.create_all(engine)

Session=DBSession()

# data1=UserSheet(name='Edmond')

# Session.add(data1)

# Session.commit()

a=Session.query(UserSheet).filter(UserSheet.name=='Edmond').first()

print(a.detail.addr,'________________')

# data2=BookSheet(title='test1',author=Session.query(UserSheet.id).filter(UserSheet.name=='Edmond').first()[0])

#

# Session.add(data2)

# Session.commit()

print(a.detail.addr) 要加 print(a[0].detail.addr , 因为可能有多个结果,

#多表查询

print( session.query(UserDetails,User).all() )  #这个是 cross join

print( session.query(UserDetails,User).filter(User.id==UserDetails.id).all() )  #这是也是cross join 但是加上了where条件

print( session.query(User.username,UserDetails.lost_login).join(UserDetails,UserDetails.id==User.id).all() )  #这个是inner join

print( session.query(User.username,UserDetails.lost_login).outerjoin(UserDetails,UserDetails.id==User.id).all() )  #这个才是左连接，sqlalchemy没有右连接

q1 = session.query(User.id)

q2 = session.query(UserDetails.id)

print(q1.union(q2).all())  #这个是union关联

除了上面的几种关联方式，子表查询也是用得很多的，也是要掌握的

from sqlalchemy import all_,any_

sql_0 = session.query(UserDetails.lost_login).subquery()  #这是声明一个子表

print( session.query(User).filter((User.creatime > all_(sql_0)) ).all()  )

print( session.query(User).filter((User.creatime > any_(sql_0)) ).all()  )