#!/usr/bin/env python3
from __future__ import unicode_literals
# coding=utf-8


from socket import *
import os
import pymysql
import time
import sys
import signal

DICT_TEXT = "./dict.txt"
HOST = '0.0.0.0'
PORT = 8000
ADDR = (HOST, PORT)

# 主控制流程


def main():
 # 連接數據庫
 db = pymysql.connect\
 ('localhost', 'root', '123456', 'dict')

 # 創建流式套接字
 s = socket()
 s.setsockopt(SOL_SOCKET, SO_REUSEADDR, 1)
 s.bind(ADDR)
 s.listen(5)
 # 或略子進程退出
 signal.signal(signal.SIGCHLD, signal.SIG_IGN)

 while True:
 try:
  c, addr = s.accept()
  print("Connect from", addr)
 except KeyboardInterrupt:
  s.close()
  sys.exit("服務器退出")
 except Exception as e:
  print(e)
  continue
 # 創建子進程處理客戶端請求
 pid = os.fork()
 if pid == 0:
  s.close()
  do_child(c, db)
 else:
  c.close()


def do_child(c, db):
 # 循環接收請求
 while True:
 data = c.recv(128).decode()
 print("Request:", data)
 if (not data) or data[0] == 'E':
  c.close()
  sys.exit(0)
 elif data[0] == 'R':
  do_register(c, db, data)
 elif data[0] == "L":
  do_login(c, db, data)
 elif data[0] == 'Q':
  do_query(c, db, data)
 elif data[0] == 'H':
  do_history(c, db, data)


def do_register(c, db, data):
 l = data.split(' ')
 name = l[1]
 passwd = l[2]

 cursor = db.cursor()
 sql = \
 "select * from user where name='%s'" % name
 cursor.execute(sql)
 r = cursor.fetchone()
 if r != None:
 c.send(b'EXISTS')
 return

 sql = "insert into user (name,passwd)\
 values ('%s','%s')" % (name, passwd)
 try:
 cursor.execute(sql)
 db.commit()
 c.send(b'OK')
 except:
 db.rollback()
 c.send(b'FALL')
 return
 else:
 print("%s注冊成功" % name)


def do_login(c, db, data):
 l = data.split(' ')
 name = l[1]
 passwd = l[2]
 cursor = db.cursor()

 sql = "select * from user where \
 name='%s' and passwd='%s'" % (name, passwd)

 cursor.execute(sql)
 r = cursor.fetchone()
 if r == None:
 c.send('用戶名或密碼不正確'.encode())
 else:
 c.send(b'OK')


def do_query(c, db, data):
 l = data.split(' ')
 name = l[1]
 word = l[2]
 cursor = db.cursor()

 def insert_history():
 tm = time.ctime()
 sql = "insert into hist (name,word,time)\
  values ('%s','%s','%s')" % (name, word, tm)
 try:
  cursor.execute(sql)
  db.commit()
 except:
  db.rollback()
  return

 try:
 f = open(DICT_TEXT, 'rb')
 except:
 c.send("500 服務端異常".encode())
 return
 while True:
 line = f.readline().decode()
 w = line.split(' ')[0]
 if (not line) or w > word:
  c.send("沒找到該單詞".encode())
  break
 elif w == word:
  c.send(b'OK')
  time.sleep(0.1)
  c.send(line.encode())
  insert_history()
  break
 f.close()


def do_history(c, db, data):
 name = data.split(' ')[1]
 cursor = db.cursor()

 try:
 sql = "select * from hist \
 where name='%s'" % name
 cursor.execute(sql)
 r = cursor.fetchall()
 if not r:
  c.send('沒有歷史記錄'.encode())
  return
 else:
  c.send(b'OK')
 except:
 c.send("數據庫查詢錯誤".encode())
 return
 n = 0
 for i in r:
 n += 1
 # 最多顯示10條
 if n > 10:
  break
 time.sleep(0.1)
 msg = "%s %s %s" % (i[1], i[2], i[3])
 c.send(msg.encode())
 time.sleep(0.1)
 c.send(b'##')


