当Python连接MySQL数据库遇到报错时,可以按照以下步骤进行排查和解决:
ModuleNotFoundError: No module named 'MySQLdb'
解决方法:
pip install mysqlclient # 推荐
# 或
pip install pymysql
OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 61] Connection refused)")
可能原因: - MySQL服务未运行 - 连接参数错误 - 防火墙阻止
解决方法:
# 检查MySQL服务状态
sudo service mysql status
# 或
systemctl status mysqld
# 启动服务
sudo service mysql start
OperationalError: (1045, "Access denied for user 'username'@'localhost' (using password: YES)")
解决方法: - 确认用户名和密码正确 - 检查用户是否有远程访问权限 - 可能需要重置MySQL密码
OperationalError: (1049, "Unknown database 'database_name'")
解决方法:
- 确认数据库名称拼写正确
- 创建数据库:CREATE DATABASE database_name;
import pymysql
try:
connection = pymysql.connect(
host='localhost',
user='your_username',
password='your_password',
database='your_database',
port=3306,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
with connection.cursor() as cursor:
# 执行SQL查询
cursor.execute("SELECT VERSION()")
result = cursor.fetchone()
print(f"Database version: {result}")
except pymysql.Error as e:
print(f"Error connecting to MySQL: {e}")
finally:
if connection:
connection.close()
检查MySQL服务器日志:
sudo tail -f /var/log/mysql/error.log
测试远程连接:
mysql -h hostname -u username -p
检查端口是否开放:
telnet hostname 3306
# 或
nc -zv hostname 3306
检查用户权限:
SELECT host, user FROM mysql.user;
SHOW GRANTS FOR 'username'@'host';
临时关闭防火墙测试:
sudo ufw disable # Ubuntu
# 或
sudo systemctl stop firewalld # CentOS
如果问题仍未解决,请提供具体的错误信息,我可以给出更有针对性的解决方案。