python网络编程：socket服务端客户端通信与sqlite的结合实例

socket库

借助于网络化，即便运行在不同的机器上，计算机程序可以彼此通信一。大多数网络化程序的工作模式或者是点对点（相同程序运行在不同机器上）的，或者是更常见的客户端/服务器（客户端程序向服务器提交请求）模式。

一个基本的客户端/服务器模式的应用程序通常实现为两个分离的程序：服务器，等待来自客户端的请求并对其进行响应；一个或多个客户端，向服务器提交请求并处理服务器的响应信息。为保证这一模式顺利运作，客户端必须知道要连接的服务器在哪里，也就是服务器的IP (Internet协议）地址与端口号。此外，客户端与服务器在发送与接收数据时，都必须使用议定的协议，协议中要使用双方都可以正确理解与处理的数据格式。

Python的底层socket模块（Python 的所有高层网络功能模块都以此模块为基础)同时支持IPv4地址与IPv6地址，也支持大多数通常使用的网络协议，包括UDP（用户数据报协议）与TCP（传输控制协议)。UDP是一个轻量级（但不是很可靠〉的无连接协议，在这一协议中，数据是以离散的数据包（数据报）形式发送的，但并不能保证一定可以发送到目的地;TCP是一个可靠的、有连接的、面向流的协议，借助于TCP，任意数量的数据都可以发送或接收—一在发送端，socket负责将数据分解为合适大小的数据块，以便可以正确发送;在接收端，socket负责将数据进行重组。

据百度百科，

所谓套接字(Socket)，就是对网络中不同主机上的应用进程之间进行双向通信的端点的抽象。

据菜鸟，

Socket又称"套接字"，应用程序通常通过"套接字"向网络发出请求或者应答网络请求，使主机间或者一台计算机上的进程间可以通讯。

python中有socket库，能用 socket()函数来创建socket套接字，但我认为这是将socket库里的抽象类socket实例化。

端口号的选择是任意的，但端口号应该大于1023，小于65535。通常选取5001与32767之间的。

一个IP地址的端口通过16bit进行编号，最多可以有65536个端口 。端口是通过端口号来标记的，端口号只有整数，范围是从0 到65535。

首先创建一个服务器

import socket
# 以TCP协议的IPv4建立服务器
server = socket.socket(socket.AF_INET,socket.SOCK_STREAM)
# 将服务器绑定到本地主机的9999端口上
server.bind(('localhost',9999))
# 开始监听
server.listen()
while True:
    conn, addr = server.accept()
    while True:
        try:
            # 接收数据（在python3中，socket接受和发送的都是二进制数据）
            data = conn.recv(1024)
            data = data.decode()
            print('Receive: ', data)
            conn.sendall(data.upper().encode('utf-8')) #然后再发送数据
        except ConnectionResetError as e:
        # 如果客户端断开连接则输出
            print('与一客户端失去连接')
            break
    conn.close()
# 因为设置了无限接受和发送数据 close 用不到 终止使用 ctrl+c
# server.close()

之后我们创建一个客户端

import socket
import sys

# 以TCP协议的IPv4建立客户端
client = socket.socket(socket.AF_INET,socket.SOCK_STREAM)
#建立一个链接，连接到本地的9999端口
client.connect(('localhost',9999))
while True:
    # addr = client.accept()

    # msg = input("enter what you want to say: ")
    try:
        msg = input('enter what you want to say: ')
        if msg != '':
            client.sendall(msg.encode('utf-8'))
        else:
            client.sendall('Empty'.encode('utf-8'))

        # 接收服务器发送的信息，并指定接收的大小为1024字节(不超即可)
        data = client.recv(1024)
        print('Receive: ',data.decode())
    except KeyboardInterrupt:
        client.close()
        sys.exit()

之后，可以开两个终端，一个开服务器，一个开客户端。当然是先开服务器再开客户端。
客户端情况

enter what you want to say: dasd
Receive:  DASD
enter what you want to say: zxcq5d1
Receive:  ZXCQ5D1
enter what you want to say: 3z2x1c32z1
Receive:  3Z2X1C32Z1
enter what you want to say: 3zx1
Receive:  3ZX1
enter what you want to say: c3z1c
Receive:  C3Z1C
enter what you want to say: 3zx13
Receive:  3ZX13
enter what you want to say: czx1
Receive:  CZX1
enter what you want to say: 3c2z
Receive:  3C2Z
enter what you want to say: 3x2c6a+
Receive:  3X2C6A+
enter what you want to say: d6
Receive:  D6
enter what you want to say: 

服务器情况

