解决Python连接MySQL8,命令行运行时错误pymysql.err.OperationalError: 1045问题
1、MySQL8.0之后的密码验证插件为caching_sha2_password,以前为mysql_native_password。
2、在PyCharm中python连接MySQL时以上两种加密方式都可以用,但是在命令提示符中运行出现如下错误:
pymysql.err.OperationalError: (1045, "Access denied for user 'bill'@'localhost' (using password: NO)")
3、以root用户登录,修改用户bill的密码插件为mysql_native_password,并重置密码
mysql -u root -p
mysql> use mysql
mysql> update user set plugin='mysql_native_password',authentication_string='' where user='bill';
mysql> flush privileges;
mysql> quit
4、以用户mat登录,设置新密码
mysql -u bill -p # 不需要密码
mysql> alter user 'bill'@'localhost' identified by '123456'; # 修改密码
mysql> use mysql
mysql> select user,plugin,authentication_string from user where user='bill';
+------------------+-----------------------+---------------------------------------------------------+
| user | plugin | authentication_string |
+------------------+-----------------------+---------------------------------------------------------+
| bill | mysql_native_password | *BC883384879877A476D478FCC3M563A952DF750E |
+------------------+-----------------------+---------------------------------------------------------+
mysql> quit
5、此时python脚本在PyCharm中运行正常,但在命令行运行出现以下错误代码:
UnicodeEncodeError: 'latin-1' codec can't encode characters in position 24-25: ordinal not in range(256)
这是因为MySQLdb正常情况下会尝试将所有的内容转为latin-1字符集处理,但明显有字符无法编码成latin-1
解决办法如下:
db = pymysql.connect(host='localhost', user='bill', password='123456', port=3306, db='test')
db.set_charset('utf8mb4') # 添加此行即可运行
cursor = db.cursor()
# 如果不能运行,尝试添加以下代码
#cursor.execute('SET NAMES utf8mb4;')
#cursor.execute('SET CHARACTER SET utf8mb4;')
#cursor.execute('SET character_set_connection=utf8mb4;')