import pymysql
import pandas as pd
import sqlalchemy as sqla
from sqlalchemy.orm import sessionmaker
import time
import re

#導(dǎo)入數(shù)據(jù)
data = pd.read_excel(r"E:/1/Study/大三下/自然語(yǔ)言處理/作業(yè)表.xlsx")
data = data.head(1)
#data

db = sqla.create_engine('mysql+pymysql://root:******@localhost/lsq?charset=utf8')#連接數(shù)據(jù)庫(kù)（密碼馬賽克一下）
#導(dǎo)入表格到數(shù)據(jù)庫(kù)中
data.to_sql('shopping_informations',db,index=False,if_exists='append')

conn = pymysql.connect("localhost","root","******","lsq")#*為密碼
cursor = conn.cursor()

def start_time():#發(fā)貨時(shí)間
  starttime_sql = "SELECT START_RIME FROM shopping_informations"
  try:
    cursor.execute(starttime_sql)
    result1 = cursor.fetchall()
    print("親您所購(gòu)買(mǎi)的寶貝計(jì)劃在%s進(jìn)行發(fā)貨~預(yù)計(jì)將會(huì)在%s不要著急喲" % result1[0])
  except:
    print("哎呀！機(jī)器客服這邊暫時(shí)找不到相關(guān)數(shù)據(jù)嗚嗚嗚~親可以聯(lián)系一下人工客服")
    conn.close()

def start_local():#發(fā)貨地
  startlocal_sql = "SELECT START_LOCAL FROM shopping_informations"
  try:
    cursor.execute(startlocal_sql)
    result2 = cursor.fetchone()
    print("親您所購(gòu)買(mǎi)的寶貝計(jì)劃從%s發(fā)出~不要著急喲" % result2[0])
  except:
    print("哎呀！機(jī)器客服這邊暫時(shí)找不到相關(guān)數(shù)據(jù)嗚嗚嗚~親可以聯(lián)系一下人工客服")
    conn.close()
   
def ID():#商品單號(hào)
  id_sql = "SELECT ID FROM shopping_informations"
  try:
    cursor.execute(id_sql)
    result3 = cursor.fetchone()
    print("親您所購(gòu)買(mǎi)的寶貝單號(hào)是%s" % result3[0])
  except:
    print("哎呀！機(jī)器客服這邊暫時(shí)找不到相關(guān)數(shù)據(jù)嗚嗚嗚~親可以聯(lián)系一下人工客服")
    conn.close()

def state():#商品狀態(tài)
  state_sql = "SELECT STATE FROM shopping_informations"
  try:
    cursor.execute(state_sql)
    result4 = cursor.fetchone()
    print("親您所購(gòu)買(mǎi)的寶貝現(xiàn)在在%s中~不要著急喲" % result4[0])
  except:
    print("哎呀！機(jī)器客服這邊暫時(shí)找不到相關(guān)數(shù)據(jù)嗚嗚嗚~親可以聯(lián)系一下人工客服")
  conn.close() 

str1 = ""
def answer_robot(str1):
  if re.search(r'.*快遞(.*)?',str1):#涉及到快遞公司的問(wèn)題
    str2 = "親！我們店統(tǒng)一默認(rèn)發(fā)百世匯通，按倉(cāng)擇優(yōu)分配快遞，不能指定喲~請(qǐng)諒解！"
  elif re.search(r'.*狀態(tài)(.*)?',str1):#涉及到商品狀態(tài)的問(wèn)題
    str2 = state()
  elif re.search(r'(\w)?[編號(hào)|單號(hào)|貨號(hào)]\w',str1):#涉及到商品單號(hào)的問(wèn)題
    str2 = ID()
  elif re.search( r'(\w)?[哪里|發(fā)貨地]\w',str1):#涉及到商品發(fā)貨地的問(wèn)題
    str2 = start_local()
  elif re.search( r'(\w)?[時(shí)間|時(shí)候]\w',str1):#涉及到商品發(fā)貨時(shí)間的問(wèn)題
    str2 = start_time()
  else:
    str2 = "嗚嗚嗚問(wèn)題太復(fù)雜啦！建議親找人工姐姐喲~"
  return str2

def main():
  while True:#可以循環(huán)多次使用（ctrl+c結(jié)束程序）
    str1 = input("輸入對(duì)話(huà)：")
    str3 = answer_robot(str1)
    time.sleep(1)
    print(str3)