<think>嗯，用户之前让我帮忙设计一个天气预报数据采集程序，用Python编写，包括数据存储到数据库。现在他问的是如何使用mysql.connector库连接和操作MySQL数据库，将数据导入其中。之前我给的例子用的是SQLite，现在需要切换到MySQL。
首先，我需要确认用户的需求。他们可能已经熟悉了SQLite，但现在项目需要升级到MySQL，因为MySQL更适合生产环境，支持更复杂的操作和更高的并发。用户可能已经完成了数据采集和清洗部分，现在需要将存储模块从SQLite迁移到MySQL。
接下来，我应该考虑用户可能遇到的问题。比如，如何安装mysql.connector，连接字符串的参数，MySQL和SQLite在语法上的差异，比如AUTO_INCREMENT在MySQL中的使用，以及数据类型的不同，比如DATETIME和VARCHAR的长度。还有权限问题，用户是否有权限在MySQL中创建数据库和表。
然后，我需要提供清晰的步骤。首先是安装mysql.connector库，使用pip安装。然后修改数据库连接部分的代码，使用mysql.connector.connect，并提供正确的host、user、password、database参数。注意可能需要先创建数据库，比如用CREATE DATABASE IF NOT EXISTS。
然后，表结构可能需要调整，比如将AUTOINCREMENT改为AUTO_INCREMENT，并指定ENGINE=InnoDB等。在插入数据时，注意占位符的变化，SQLite使用?，而MySQL使用%s，虽然mysql.connector也支持?，但为了统一，示例中使用%s更常见。
另外，异常处理也很重要，需要捕获mysql.connector的Error，并在出现问题时回滚事务。用户可能还需要考虑数据库的持久连接，或者使用连接池，但在简单示例中可能不需要。
此外，用户可能需要设置权限，确保MySQL用户有创建表和插入数据的权限。如果遇到访问被拒绝的错误，需要检查用户的host和权限设置。
最后，扩展部分可以提到使用ORM如SQLAlchemy，或者异步操作，但用户当前的问题比较基础，所以保持示例简单明了，直接回答如何连接和操作即可。确保代码示例正确，并测试过，避免拼写错误，比如端口号3306是否正确，以及是否需要SSL连接等。</think>我将为您详细说明使用Python的`mysql.connector`库操作MySQL数据库的全流程，包含代码实现和关键技术要点：
---
### 一、环境准备
```bash
# 安装必要库
pip install mysql-connector-python
```
---
### 二、数据库连接配置
```python
import mysql.connector
from mysql.connector import Error
DB_CONFIG = {
"host": "localhost",
"user": "your_username",
"password": "your_password",
"database": "weather_db",
"port": 3306,
"charset": "utf8mb4"
}
```
---
### 三、核心操作实现
#### 1. 创建数据库表
```python
def create_table():
create_table_sql = """
CREATE TABLE IF NOT EXISTS weather_data (
id INT AUTO_INCREMENT PRIMARY KEY,
timestamp DATETIME NOT NULL,
source VARCHAR(20) NOT NULL,
temperature FLOAT,
humidity INT,
wind_speed FLOAT,
pressure INT,
rainfall FLOAT,
INDEX timestamp_index (timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
try:
conn = mysql.connector.connect(**DB_CONFIG)
cursor = conn.cursor()
cursor.execute(create_table_sql)
print("表创建成功")
except Error as e:
print(f"数据库错误: {e}")
finally:
if conn.is_connected():
cursor.close()
conn.close()
```
#### 2. 数据插入操作（带防注入示例）
```python
def insert_data(data):
insert_sql = """
INSERT INTO weather_data
(timestamp, source, temperature, humidity, wind_speed, pressure, rainfall)
VALUES (%s, %s, %s, %s, %s, %s, %s)
"""
try:
conn = mysql.connector.connect(**DB_CONFIG)
cursor = conn.cursor()
# 执行参数化查询
cursor.execute(insert_sql, (
data['timestamp'],
data['source'],
data['temperature'],
data['humidity'],
data['wind_speed'],
data['pressure'],
data.get('rainfall', 0)
))
conn.commit()
print(f"插入成功，ID: {cursor.lastrowid}")
except Error as e:
print(f"插入失败: {e}")
conn.rollback()
finally:
if conn.is_connected():
cursor.close()
conn.close()
```
---
### 四、高级功能实现
#### 1. 批量插入优化
```python
def bulk_insert(data_list):
insert_sql = """
INSERT INTO weather_data
(timestamp, source, temperature, humidity, wind_speed, pressure, rainfall)
VALUES (%s, %s, %s, %s, %s, %s, %s)
"""
try:
conn = mysql.connector.connect(**DB_CONFIG)
cursor = conn.cursor()
# 转换为元组列表
values = [(
d['timestamp'], d['source'], d['temperature'],
d['humidity'], d['wind_speed'], d['pressure'],
d.get('rainfall', 0)
) for d in data_list]
cursor.executemany(insert_sql, values)
conn.commit()
print(f"批量插入 {cursor.rowcount} 条数据")
except Error as e:
print(f"批量插入失败: {e}")
conn.rollback()
finally:
if conn.is_connected():
cursor.close()
conn.close()
```
---
### 五、连接池管理（生产环境推荐）
```python
from mysql.connector import pooling
# 创建连接池
db_pool = pooling.MySQLConnectionPool(
pool_name="weather_pool",
pool_size=5,
**DB_CONFIG
)
def get_connection():
return db_pool.get_connection()
```
---
### 六、常见问题解决方案
1. **中文乱码问题**
确保数据库、表和连接都使用`utf8mb4`字符集
2. **连接超时处理**
```python
DB_CONFIG["connection_timeout"] = 10 # 设置超时时间
```
3. **SSL连接配置**
```python
DB_CONFIG.update({
"ssl_ca": "/path/to/server-ca.pem",
"ssl_cert": "/path/to/client-cert.pem",
"ssl_key": "/path/to/client-key.pem"
})
```
---
### 七、操作验证示例
```python
# 测试数据样例
test_data = {
"timestamp": "2023-08-20 14:00:00",
"source": "openweathermap",
"temperature": 26.5,
"humidity": 65,
"wind_speed": 3.2,
"pressure": 1013,
"rainfall": 0.5
}
# 执行测试
create_table()
insert_data(test_data)
```
---
### 八、性能优化建议
1. 使用`executemany()`进行批量插入时，每批数据量控制在500-1000条
2. 高频操作使用连接池管理
3. 对时间戳字段建立索引
4. 启用数据库的查询缓存功能
5. 定期执行`ANALYZE TABLE`优化统计信息
---
如果需要更详细的MySQL配置说明或遇到具体问题（如远程连接配置、存储过程调用等），可以告知具体需求方向。
在MySQL-connector升级到8版本后，由于时间戳精度提升，导致与MySQL5.7数据库中秒级时间比较时出现问题，影响了应用中基于Timestamp的缓存刷新机制。通过对比测试和日志分析，发现Quartz定时任务中的时间判断逻辑不再适用，因为数据库中的lastUpdTime始终小于 connector 捕获的毫秒级nextQryTimeBaseLine，从而无法正确刷新缓存。解决方案是调整业务逻辑，避免依赖于特定时间比较来刷新缓存。
最低0.47元/天 解锁文章
扫一扫
1183
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
