运行import struct
import asyncio
import websockets
import mysql.connector
from mysql.connector import pooling
from dotenv import load_dotenv
import os
from datetime import datetime
import logging
import json
# 加载环境变量
load_dotenv()
# 配置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s [%(levelname)s] %(message)s')
# 数据库连接池配置
db_pool = pooling.MySQLConnectionPool(
pool_name="ws_pool",
pool_size=5,
host=os.getenv('DB_HOST'),
port=os.getenv('DB_PORT', 3306),
user=os.getenv('DB_USER'),
password=os.getenv('DB_PASSWORD'),
database=os.getenv('DB_NAME')
)
# 数据类型映射表
DATA_TYPES = {
0: 'Float',
1: 'Integer',
2: 'Boolean',
3: 'String',
4: 'Timestamp'
}
def create_tables():
"""创建数据库表结构"""
try:
conn = db_pool.get_connection()
cursor = conn.cursor()
# 创建数据表
cursor.execute("""
CREATE TABLE IF NOT EXISTS sensor_data (
id INT AUTO_INCREMENT PRIMARY KEY,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
device_id VARCHAR(50) NOT NULL,
tag_id INT NOT NULL,
value FLOAT,
raw_data JSON,
INDEX (device_id),
INDEX (tag_id),
INDEX (timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
""")
# 创建标签映射表
cursor.execute("""
CREATE TABLE IF NOT EXISTS tag_mapping (
tag_id INT PRIMARY KEY,
tag_name VARCHAR(255) NOT NULL,
data_type ENUM('Float', 'Short', 'Boolean', 'String', 'Date') NOT NULL,
description TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
""")
conn.commit()
logging.info("数据库表创建成功")
except mysql.connector.Error as err:
logging.error(f"数据库表创建失败: {err}")
finally:
if conn.is_connected():
cursor.close()
conn.close()
def parse_binary_data(binary_data):
"""解析二进制数据为结构化数据"""
try:
# 解析头部信息
device_id = struct.unpack('>I', binary_data[0:4])[0]
timestamp = struct.unpack('>Q', binary_data[4:12])[0] / 1000.0 # 毫秒转秒
num_points = struct.unpack('>H', binary_data[12:14])[0]
# 将时间戳转为ISO格式
dt = datetime.fromtimestamp(timestamp)
iso_timestamp = dt.isoformat()
# 解析数据点
data_points = []
offset = 14
for i in range(num_points):
tag_id = struct.unpack('>H', binary_data[offset:offset + 2])[0]
data_type = DATA_TYPES.get(binary_data[offset + 2], 'Unknown')
offset += 3
# 根据数据类型解析值
if data_type == 'Float':
value = struct.unpack('>f', binary_data[offset:offset + 4])[0]
elif data_type == 'Integer':
value = struct.unpack('>i', binary_data[offset:offset + 4])[0]
elif data_type == 'Boolean':
value = bool(struct.unpack('>I', binary_data[offset:offset + 4])[0])
elif data_type == 'String':
str_len = struct.unpack('>I', binary_data[offset:offset + 4])[0]
offset += 4
value = binary_data[offset:offset + str_len].decode('utf-8')
offset += str_len
continue # 跳过下面的偏移量增加
elif data_type == 'Timestamp':
value = struct.unpack('>Q', binary_data[offset:offset + 8])[0] / 1000.0
offset += 8
continue # 跳过下面的偏移量增加
offset += 4
data_points.append({
'tag_id': tag_id,
'data_type': data_type,
'value': value
})
return {
'device_id': f"device_{device_id}",
'timestamp': iso_timestamp,
'data': data_points
}
except Exception as e:
logging.error(f"二进制数据解析失败: {str(e)}")
return None
def save_to_database(data):
"""将解析后的数据保存到MySQL数据库"""
try:
conn = db_pool.get_connection()
cursor = conn.cursor()
# 插入主记录
insert_main = """
INSERT INTO sensor_data
(device_id, timestamp, raw_data)
VALUES (%s, %s, %s)
"""
cursor.execute(insert_main, (
data['device_id'],
data['timestamp'],
json.dumps(data) # 存储原始JSON数据
))
data_id = cursor.lastrowid
# 插入数据点
for item in data['data']:
insert_detail = """
INSERT INTO sensor_data_details
(data_id, tag_id, data_type, value)
VALUES (%s, %s, %s, %s)
"""
cursor.execute(insert_detail, (
data_id,
item['tag_id'],
item['data_type'],
str(item['value'])
))
conn.commit()
logging.info(f"成功存储设备 {data['device_id']} 的 {len(data['data'])} 条数据")
except mysql.connector.Error as err:
logging.error(f"数据库存储失败: {err}")
conn.rollback()
finally:
if conn.is_connected():
cursor.close()
conn.close()
async def handle_client(websocket, path):
"""处理WebSocket客户端连接"""
client_ip = websocket.remote_address[0]
logging.info(f"客户端 {client_ip} 已连接")
try:
async for message in websocket:
try:
# 处理二进制数据
if isinstance(message, bytes):
logging.info(f"收到来自 {client_ip} 的二进制数据 ({len(message)} 字节)")
# 解析二进制数据
parsed_data = parse_binary_data(message)
if not parsed_data:
error_response = b"\x00\x01" # 错误代码 1: 解析失败
await websocket.send(error_response)
continue
# 存储到数据库
save_to_database(parsed_data)
# 发送成功响应 (二进制确认)
success_response = b"\x00\x00" # 成功代码 0
await websocket.send(success_response)
# 处理文本数据 (可选)
else:
# 原有文本处理逻辑...
pass
except Exception as e:
logging.error(f"处理数据时出错: {e}")
error_response = b"\x00\x02" # 错误代码 2: 内部错误
await websocket.send(error_response)
except websockets.exceptions.ConnectionClosedError:
logging.info(f"客户端 {client_ip} 断开连接")
# 其他函数不变 (create_tables, main等)
async def main():
"""启动WebSocket服务器"""
create_tables()
host = os.getenv('WS_HOST', '0.0.0.0')
port = int(os.getenv('WS_PORT', 8765))
async with websockets.serve(
handle_client,
host,
port,
ping_interval=30, # 30秒心跳检测
ping_timeout=60 # 60秒无响应断开
):
logging.info(f"WebSocket服务器启动，监听 {host}:{port}")
await asyncio.Future() # 永久运行
if __name__ == "__main__":
asyncio.run(main())
后，用客户端import asyncio
import websockets
import struct
import time
from datetime import datetime
async def send_binary_data():
uri = "ws://localhost:8765"
async with websockets.connect(uri) as websocket:
# 构造二进制数据
device_id = 1001
timestamp = int(time.time() * 1000) # 毫秒级时间戳
num_points = 3
# 数据点1: 温度 (Float)
tag1 = 101
type1 = 0 # Float
value1 = 23.45
# 数据点2: 状态 (Boolean)
tag2 = 102
type2 = 2 # Boolean
value2 = 1 # True
# 数据点3: 消息 (String)
tag3 = 103
type3 = 3 # String
message = "设备正常运行"
# 构建二进制消息
binary_data = struct.pack('>I', device_id) # 设备ID (4字节)
binary_data += struct.pack('>Q', timestamp) # 时间戳 (8字节)
binary_data += struct.pack('>H', num_points) # 数据点数 (2字节)
# 数据点1
binary_data += struct.pack('>H', tag1) # 标签ID (2字节)
binary_data += struct.pack('>B', type1) # 数据类型 (1字节)
binary_data += struct.pack('>f', value1) # 值 (4字节)
# 数据点2
binary_data += struct.pack('>H', tag2)
binary_data += struct.pack('>B', type2)
binary_data += struct.pack('>I', value2) # 布尔值用4字节表示
# 数据点3 (字符串)
binary_data += struct.pack('>H', tag3)
binary_data += struct.pack('>B', type3)
binary_data += struct.pack('>I', len(message)) # 字符串长度 (4字节)
binary_data += message.encode('utf-8') # 字符串内容
# 发送二进制数据
await websocket.send(binary_data)
print(f"发送二进制数据 ({len(binary_data)} 字节)")
# 接收响应
response = await websocket.recv()
if response == b"\x00\x00":
print("收到成功响应")
else:
error_code = struct.unpack('>H', response)[0]
print(f"收到错误响应，代码: {error_code}")
if __name__ == "__main__":
asyncio.get_event_loop().run_until_complete(send_binary_data())
验证，报错2025-06-16 15:13:59,009 [ERROR] 数据库存储失败: 1364 (HY000): Field 'tag_id' doesn't have a default value
最新发布
本文介绍了使用C++操作MySQL时如何输出错误码和错误描述，并提供了两种解决因STRICT_TRANS_TABLES导致的问题的方法：一是为字段设置默认值；二是通过配置或SQL命令修改SQL模式。
扫一扫
2853
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
