##
# 服務(wù)器程序，接收代理服務(wù)器轉(zhuǎn)發(fā)來的SQL指令，并返回結(jié)果
#
import sqlite3
import socket
import struct

def getData(sql):
 '''通過給定的SQL SELECT語句返回結(jié)果'''
 with sqlite3.connect(r'database.db') as conn:
  cur = conn.cursor()
  cur.execute(sql)
  result = cur.fetchall()
 return result

def doSql(sql):
 '''適用于DELETE/UPDATE/INSERT INTO語句，返回影響的記錄條數(shù)'''
 with sqlite3.connect(r'database.db') as conn:
  cur = conn.cursor()
  result = cur.execute(sql)
 return result.rowcount

# 創(chuàng)建socket對(duì)象，默認(rèn)使用IPV4+TCP
sockServer = socket.socket()
sockServer.bind(('', 3030))
sockServer.listen(1)
while True:
 # 接收客戶端連接
 try:
  conn, addr = sockServer.accept()
 except:
  continue
 
 sql = conn.recv(1024).decode('gbk').lower()
 
 if sql.startswith(('update','delete','insert')):
  try:
   # 首先發(fā)送要發(fā)送的字節(jié)總數(shù)量
   # 然后再發(fā)送真實(shí)數(shù)據(jù)
   result = str(doSql(sql)).encode('gbk')
   conn.send(struct.pack('i', len(result)))
   conn.send(result)
  except:
   message = b'error'
   conn.send(struct.pack('i', len(message)))
   conn.send(message)
 elif sql.startswith('select'):
  try:
   result = str(getData(sql)).encode('gbk')
   conn.send(struct.pack('i', len(result)))
   conn.send(result)
  except:
   message = b'error'
   conn.send(struct.pack('i', len(message)))
   conn.send(message)

##
# 代理服務(wù)器，在SQLite數(shù)據(jù)庫服務(wù)器和客戶端之間進(jìn)行指令和數(shù)據(jù)的轉(zhuǎn)發(fā)
# 這樣可以把數(shù)據(jù)庫和程序放到兩個(gè)服務(wù)器上進(jìn)行分離
##
import socket
from threading import Thread
import struct

sockServer = socket.socket()
sockServer.bind(('',5050))
sockServer.listen(50)

def agent(conn):
 # 接收客戶端發(fā)來的指令，并進(jìn)行過濾
 sql = conn.recv(1024)
 if not sql.decode('gbk').startswith(('select', 'delete', 'insert','update')):
  message = b'not a sql statement'
  conn.send(struct.pack('i', len(message)))
  conn.send(message)
  return
 else:
  sockClient = socket.socket()
  # 嘗試連接服務(wù)器
  try:
   sockClient.connect(('10.2.1.3', 3030))
  except:
   message = b'Server not alive'
   conn.send(struct.pack('i', len(message)))
   conn.send(message)
   return
   
  # 向服務(wù)程序轉(zhuǎn)發(fā)SQL語句
  sockClient.send(sql)
  # 數(shù)據(jù)量大小，使用sturct序列化一個(gè)整數(shù)需要4個(gè)字節(jié)
  size = sockClient.recv(4)
  conn.send(size)
  size = struct.unpack('i', size)[0]
  while True:
   if size == 0:
    break
   elif size > 4096:
    data = sockClient.recv(4096)
    conn.send(data)
    size -= len(data)
   else:
    data = sockClient.recv(size)
    conn.send(data)
    size -= len(data)
  sockClient.close()
 conn.close()

while True:
 conn, _ = sockServer.accept()
 Thread(target=agent, args=(conn,)).start()

##
# 模擬客戶端，向SQLite代理服務(wù)器發(fā)送指令并接收數(shù)據(jù)
#
import sqlite3
import socket
import struct

while True:
 sql = input('輸入一個(gè)要執(zhí)行的SQL語句：\n')
 # 沒有輸入，進(jìn)入下一次循環(huán)
 if sql.strip() == '':
  continue
 
 # 輸入exit或quit，退出客戶端
 if sql in ('exit', 'quit'):
  break
 # 建立socket，嘗試連接
 sockClient = socket.socket()
 try:
  sockClient.connect(('10.2.1.3', 5050))  
 except:
  print('服務(wù)器異常，請(qǐng)檢查')
 else:
  # 發(fā)送遠(yuǎn)程SQL語句
  sockClient.send(sql.encode('gbk'))
  size = sockClient.recv(4)
  size = struct.unpack('i', size)[0]
  
  data = b''
  while True:
   if size == 0:
    break
   elif size > 4096:
    # 注意斷包和粘包
    # 雖然設(shè)置了4096，但是不一定能夠接收4096字節(jié)
    #即使緩沖區(qū)的數(shù)據(jù)遠(yuǎn)多于4096
    t = sockClient.recv(4096)
    data += t
    size -= len(t)
   else:
    t = sockClient.recv(size)
    data += t
    size -= len(t)
  data = data.decode('gbk')
  try:
   data = eval(data)
  except:
   pass
  sockClient.close()
  print(data)