import asyncio
import websockets
import json
import struct
import datetime
import logging
import traceback
import mysql.connector
from mysql.connector import pooling
from mysql.connector import errorcode
from typing import Dict, List, Optional
# ======================
# 配置参数
# ======================
CONFIG = {
"AUTH_ENABLED": False, # 是否启用认证
"APP_SECRET": "2d0c7a6f35a74721bdae2b0077735938", # AppSecretKey
"FACTORY": "ztzbStdTag", # 工厂标识
"VALID_TOKEN": { # 有效Token
"appSecret": "BG7DpcHE4oBjsD5x6lkI8KQpfRSWDF6M",
"fullCode": "ztzbStdTagJsonPush"
},
"WEBSOCKET_HOST": "0.0.0.0",
"WEBSOCKET_PORT": 8765,
"MAX_CONNECTIONS": 100,
"DB_WRITE_BATCH_SIZE": 50,
"DB_WRITE_TIMEOUT": 5.0,
# MySQL数据库配置
"MYSQL": {
"HOST": "192.168.191.11",
"PORT": 3306,
"USER": "root",
"PASSWORD": "Adu@123.",
"DATABASE": "CF_HIDB",
"POOL_SIZE": 5,
"POOL_NAME": "sensor_pool",
"POOL_RESET_SESSION": True
}
}
# ======================
# 极简日志设置
# ======================
def setup_logging():
"""配置极简日志系统"""
logger = logging.getLogger("SensorServer")
logger.setLevel(logging.INFO)
# 控制台处理器
console_handler = logging.StreamHandler()
console_handler.setFormatter(logging.Formatter(
'%(asctime)s - %(levelname)s - %(message)s'
))
logger.addHandler(console_handler)
return logger
# 全局日志器
logger = setup_logging()
# ======================
# MySQL数据库管理
# ======================
class MySQLDatabaseManager:
def __init__(self):
self.data_buffer = []
self.last_write_time = datetime.datetime.now()
self.connection_pool = self._create_connection_pool()
self._init_db()
def _create_connection_pool(self) -> pooling.MySQLConnectionPool:
"""创建MySQL连接池"""
try:
return pooling.MySQLConnectionPool(
pool_name=CONFIG["MYSQL"]["POOL_NAME"],
pool_size=CONFIG["MYSQL"]["POOL_SIZE"],
pool_reset_session=CONFIG["MYSQL"]["POOL_RESET_SESSION"],
host=CONFIG["MYSQL"]["HOST"],
port=CONFIG["MYSQL"]["PORT"],
user=CONFIG["MYSQL"]["USER"],
password=CONFIG["MYSQL"]["PASSWORD"],
database=CONFIG["MYSQL"]["DATABASE"]
)
except mysql.connector.Error as err:
logger.error(f"MySQL连接池创建失败: {err}")
raise
def _init_db(self) -> None:
"""初始化数据库表结构"""
try:
conn = self.connection_pool.get_connection()
cursor = conn.cursor()
# 创建设备表
cursor.execute('''
CREATE TABLE IF NOT EXISTS devices (
id INT AUTO_INCREMENT PRIMARY KEY,
device_id VARCHAR(50) NOT NULL UNIQUE,
first_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
''')
# 创建传感器数据表
cursor.execute('''
CREATE TABLE IF NOT EXISTS sensor_data (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
device_id INT NOT NULL,
timestamp TIMESTAMP NOT NULL,
sensor_values JSON NOT NULL,
received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_device_id (device_id),
INDEX idx_timestamp (timestamp),
FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
)
''')
conn.commit()
conn.close()
except mysql.connector.Error as err:
logger.error(f"数据库初始化失败: {err}")
def _get_or_create_device(self, conn, device_id: str) -> int:
"""获取或创建设备记录"""
cursor = conn.cursor()
try:
# 尝试获取设备ID
cursor.execute(
"SELECT id FROM devices WHERE device_id = %s",
(device_id,)
)
device = cursor.fetchone()
if device:
device_id = device[0]
# 更新最后出现时间
cursor.execute(
"UPDATE devices SET last_seen = CURRENT_TIMESTAMP WHERE id = %s",
(device_id,)
)
return device_id
else:
# 创建新设备
cursor.execute(
"INSERT INTO devices (device_id) VALUES (%s)",
(device_id,)
)
return cursor.lastrowid
except mysql.connector.Error as err:
logger.error(f"设备操作失败: {err}")
raise
def insert_sensor_data(self, device_id: str, timestamp: str, sensor_values: List[float]) -> None:
"""将数据添加到缓冲区"""
# 保留小数点后两位
rounded_values = [round(value, 2) for value in sensor_values]
self.data_buffer.append({
'device_id': device_id,
'timestamp': timestamp,
'sensor_values': rounded_values
})
# 检查是否满足批量写入条件
now = datetime.datetime.now()
buffer_full = len(self.data_buffer) >= CONFIG["DB_WRITE_BATCH_SIZE"]
timeout_reached = (now - self.last_write_time).total_seconds() >= CONFIG["DB_WRITE_TIMEOUT"]
if buffer_full or timeout_reached:
self.flush_buffer()
def flush_buffer(self) -> None:
"""将缓冲区数据写入数据库"""
if not self.data_buffer:
return
try:
conn = self.connection_pool.get_connection()
cursor = conn.cursor()
# 批量写入设备数据
device_ids = []
for data in self.data_buffer:
# 获取或创建设备ID
device_db_id = self._get_or_create_device(
conn,
data['device_id']
)
device_ids.append(device_db_id)
# 准备批量插入传感器数据
sensor_data = []
for i, data in enumerate(self.data_buffer):
sensor_data.append((
device_ids[i],
data['timestamp'],
json.dumps(data['sensor_values'])
))
# 批量插入传感器数据
cursor.executemany(
"INSERT INTO sensor_data (device_id, timestamp, sensor_values) "
"VALUES (%s, %s, %s)",
sensor_data
)
conn.commit()
logger.info(f"写入 {len(self.data_buffer)} 条数据")
# 清空缓冲区
self.data_buffer.clear()
self.last_write_time = datetime.datetime.now()
except mysql.connector.Error as err:
if err.errno == errorcode.ER_TRUNCATED_WRONG_VALUE:
# 日期时间错误，尝试修正
logger.warning("检测到日期时间格式错误，尝试修正...")
for data in self.data_buffer:
try:
# 尝试使用当前时间
data['timestamp'] = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
except:
pass
# 重试写入
self.flush_buffer()
else:
logger.error(f"数据库写入失败: {err}")
except Exception as e:
logger.error(f"数据库操作异常: {e}")
finally:
if 'conn' in locals() and conn.is_connected():
conn.close()
# ======================
# 数据包解析
# ======================
def parse_timestamp(timestamp_bytes: bytes) -> str:
"""解析时间戳字节数据为字符串"""
try:
timestamp_str = timestamp_bytes.decode('ascii').strip()
# 提取日期时间部分 (YYYY-MM-DD HH:MM:SS)
return timestamp_str[:19]
except:
# 使用当前时间作为默认值
return datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
def parse_sensor_values(data: bytes, start_index: int) -> List[float]:
"""解析传感器值列表"""
values = []
index = start_index
max_count = (len(data) - index) // 4
for _ in range(max_count):
if index + 4 > len(data):
break
value_bytes = data[index:index + 4]
index += 4
try:
# 尝试小端序解析
value = struct.unpack('<f', value_bytes)[0]
values.append(value)
except:
try:
# 尝试大端序解析
value = struct.unpack('>f', value_bytes)[0]
values.append(value)
except:
# 解析失败，跳过此值
continue
return values
def parse_binary_packet(raw_data: bytes) -> Optional[Dict]:
"""解析二进制数据包"""
try:
# 1. 验证包头
HEADER = b'$\x00\x08\x00\x00\x00'
if not raw_data.startswith(HEADER):
return None
# 2. 解析设备ID
DEVICE_ID_START = len(HEADER)
DEVICE_ID_END = DEVICE_ID_START + 8
device_id_bytes = raw_data[DEVICE_ID_START:DEVICE_ID_END]
# 3. 解析时间戳
TIMESTAMP_START = DEVICE_ID_END
TIMESTAMP_END = TIMESTAMP_START + 19
timestamp_bytes = raw_data[TIMESTAMP_START:TIMESTAMP_END]
# 4. 解析传感器值
SENSOR_DATA_START = TIMESTAMP_END
sensor_values = parse_sensor_values(raw_data, SENSOR_DATA_START)
# 5. 返回解析结果
return {
'device_id': device_id_bytes.decode('ascii', errors='ignore').strip(),
'timestamp': parse_timestamp(timestamp_bytes),
'sensor_values': sensor_values
}
except Exception:
return None
# ======================
# WebSocket 服务器 (带认证)
# ======================
class SensorWebSocketServer:
def __init__(self, host: str, port: int, db_manager: MySQLDatabaseManager):
self.host = host
self.port = port
self.db_manager = db_manager
self.connections = set()
self.server = None
self.authenticated_clients = set() # 已认证的客户端集合
async def handler(self, websocket, path: str) -> None:
"""处理WebSocket连接"""
self.connections.add(websocket)
client_ip = websocket.remote_address[0] if websocket.remote_address else "unknown"
logger.info(f"客户端连接: {client_ip}")
try:
# 如果启用认证，先进行认证
if CONFIG["AUTH_ENABLED"]:
# 等待认证消息
auth_message = await websocket.recv()
try:
auth_data = json.loads(auth_message)
# 验证Token
if (auth_data.get("appSecret") == CONFIG["VALID_TOKEN"]["appSecret"] and
auth_data.get("fullCode") == CONFIG["VALID_TOKEN"]["fullCode"]):
self.authenticated_clients.add(websocket)
await websocket.send("AuthSuccess")
logger.info(f"客户端认证成功: {client_ip}")
else:
await websocket.send("AuthFailed: Invalid token")
logger.warning(f"客户端认证失败: {client_ip}")
return
except:
await websocket.send("AuthFailed: Invalid format")
logger.warning(f"无效的认证消息: {client_ip}")
return
# 处理数据消息
async for message in websocket:
if not isinstance(message, bytes):
continue
# 如果启用认证但客户端未认证，跳过处理
if CONFIG["AUTH_ENABLED"] and websocket not in self.authenticated_clients:
continue
parsed_data = parse_binary_packet(message)
if parsed_data:
# 存储到数据库
self.db_manager.insert_sensor_data(
parsed_data['device_id'],
parsed_data['timestamp'],
parsed_data['sensor_values']
)
except websockets.exceptions.ConnectionClosed:
logger.info(f"客户端断开: {client_ip}")
except Exception as e:
logger.error(f"处理客户端时出错: {e}")
finally:
if websocket in self.connections:
self.connections.remove(websocket)
if websocket in self.authenticated_clients:
self.authenticated_clients.remove(websocket)
async def start(self) -> None:
"""启动WebSocket服务器"""
self.server = await websockets.serve(
self.handler,
self.host,
self.port,
max_size=2 ** 20, # 1MB
ping_interval=60,
ping_timeout=30,
close_timeout=10,
max_queue=CONFIG["MAX_CONNECTIONS"]
)
logger.info(f"服务器启动: ws://{self.host}:{self.port}")
logger.info(f"认证状态: {'启用' if CONFIG['AUTH_ENABLED'] else '禁用'}")
async def stop(self) -> None:
"""停止WebSocket服务器"""
if self.server:
self.server.close()
await self.server.wait_closed()
logger.info("服务器已停止")
# ======================
# 主程序
# ======================
async def main():
# 初始化MySQL数据库管理器
db_manager = MySQLDatabaseManager()
# 创建WebSocket服务器
server = SensorWebSocketServer(
CONFIG["WEBSOCKET_HOST"],
CONFIG["WEBSOCKET_PORT"],
db_manager
)
# 启动服务器
await server.start()
try:
# 运行主循环
while True:
await asyncio.sleep(5)
# 定期刷新数据库缓冲区
db_manager.flush_buffer()
except asyncio.CancelledError:
pass
except KeyboardInterrupt:
logger.info("收到停止信号")
finally:
# 确保所有缓冲数据都写入数据库
db_manager.flush_buffer()
await server.stop()
# 启动程序
if __name__ == "__main__":
logger.info("=" * 40)
logger.info("传感器数据采集服务器启动")
logger.info(f"时间: {datetime.datetime.now()}")
logger.info(f"MySQL主机: {CONFIG['MYSQL']['HOST']}:{CONFIG['MYSQL']['PORT']}")
logger.info(f"数据库: {CONFIG['MYSQL']['DATABASE']}")
logger.info("=" * 40)
try:
asyncio.run(main())
except Exception as e:
logger.error(f"服务器异常停止: {e}")
finally:
logger.info("=" * 40)
logger.info("服务器已停止运行")
logger.info("=" * 40)
部署到NAS的docker里
本文解析了新版mqtt物联网套件中鉴权信息的变更，强调了从传统APIKEY到使用token加密传输的安全升级，并提供了token计算工具参数说明。
扫一扫
12
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
