#!/usr/bin/env python
# -*- coding: utf-8 -*-

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

#######################################################################################################
##############初始化數(shù)據(jù)庫連接唾糯，返回session
#######################################################################################################
def get_session():
    # 初始化數(shù)據(jù)庫連接 # '數(shù)據(jù)庫類型+數(shù)據(jù)庫驅動名稱://用戶名:口令@機器地址:端口號/數(shù)據(jù)庫名'
    engine = create_engine('mysql+pymysql://root:root@10.10.10.11:9000/mysqldb?charset=utf8')
    DBSession = sessionmaker(bind=engine)  # 創(chuàng)建DBSession類型
    session = DBSession()
    return session



#######################################################################################################
##############表格對象創(chuàng)建
#######################################################################################################
###創(chuàng)建對象的基類:
Base = declarative_base()

###定義Asset表對象
class Asset(Base):
    # 表的名字:
    __tablename__ = 'hr_asset'

    # 表的結構:
    id = Column(Integer, primary_key=True)
    assetnum = Column(String(50))
    assetname = Column(String(50))
    assetmodel = Column(String(50))
    assettype = Column(Integer)
    assetstate =  Column(Integer)
    usepersonid = Column(Integer, ForeignKey("hr_user.id"))
    userperson = relationship("User",backref="asset", uselist=False)


####定義User表對象
class User(Base):
    # 表的名字:
    __tablename__ = 'hr_user'

    # 表的結構:
    id = Column(Integer, primary_key=True)
    username = Column(String(50))
    age =  Column(Integer)
    degree = Column(String(50))



#######################################################################################################
##############數(shù)據(jù)操作模板
########################################################################################################
####INSERT插入數(shù)據(jù)  模板
if __name__ == "__main__":
    try:
      # 創(chuàng)建session對象
      session1 = get_session()
      # 創(chuàng)建新表格對象
      new_user = User(username='龍九', age=33, degree="本科")
      # 添加到session
      session1 .add(new_user)
      # 提交即保存到數(shù)據(jù)庫
      session1 .commit()
      # 得到新數(shù)據(jù)id  
      the_id=new_user.id    
      # 關閉session
      session1 .close()
    except:
        pass



####DELETE刪除數(shù)據(jù)  模板
if __name__ == "__main__":
    try:
        session2 = get_session()
        session2.query(User).filter(User.id == '3').delete()
        session2.commit()
        session2.close()
    except:
        pass

####UPDATE更新數(shù)據(jù)  模板
if __name__ == "__main__":    #模板1
    try:
        session3 = get_session()
        session3.query(User).filter(User.id == '2').update({User.degree: '高中'}, synchronize_session=False)
        session3.commit()
        session3.close()
    except:
        pass




if __name__ == "__main__":   #模板2   
    try:
        session3 = get_session()
        the_user = session3.query(User).get(2)  #參數(shù)為主鍵id
        the_user.username ="小王"
        the_user.age= 40
        the_user.degree ="博士"
        session3.commit()
        session3.close()
    except:
        pass


####SELECT查詢數(shù)據(jù)之單條數(shù)據(jù) 模板
if __name__ == "__main__":
    try:
        session4 = get_session()
        user = session4.query(User).filter(User.id == 3).one()    #注：這里用one()
        print('type:', type(user))
        print('name:', user.username)
        session4.close()
    except:
        pass



####SELECT查詢數(shù)據(jù)之多條數(shù)據(jù) 模板
if __name__ == "__main__":
    try:
        session5 = get_session()
        users = session5.query(User).filter(User.id > 0).all()    #注：這里用all()
        for i in range(len(users)):
            print(users[i].id)
            print(users[i].username)
        session5.close()
    except:
        pass

####SELECT查詢數(shù)據(jù)之連表查詢 模板
if __name__ == "__main__":
    try:
        session6 = get_session()
        assets = session6.query(Asset).join(User).filter(User.id > 0).all()
        for i in range(len(assets)):
            pass
        session6.close()
    except:
        pass