使用Python开发SQLite代理服务器的方法

##
# 服务器程序，接收代理服务器转发来的SQL指令，并返回结果
#
import sqlite3
import socket
import struct

def getData(sql):
 \'\'\'通过给定的SQL SELECT语句返回结果\'\'\'
 with sqlite3.connect(r\'data .db\') as conn:
  cur = conn.cursor()
  cur.execute(sql)
  result = cur.fetchall()
 return result

def doSql(sql):
 \'\'\'适用于DELETE/UPDATE/INSERT INTO语句，返回影响的记录条数\'\'\'
 with sqlite3.connect(r\'data .db\') as conn:
  cur = conn.cursor()
  result = cur.execute(sql)
 return result.rowcount

# 创建socket对象，默认使用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:
   # 首先发送要发送的字节总数量
   # 然后再发送真实数据
   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)

##
# 代理服务器，在SQLite数据库服务器和客户端之间进行指令和数据的转发
# 这样可以把数据库和程序放到两个服务器上进行分离
##
import socket
from threading import Thread
import struct

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

def agent(conn):
 # 接收客户端发来的指令，并进行过滤
 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()
  # 尝试连接服务器
  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
   
  # 向服务程序转发SQL语句
  sockClient.send(sql)
  # 数据量大小，使用sturct序列化一个整数需要4个字节
  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代理服务器发送指令并接收数据
#
import sqlite3
import socket
import struct

while True:
 sql = input(\'输入一个要执行的SQL语句：\\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(\'服务器异常，请检查\')
 else:
  # 发送远程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:
    # 注意断包和粘包
    # 虽然设置了4096，但是不一定能够接收4096字节
    #即使缓冲区的数据远多于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)