# -*- coding: UTF-8 -*-
# Author  :  LinYaoHong
# Date    :  2019/8/6 11:13
# connsqlalchemy.py
# Tools   :  PyCharm

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import and_, or_
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
Base = declarative_base()

engine = create_engine("mysql+pymysql://root:111111@127.0.0.1/testdb?charset=utf8", max_overflow=5)
Session = sessionmaker(bind=engine)
session = Session()

from connsqlalchemy import UserInfo,UserType

# 如果后面不加.all() 只有循环的时候才会一个个取出
user_list = session.query(UserInfo.name, UserType.user_type).join(UserType, isouter=True) #.all()
for row in user_list:
    print(row[0], row[1])

print('---' * 20)

for row in user_list:
    print(row.name, row.user_type)

print('---' * 20)
user_list = session.query(UserInfo)
for row in user_list: #userinfo里的字段名  # usertype里的 user_type
    print(row.name, row.user_type.user_type) # 加了 relationship

print('---' * 20)
user_type = session.query(UserType)
for row in user_type:
    print(row.user_type, session.query(UserInfo.name).filter(UserInfo.user_type_id == row.id).all())


user_type = session.query(UserType)
for row in user_type: # type_to_user 为自定义的 反向查询
    print(row.user_type, row.type_to_user)


session.commit()  # 提交
session.close()  # 关闭