if __name__ == "__main__":
 main()

#!/usr/bin/env python3
#coding=utf-8

from socket import *
import sys 
import getpass

def main():
 if len(sys.argv) < 3:
 print("argv is error")
 return
 HOST = sys.argv[1]
 PORT = int(sys.argv[2])
 ADDR = (HOST,PORT)

 s = socket()
 s.connect(ADDR)

 while True:
 print('''\n
  ===========Welcome=========
  --1.注冊 2.登錄 3.退出--
  ===========================
  ''')
 try:
  cmd = int(input("輸入選項>>"))
 except Exception:
  print("輸入命令錯誤")
  continue 

 if cmd not in [1,2,3]:
  print("對不起，沒有該命令")
  sys.stdin.flush() #清除輸入
  continue 
 elif cmd == 1:
  name = do_register(s)
  if name != 1:
  print("注冊成功,直接登錄！")
  login(s,name)
  else:
  print("注冊失敗！")
 elif cmd == 2:
  name = do_login(s)
  if name != 1:
  print("登錄成功！")
  login(s,name)
  else:
  print("登錄失敗！")
 elif cmd == 3:
  s.send(b"E")
  sys.exit("謝謝使用")

def do_register(s):
 while True:
 name = input("用戶名:")
 passwd = getpass.getpass("密 碼:")
 passwd1 = getpass.getpass("確認密碼:")

 if (' ' in name) or (' ' in passwd):
  print("用戶名密碼不允許空格")
  continue
 if passwd != passwd1:
  print("兩次密碼不一致")
  continue

 msg = "R {} {}".format(name,passwd)
 #發送請求
 s.send(msg.encode())
 #接收回復
 data = s.recv(128).decode()

 if data == "OK":
  return name
 elif data == 'EXISTS':
  print("該用戶已存在")
  return 1
 else:
  return 1
def do_login(s):
 name = input("用戶名:")
 passwd = getpass.getpass("密 碼:")
 msg = "L {} {}".format(name,passwd)
 s.send(msg.encode())
 data = s.recv(128).decode()

 if data == 'OK':
 return name
 else:
 print(data)
 return 1

def login(s,name):
 while True:
 print('''\n
  ===========查詢界面============
  1.查詞 2.歷史記錄 3.注銷
  =============================
  ''')
 try:
  cmd = int(input("輸入選項>>"))
 except Exception:
  print("命令錯誤")
  continue
 if cmd not in [1,2,3]:
  print("對不起，沒有該命令")
  sys.stdin.flush() #清除輸入
  continue 
 elif cmd == 1:
  do_query(s,name)
 elif cmd == 2:
  do_history(s,name)
 elif cmd == 3:
  return

def do_query(s,name):
 while True:
 word = input("單詞:")
 if word == "##":
  break 
 msg = "Q {} {}".format(name,word)
 s.send(msg.encode())
 data = s.recv(128).decode()
 if data == 'OK':
  data = s.recv(2048).decode()
  print(data)
 else:
  print(data)


def do_history(s,name):
 msg = "H {}".format(name)
 s.send(msg.encode())
 data = s.recv(128).decode()
 if data == 'OK':
 while True:
  data = s.recv(1024).decode()
  if data == "##":
  break
  print(data)
 else:
 print(data)


if __name__ == "__main__":
 main()

import pymysql 
import re

f = open('dict.txt')
db = pymysql.connect\
('localhost','root','123456','dict')

cursor = db.cursor()

for line in f:
 try:
 l = re.split("[ ]+",line)
 except:
 pass 
 sql = "insert into words (word,interpret)\
 values ('%s','%s')"%(l[0],' '.join(l[1:]))

 try:
 cursor.execute(sql)
 db.commit()
 except:
 db.rollback()
 
f.close()