Receive:  dasd
Receive:  zxcq5d1
Receive:  3z2x1c32z1
Receive:  3zx1
Receive:  c3z1c
Receive:  3zx13
Receive:  czx1
Receive:  3c2z
Receive:  3x2c6a+
Receive:  d6

但是呢，如果你在终端上运行客户端，并且使用ctrl+c来抛出KeyboardInterrupt错误来终止程序的话，服务端就会持续不断地出现

Receive:
Receive:
Receive:
Receive:
Receive:

如果在终端运行客户端，直接点×关闭客户端；或者在pycharm上运行，直接点stop（结束后会出现Process finished with exit code -1）那么在服务端正常出现：

与一客户端失去连接

目前试了很多，比如如果出现KeyboardInterrupt就sys.exit()，也不行。关键是要去理解python程序非正常关闭的时候是抛出了什么异常或者是其他什么原因…

socket + sqlite 远程操纵数据库

做这个的起因是一道题目：

客户端能够向服务器发送5种不同的请求：

1. create：创建一个银行账户，账户的初始金额为0.0；
2. increment：对某个账户增加一具体金额
3. decrement：对某个账户减少一具体金额
4. account：展示某一账户的所有信息
5. shutdown：关闭服务器

在服务器开启的情况下，客户端的示例如下：

(c)reate (i)ncrement (d)ecrement (a)ccount (s)top server (q)uit [c]: _c_
Account Number: _123456_
Account Holder: _John Doe_
Phone Number: _987654321_
(c)reate (i)ncrement (d)ecrement (a)ccount (s)top server (q)uit [c]: _c_
Account Number: _222333_
Account Holder: _Alice Queen_
Phone Number: _999654111_
(c)reate (i)ncrement (d)ecrement (a)ccount (s)top server (q)uit [c]: _i_
Account Number: _123456_
Amount: _50.4_
(c)reate (i)ncrement (d)ecrement (a)ccount (s)top server (q)uit [c]: _a_
Account Number: _123456_
Account 123456 Details:
Holder: John Doe
Phone Number: 987654321
Current Balance: 50.4
(c)reate (i)ncrement (d)ecrement (a)ccount (s)top server (q)uit [c]: _s_
(c)reate (i)ncrement (d)ecrement (a)ccount (s)top server (q)uit [c]: _q_

其中，两个下划线("_")之间的数据被视为用户输入的，如_c_，即为用户输入了c

服务器所管控的数据库的建立可以参考ER图：

这道题目是参考着Programming in Python 3 (Second Edition)的第十一章做的，这是个开源的书，它的源代码也可以在这个网站下载到，接下来客户端中的get_string，get_float和get_menu_choice是源文件Console.py里挺实用的函数

这个项目是用socket作客户端，但是用以socket为基础的socketserver来搭建的，服务器的中心基本上就是下面的草稿代码

class BankServer(socketserver.ThreadingMixIn, socketserver.TCPServer): pass
                            
class RequestHandler(socketserver.StreamRequestHandler):
	def handle(self):
	def shutdown(self):
	def create_account(self):
	def crease_balance(self):
	

server = BankServer(("localhost", 9653), RequestHandler)
server.serve_forever()

socketserver服务器中很核心的就是他的RequestHandler类中的handle函数了，他是接管从客户端发来的数据并进行处理的。

def handle(self):
    SizeStruct = struct.Struct("!I")
    # read first 4 bytes
    size_data = self.rfile.read(SizeStruct.size)
    size = SizeStruct.unpack(size_data)[0]
    # the real data and transform binary to normal
    data = pickle.loads(self.rfile.read(size))

    try:
        with RequestHandler.CallLock:
        # which function to use
            function = self.Call[data[0]]
        # run function and put the parameters into it
        reply = function(self, *data[1:])
    except Finish:
        return
    data = pickle.dumps(reply, 3)

    self.wfile.write(SizeStruct.pack(len(data)))
    self.wfile.write(data)

至于为何要用struct.Struct，可以参考“浅析Python中的struct模块”，而且需要明白客户端发送的信息的形式才能比较理解。

在该方法内部，来自于客户端的数据将从文件对象self.rfile读取，这可以理解为一个缓冲区，客户端向缓冲区内发送数据，服务端可以从缓冲区内读取数据。向客户端返回数据则可以通过写self.wfile对象实现，当然这两个对象都是由socketserver提供的。这里的rfile.read()和python里正常文件的open()的read()、readline()操作类似，就是你每执行一次readline()，open()过对象里就少了被读取的那一部分，就是取了东西不放回的意思。

在RequestHandler类里面，其实还创建了两个线程的锁，为了防止多个用户同时访问同一个数据之类的导致数据库损坏或程序崩溃等。

