# coding=utf-8

import datetime
from sqlalchemy import Column, String, create_engine, Integer, ForeignKey, Table,text, select, update,func
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.sqltypes import INT, DateTime
from sqlalchemy.sql.selectable import Select

# 创建对象的基类:
Base = declarative_base()

user_role = Table(
't_user_role',#第三张表名
Base.metadata, #元类的数据
Column('id',INT, primary_key=True),
Column('user_id',Integer,ForeignKey("t_user.id")),
Column('role_id',Integer,ForeignKey("t_role.id")),
Column('create_id',INT ),
Column('create_time',DateTime,default=datetime.datetime.now()),
Column('modify_id',INT),
Column('modify_time',DateTime),
)

class Role(Base):
# 表的名字:
__tablename__ = 't_role'

# 表的结构:
id = Column(INT, primary_key=True)
role_name = Column(String(20))

# 定义User对象:
class User(Base):
# 表的名字:
__tablename__ = 't_user'

# 表的结构:
id = Column(INT, primary_key=True)
user_name = Column(String(20))
roles = relationship('Role',secondary=user_role)

# 初始化数据库连接:
engine = create_engine('mysql+mysqlconnector://root:123456@localhost:3306/sky_report?charset=utf8',echo=True)
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)

session = DBSession()
#new_user = User(user_name='allen')
#new_user.user_name = 'allen_2'
#session.add(new_user)
#session.commit()

# 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
user = session.query(User).filter(User.id==1).one()
role = session.query(Role).filter(Role.id==8).one()

# 打印类型和对象的name属性:
print('type:', type(user))
print('name:', user.user_name)
print('id:', user.id)
#user.roles.append(role)
i = 0
for a in user.roles:
print(a.role_name)
if (i==0):
pass
# del user.roles[i]
i=i+1

session.commit()

for o in session.query(User).all():
print(o.user_name)

reports = session.query(
(func.sum(User.id)).label('c'),
User.id,
).group_by(User.id).subquery();

q = session.query(
(func.max(reports.c.c)).label('x'),
reports.c.id
).group_by(reports.c.id)

for item in q:
print (item.id)

o = session.query(User).filter(text("id>:id")).params(id=2).all()
for item in o:
print(item.user_name)

#print('roles',user.roles)
# 关闭Session:
session.close()