from sqlalchemy import exists, Column, Integer, String, ForeignKey, DateTime, Text, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from conf.parseConfig import parseConf

# 從配置文件中獲取數(shù)據(jù)庫信息
host = parseConf.get_conf('MySQLInfo', 'host')
port = parseConf.get_conf('MySQLInfo', 'port')
dbname = parseConf.get_conf('MySQLInfo', 'dbname')
usernm = parseConf.get_conf('MySQLInfo', 'usernm')
passwd = parseConf.get_conf('MySQLInfo', 'passwd')
# 連接數(shù)據(jù)庫
engine_str = "mysql+mysqlconnector://{0}:{1}@{2}:{3}/{4}".format(usernm, passwd, host, port, dbname)
# 創(chuàng)建的數(shù)據(jù)庫引擎
engine = create_engine(engine_str, encoding='utf-8')

#創(chuàng)建session類型
DBSession = sessionmaker(bind=engine)
# 創(chuàng)建session對(duì)象，進(jìn)行增刪改查:
session = DBSession()

# 實(shí)例化官宣模型 - Base 就是 ORM 模型
Base = declarative_base()


# 創(chuàng)建服務(wù)單表 繼承Base基類
class ServiceOrder(Base):
  __tablename__ = 'serviceOrderTable'
  serviceOrderId = Column(String(32), primary_key=True, comment='服務(wù)單ID')
  serviceDesc = Column(String(512), comment='服務(wù)說明')
  transferTimes = Column(String(32), comment='轉(zhuǎn)派次數(shù)')
  # 創(chuàng)建更新時(shí)間，對(duì)數(shù)據(jù)的更新進(jìn)行記錄
  updateTime = Column(DateTime, server_default=func.now(), onupdate=func.now())


def init_db():
  Base.metadata.create_all(engine)


def drop_db():
  Base.metadata.drop_all(engine)


if __name__ == "__main__":
  # 每次執(zhí)行時(shí) 會(huì)判斷表的存在性 對(duì)于數(shù)據(jù)庫中不存在的表進(jìn)行創(chuàng)建 已存在的表則可以直接進(jìn)行增刪改查
  init_db()

  ### 首先講一下使用sqlalchemy執(zhí)行原生的sql語句###
  # 方式一：
  res = session.execute('select * from ServiceOrder') # res是獲取的對(duì)象
  all_res_list = res.fetchall() # all_res_list具體的結(jié)果 是列表
  print(all_res_list ) # 結(jié)果： [('數(shù)據(jù)提取',), ('非數(shù)據(jù)提取',)]
  # 方式二：
  conn = engine.connect()
  res = conn.execute('select * from ServiceOrder') 
  all_res_list = res.fetchall()

  ### 使用創(chuàng)建好的session對(duì)象進(jìn)行增刪改查 ###
  # 插入單條數(shù)據(jù)
  # 創(chuàng)建新service0rder對(duì)象
  new_serviceorder = ServiceOrder(serviceOrderId='001', serviceDesc='ack', transferTimes='9')
  # 添加到session
  session.add(new_serviceorder)
  # 提交即保存到數(shù)據(jù)庫
  session.commit()

  # 插入多條數(shù)據(jù)
  serviceorder_list = [ServiceOrder(serviceOrderId='002', serviceDesc='好的', transferTimes='9'),ServiceOrder(serviceOrderId='003', serviceDesc='起床', transferTimes='9')]
  session.add_all(serviceorder_list)
  session.commit()
  # session.close()

  # 查詢
  # 查詢是否存在 結(jié)果是布爾值
  it_exists = session.query(
    exists().where(ServiceOrder.serviceOrderId == '002')
  ).scalar()
  # 創(chuàng)建Query查詢，filter是where條件
  # 調(diào)用one() first()返回唯一行，如果調(diào)用all()則已列表的形式返回所有行:
  server_order = session.query(ServiceOrder).filter(ServiceOrder.serviceOrderId == '003').first()
  print(server_order.serviceDesc)
  serciceorders = session.query(ServiceOrder).filter(ServiceOrder.serviceDesc == '好的').all()

  # 改 更新數(shù)據(jù)
  # 數(shù)據(jù)更新，將值為Mack的serviceDesc修改為Danny
  update_obj = session.query(ServiceOrder).filter(ServiceOrder.serviceDesc == 'Mack').update({"serviceDesc": "Danny"})
  # 或則
  update_objp = session.query(ServiceOrder).filter(ServiceOrder.serviceDesc == 'Mack').first()
  update_objp.serviceDesc = 'Danny'
  session.commit()

  # 刪除
  update_objk = session.query(ServiceOrder).filter(ServiceOrder.serviceDesc == 'Mack').delete()
  # 或則
  update_objkp = session.query(ServiceOrder).filter(ServiceOrder.serviceDesc == 'Mack').one()
  update_objkp.delete()
  session.commit()
  session.close()