<think>我们参考用户提供的引用[1]中的建表语句，但根据需求调整字段。用户需要存储的字段包括：device_id,timestamp,raw_data（存储解析后的JSON数据）因此，我们创建如下表结构：建表SQL（参考引用[1]的格式）：CREATETABLEsensor_data(idINTAUTO_INCREMENTPRIMARYKEY,device_idVARCHAR(50)NOTNULL,timestampDATETIMENOTNULL,raw_dataJSON,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP);接下来，我们使用Python的mysql.connector库来实现数据存储。步骤：1.导入必要的库（mysql.connector,json）2.建立数据库连接（使用连接池或直接连接，这里为了简单先使用直接连接）3.将解析后的数据（字典）转换为JSON字符串，用于存储到raw_data字段4.执行INSERT语句注意：timestamp字段在数据库中为DATETIME类型，而我们从解析数据中得到的是ISO格式的字符串（如'2025-06-14T20:47:15'），可以直接作为DATETIME插入。代码实现：</think># Python实现解析数据存储到MySQL数据库
下面我将详细说明如何使用Python将解析后的数据存储到MySQL数据库，包括`device_id`、`timestamp`和`raw_data`字段的具体实现方法。
## 数据库表结构设计
参考引用[1]中的表结构设计，我们需要创建包含所需字段的表：
```sql
CREATE TABLE sensor_data (
id INT AUTO_INCREMENT PRIMARY KEY,
device_id VARCHAR(100) NOT NULL,
timestamp DATETIME NOT NULL,
raw_data JSON,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX (device_id),
INDEX (timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```
## Python实现代码
### 1. 数据库连接配置
首先配置数据库连接信息：
```python
import mysql.connector
from mysql.connector import Error
import json
from datetime import datetime
# 数据库配置
DB_CONFIG = {
'host': 'localhost',
'database': 'your_database',
'user': 'your_username',
'password': 'your_password',
'port': 3306
}
```
### 2. 数据库连接函数
创建可重用的数据库连接函数：
```python
def create_connection():
"""创建数据库连接"""
try:
conn = mysql.connector.connect(**DB_CONFIG)
if conn.is_connected():
return conn
except Error as e:
print(f"数据库连接错误: {e}")
return None
```
### 3. 数据插入函数
实现将解析数据插入数据库的核心功能：
```python
def insert_sensor_data(device_id, timestamp, raw_data):
"""将传感器数据插入数据库"""
connection = None
try:
connection = create_connection()
if connection:
cursor = connection.cursor()
# 准备SQL语句
sql = """
INSERT INTO sensor_data (device_id, timestamp, raw_data)
VALUES (%s, %s, %s)
"""
# 准备数据
# 将timestamp转换为datetime对象
if isinstance(timestamp, str):
dt = datetime.fromisoformat(timestamp)
elif isinstance(timestamp, datetime):
dt = timestamp
else:
raise ValueError("无效的时间戳格式")
# 将raw_data转换为JSON字符串
if isinstance(raw_data, dict):
json_data = json.dumps(raw_data)
elif isinstance(raw_data, str):
json_data = raw_data
else:
raise ValueError("raw_data必须是字典或JSON字符串")
# 执行插入
cursor.execute(sql, (device_id, dt, json_data))
connection.commit()
print(f"成功插入数据: {device_id} - {timestamp}")
return cursor.rowcount
except Error as e:
print(f"数据库操作错误: {e}")
if connection:
connection.rollback()
finally:
if connection and connection.is_connected():
cursor.close()
connection.close()
return 0
```
### 4. 使用示例
```python
# 示例数据
sample_data = {
"device_id": "device_1001",
"timestamp": "2025-06-14T20:47:15",
"sensor_values": {
"temperature": 23.45,
"humidity": 56.78,
"status": "active"
}
}
# 插入数据
inserted = insert_sensor_data(
device_id=sample_data["device_id"],
timestamp=sample_data["timestamp"],
raw_data=sample_data["sensor_values"]
)
print(f"插入记录数: {inserted}")
```
## 高级功能扩展
### 1. 批量插入优化
对于大量数据，使用批量插入提高性能：
```python
def insert_batch_sensor_data(data_list):
"""批量插入传感器数据"""
connection = None
try:
connection = create_connection()
if connection:
cursor = connection.cursor()
# 准备SQL语句
sql = """
INSERT INTO sensor_data (device_id, timestamp, raw_data)
VALUES (%s, %s, %s)
"""
# 准备数据
values = []
for data in data_list:
dt = data["timestamp"] if isinstance(data["timestamp"], datetime) else datetime.fromisoformat(data["timestamp"])
json_data = json.dumps(data["raw_data"]) if isinstance(data["raw_data"], dict) else data["raw_data"]
values.append((data["device_id"], dt, json_data))
# 执行批量插入
cursor.executemany(sql, values)
connection.commit()
print(f"成功批量插入 {len(data_list)} 条记录")
return cursor.rowcount
except Error as e:
print(f"批量插入错误: {e}")
if connection:
connection.rollback()
finally:
if connection and connection.is_connected():
cursor.close()
connection.close()
return 0
```
### 2. 使用连接池提高性能
参考引用[2]中的连接管理思想，使用连接池：
```python
from mysql.connector import pooling
# 创建连接池
db_pool = pooling.MySQLConnectionPool(
pool_name="sensor_pool",
pool_size=5,
**DB_CONFIG
)
def insert_with_pool(device_id, timestamp, raw_data):
"""使用连接池插入数据"""
try:
conn = db_pool.get_connection()
cursor = conn.cursor()
# 准备SQL和数据
sql = "INSERT INTO sensor_data (device_id, timestamp, raw_data) VALUES (%s, %s, %s)"
dt = datetime.fromisoformat(timestamp)
json_data = json.dumps(raw_data)
# 执行插入
cursor.execute(sql, (device_id, dt, json_data))
conn.commit()
print(f"使用连接池插入成功: {device_id}")
return True
except Error as e:
print(f"连接池插入错误: {e}")
if conn:
conn.rollback()
return False
finally:
if conn.is_connected():
cursor.close()
conn.close()
```
### 3. 数据验证
参考引用[3]中的数据验证思想，添加数据验证：
```python
def validate_sensor_data(device_id, timestamp, raw_data):
"""验证传感器数据有效性"""
# 验证设备ID格式
if not device_id.startswith("device_") or not device_id[7:].isdigit():
raise ValueError("设备ID格式无效")
# 验证时间戳
try:
dt = datetime.fromisoformat(timestamp)
if dt > datetime.now():
raise ValueError("时间戳不能是未来时间")
except ValueError:
raise ValueError("时间戳格式无效")
# 验证原始数据
if not isinstance(raw_data, dict):
raise ValueError("raw_data必须是字典类型")
# 验证数据字段
required_fields = ['temperature', 'humidity', 'status']
for field in required_fields:
if field not in raw_data:
raise ValueError(f"缺少必需字段: {field}")
return True
```
## 使用示例扩展
```python
# 验证并插入数据
try:
validate_sensor_data(
device_id="device_1001",
timestamp="2025-06-14T20:47:15",
raw_data={
"temperature": 23.45,
"humidity": 56.78,
"status": "active"
}
)
insert_sensor_data(
device_id="device_1001",
timestamp="2025-06-14T20:47:15",
raw_data={
"temperature": 23.45,
"humidity": 56.78,
"status": "active"
}
)
except ValueError as e:
print(f"数据验证失败: {e}")
```
## 性能优化建议
1. **批量处理**：使用`executemany`批量插入数据，减少数据库交互次数[^1]
2. **连接池**：使用连接池管理数据库连接，避免频繁创建和销毁连接
3. **异步处理**：对于高并发场景，使用异步IO处理数据库操作
4. **索引优化**：确保在`device_id`和`timestamp`字段上有适当索引
5. **数据压缩**：对于大型JSON数据，可以在插入前进行压缩
## 常见问题解决
1. **时间戳格式问题**：
- 使用`datetime.fromisoformat()`处理ISO格式时间戳
- 确保MySQL服务器和客户端时区设置一致
2. **JSON处理问题**：
- 使用`json.dumps()`将Python字典转换为JSON字符串
- MySQL 5.7+支持JSON数据类型，可以充分利用JSON函数查询数据
3. **连接管理**：
- 使用`try-finally`确保数据库连接正确关闭
- 使用连接池管理连接，避免连接泄露
4. **字符编码问题**：
- 设置数据库字符集为`utf8mb4`以支持所有Unicode字符
- 在连接字符串中指定`charset='utf8mb4'`
这个实现方案提供了从数据验证、数据库连接到实际插入的完整流程，可以根据具体需求进行调整和扩展[^1]。
扫一扫
650
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