BankLock = threading.Lock()
CallLock = threading.Lock()

处理好的data内容很简单，一个是请求调用的函数名，一个是参数或参数块。data[0]是前者，data[1:]是后者。

对于

    Call = dict(
        ACCOUNT_DUPLICATE=(
            lambda self, *args: self.get_all_from_account(*args)),
        PHONE_DUPLICATE=(
            lambda self, *args: self.get_all_from_customer(*args)),

如果

data = ['ACCOUNT_DUPLICATE', True, 1, 2, 0]

那么

data[0] = 'ACCOUNT_DUPLICATE'
data[1:] = [True, 1, 2, 0]
function = self.get_all_from_account
reply = function(True, 1, 2, 0)

dumps用于将对象以二进制格式存储在内存中，便于进行发送。

对于pickle的dumps的protocol选取问题，摘自stackoverflow：

There are currently 6 different protocols which can be used for pickling. The higher the protocol used, the more recent the version of Python needed to read the pickle produced.

• Protocol version 0 is the original “human-readable” protocol and is backwards compatible with earlier versions of Python.
• Protocol version 1 is an old binary format which is also compatible with earlier versions of Python.
• Protocol version 2 was introduced in Python 2.3. It provides much more efficient pickling of new-style classes. Refer to PEP 307 for information about improvements brought by protocol 2.
• Protocol version 3 was added in Python 3.0. It has explicit support for bytes objects and cannot be unpickled by Python 2.x. This was the default protocol in Python 3.0–3.7.
• Protocol version 4 was added in Python 3.4. It adds support for very large objects, pickling more kinds of objects, and some data format optimizations. It is the default protocol starting with Python 3.8. Refer to PEP 3154 for information about improvements brought by protocol 4.
• Protocol version 5 was added in Python 3.8. It adds support for out-of-band data and speedup for in-band data. Refer to PEP 574 for information about improvements brought by protocol 5.
  If a protocol is not specified, protocol 0 is used. If protocol is specified as a negative value or HIGHEST_PROTOCOL, the highest protocol version available will be used.

随后将需要发送的数据大小和二进制数据写入缓冲区进而发送给客户端。

至于数据库的搭建，可以参考学习菜鸟教程

由于python的sqlite不允许某一线程操纵其他线程，即

SQLite objects created in a thread can only be used in that same thread

这是我第一开始将conn当做RequstHandler类里面的一个属性，让他管完所有的execute操作而引发的错误，所以在每一个数据库操作，我都使用了：

with sqlite3.connect(r'bank.db') as conn:
    cursor = conn.cursor()
    cursor.execute()
    conn.commit()

服务端完整代码：

import pickle
import socketserver
import struct
import threading
import sqlite3


class Finish(Exception): pass


class RequestHandler(socketserver.StreamRequestHandler):
    BankLock = threading.Lock()
    CallLock = threading.Lock()
    Call = dict(
        ACCOUNT_DUPLICATE=(
            lambda self, *args: self.get_all_from_account(*args)),
        PHONE_DUPLICATE=(
            lambda self, *args: self.get_all_from_customer(*args)),
        CREATE_ACCOUNT=(
            lambda self, *args: self.create_account(*args)),
        GET_BALANCE=(
            lambda self, *args: self.get_balance(*args)),
        CREASE_BALANCE=(
            lambda self, *args: self.crease_balance(*args)),
        GET_HOLDER=(
            lambda self, *args: self.get_holder(*args)),
        SHUTDOWN=lambda self, *args: self.shutdown(*args))

    def get_holder(self, account_number):
        with RequestHandler.BankLock:
            with sqlite3.connect(r'bank.db') as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT customer_id FROM account WHERE account_number=?", (account_number,))
                customer_id = list(cursor)[0][0]
                cursor.execute("SELECT name, phone_number FROM customer WHERE id=?", (customer_id,))
                temp = list(cursor)[0]
                account_holder = temp[0]
                phone_number = temp[1]

        return (True, account_holder, phone_number)


    def crease_balance(self, account_number, current_amount):
        with RequestHandler.BankLock:
            with sqlite3.connect(r'bank.db') as conn:
                cursor = conn.cursor()
                cursor.execute("UPDATE account set balance=? where account_number=?",
                                    (current_amount, account_number))
                conn.commit()

        return None

    
    def get_balance(self, account_number):
        with RequestHandler.BankLock:
            with sqlite3.connect(r'bank.db') as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT balance FROM account WHERE account_number=?", (account_number,))
                temp = list(cursor)
                if temp == []:
                    return (False, '-*- this account does not exist -*-')

        return (True, temp[0][0])


    def get_all_from_account(self, account_number):
        with RequestHandler.BankLock:
            with sqlite3.connect(r'bank.db') as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT * FROM account WHERE account_number=?", (account_number,))
                if len(list(cursor)) != 0:
                    return (False, '-*- this account already exists -*-')

        return (True, None)

    def get_all_from_customer(self, account_holder, phone_number):
        with RequestHandler.BankLock:
            with sqlite3.connect(r'bank.db') as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT phone_number FROM customer WHERE name=?", (account_holder,))
                temp = list(cursor)

                if temp != []:
                    if temp[0] == phone_number:
                        return (True, None)
                    else:
                        return (False, '-*- this user already has a phone number -*-')

        return (True, None)

    def create_account(self, account_number, account_holder, phone_number):
        with RequestHandler.BankLock:
            with sqlite3.connect(r'bank.db') as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT INTO customer (name, phone_number)
                    VALUES (?, ?);''',
                    (account_holder, phone_number))
                conn.commit()
    
                cursor.execute("SELECT id FROM customer WHERE name=?", (account_holder,))

                customer_id = list(cursor)[0][0]
    
                cursor.execute('''
                    INSERT INTO account (account_number, balance, customer_id)
                    VALUES (?, 0, ?);''',
                    (account_number, customer_id))
                conn.commit()

        return None


    def handle(self):
        SizeStruct = struct.Struct("!I")
        # # read first 4 bytes
        size_data = self.rfile.read(SizeStruct.size)
        size = SizeStruct.unpack(size_data)[0]
        # the real data and transform binary to normal
        data = pickle.loads(self.rfile.read(size))
        # data = pickle.loads(self.rfile.read(1024))


        try:
            with RequestHandler.CallLock:
            # which function to use
                function = self.Call[data[0]]
            # run function and put the parameters into it
            reply = function(self, *data[1:])
        except Finish:
            return
        data = pickle.dumps(reply, 3)

        self.wfile.write(SizeStruct.pack(len(data)))
        self.wfile.write(data)


    def shutdown(self, *ignore):
        self.server.shutdown()
        raise Finish()



class BankServer(socketserver.ThreadingMixIn,
                            socketserver.TCPServer): pass


def create_tables(conn, cursor):

    cursor.execute('''
    CREATE TABLE IF NOT EXISTS customer
        (
            id               INTEGER       PRIMARY KEY   AUTOINCREMENT,
            name             CHAR(30)  NOT NULL,
            phone_number     CHAR(20)  NOT NULL
        );''')
    conn.commit()

    cursor.execute('''
    CREATE TABLE IF NOT EXISTS account
        (
            id               INTEGER         PRIMARY KEY     AUTOINCREMENT,
            account_number   CHAR(30)    NOT NULL,
            balance          FLOAT,
            customer_id      INT     NOT NULL    REFERENCES  customer(id)
        );''')
    conn.commit()


def main():
    with sqlite3.connect(r'bank.db') as conn:
        cursor = conn.cursor()
        create_tables(conn, cursor)


    server = None
    try:
        server = BankServer(("localhost", 9653), RequestHandler)
        server.serve_forever()
    except Exception as err:
        print("ERROR", err)
    finally:
        if server is not None:
            server.shutdown()

if __name__ == '__main__':
    main()

客户端的核心就是读取用户输入的数据，判断数据是否合法，并对这些数据进行整合并发送到客户端，并且在接受到服务器发送来的数据后进行合理地判断，并将最后需要输出的结果展示到客户端的界面上。

同样的，服务端中的handle_request是很重要的函数，他和服务器的代码差不多。

客户端完整代码：

import pickle
import socket
import struct
import sys

Address = ["localhost", 9653]

class _RangeError(Exception): pass

class SocketManager:

    def __init__(self, address):
        self.address = address

    def __enter__(self):
        self.sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        self.sock.connect(self.address)
        return self.sock

    def __exit__(self, *ignore):
        self.sock.close()

def get_string(message, name="string", default=None,
               minimum_length=0, maximum_length=80,
               force_lower=False):
    message += ": " if default is None else " [{0}]: ".format(default)
    while True:
        try:
            line = input(message)
            if not line:
                if default is not None:
                    return default
                if minimum_length == 0:
                    return ""
                else:
                    raise ValueError("{0} may not be empty".format(
                                     name))
            if not (minimum_length <= len(line) <= maximum_length):
                raise ValueError("{0} must have at least {1} and "
                        "at most {2} characters".format(
                        name, minimum_length, maximum_length))
            return line if not force_lower else line.lower()
        except ValueError as err:
            print("ERROR", err)

def get_float(message, name="float", default=None, minimum=None,
              maximum=None, allow_zero=True):
    message += ": " if default is None else " [{0}]: ".format(default)
    while True:
        try:
            line = input(message)
            if not line and default is not None:
                return default
            x = float(line)
            if abs(x) < sys.float_info.epsilon:
                if allow_zero:
                    return x
                else:
                    raise _RangeError("{0} may not be 0.0".format(
                                      name))
            if ((minimum is not None and minimum > x) or
                (maximum is not None and maximum < x)):
                raise _RangeError("{0} must be between {1} and {2} "
                        "inclusive{3}".format(name, minimum, maximum,
                        (" (or 0.0)" if allow_zero else "")))
            return x
        except _RangeError as err:
            print("ERROR", err)
        except ValueError as err:
            print("ERROR {0} must be a float".format(name))

def get_menu_choice(message, valid, default=None, force_lower=False):
    message += ": " if default is None else " [{0}]: ".format(default)
    while True:
        line = input(message)
        if not line and default is not None:
            return default
        if line not in valid:
            print("ERROR only {0} are valid choices".format(
                  ", ".join(["'{0}'".format(x)
                  for x in sorted(valid)])))
        else:
            return line if not force_lower else line.lower()


def main():
    call = dict(c=create_account, i=increase, d=decrease,
                a=account, s=stop_server, q=quit)
    menu = ('(c)reate (i)ncrement (d)ecrement (a)ccount (s)top server (q)uit')
    valid = frozenset("cidasq")

    while True:
        action = get_menu_choice(menu, valid, "c", True)
        ignore = call[action]()


def create_account():
    account_number = get_string("Account Number", minimum_length=1)
    ok, *data = handle_request("ACCOUNT_DUPLICATE", account_number)
    if not ok:
        print(data[0])
        return None
    else:
        account_holder = get_string("Account Holder", minimum_length=1)
        phone_number = get_string("Phone Number", minimum_length=1)
        ok, *data = handle_request("PHONE_DUPLICATE", account_holder, phone_number)
        if not ok:
            print(data[0])
            return None
        else:
            handle_request("CREATE_ACCOUNT", account_number,
                           account_holder, phone_number)
            return None

def increase():
    account_number = get_string("Account Number")
    ok, *data = handle_request("GET_BALANCE", account_number)
    if not ok:
        print(data[0])
        return None
    else:
        original = data[0]
        increase_amount = get_float("Amount")
        current_amount = float(original) + increase_amount
        handle_request("CREASE_BALANCE", account_number, current_amount)
        return None
    
def decrease():
    account_number = get_string("Account Number")
    ok, *data = handle_request("GET_BALANCE", account_number)
    if not ok:
        print(data[0])
        return None
    else:
        original = data[0]
        decrease_amount = get_float("Amount")
        current_amount = float(original) - decrease_amount
        handle_request("CREASE_BALANCE", account_number, current_amount)
        return None

def account():
    account_number = get_string("Account Number")
    ok, *data = handle_request("GET_BALANCE", account_number)
    if not ok:
        print(data[0])
        return None
    else:
        balance = float(data[0])
        ok, *data = handle_request("GET_HOLDER", account_number)
        if ok:
            account_holder, phone_number = data
            print('Account {} Details:\nHolder: {}\nPhone Number: {}\nCurrent Balance: {}'.format(
                account_number, account_holder, phone_number, balance
            ))
            return None


def quit(*ignore):
    sys.exit()


def stop_server(*ignore):
    handle_request("SHUTDOWN", wait_for_reply=False)


def handle_request(*items, wait_for_reply=True):
    # no data processing, only send message to the server
    SizeStruct = struct.Struct("!I")
    data = pickle.dumps(items, 3)

    try:
        with SocketManager(tuple(Address)) as sock:
            # send two messages
            sock.sendall(SizeStruct.pack(len(data)))
            sock.sendall(data)
            if not wait_for_reply:
                return

            size_data = sock.recv(SizeStruct.size)
            size = SizeStruct.unpack(size_data)[0]
            # result is one format of b''
            result = bytearray()
            while True:
                # wait for message from server
                data = sock.recv(4000)
                if not data:
                    break
                result.extend(data)
                if len(result) >= size:
                    break
        # processed data from server (already binary to normal)
        return pickle.loads(result)

    except socket.error as err:
        print("{0}: is the server running?".format(err))
        sys.exit(1)

if __name__ == '__main__':
    main()

下面是一些演示实例：

• 苦逼地调试改bug

• 双开
  

• 在cmd运行服务器，pycharm上运行客户端（左侧是需求书）