py操作MySQL以及解决乱码问题的记录

写在前面

做py操作MySQL的原因是自己做着玩的项目需要：wepy微信机器人：向好友推送爬取的公告，里面涉及到保存用户的信息，以前图简单，直接放入一个列表内，一旦想优化一下代码，程序重启，用户信息就没了。所以就想着写个数据库保存用户的信息。以前也使用过一些语言操作MySQL：PHP、Java等。其实都一样，所以py上手也很快。

py操作MySQL

注：本小节内容几乎完全引用的此博客：python-入门教程（操作mysql数据库），但是自己还是写一写

pymysql安装

1. cmd内直接pip install pymysql
2. pyCharm内引入时Alt+Enter安装易可

代码示例

关于库也没啥好介绍的，直接上代码，下面是增删改查的示例，看了就知道库的用法了。唯一不熟悉的可能就是SQL语句了，但这也跟python无关，想要找对应的SQL语句可以去对应百度一下，但下面几条应该够用了。

查询操作

import pymysql  #导入 pymysql

#打开数据库连接,原博客端口是3307
db= pymysql.connect(host="localhost",user="root",password="123456",db="test",port=3306)

# 使用cursor()方法获取操作游标
cur = db.cursor()

#1.查询操作
# 编写sql 查询语句  user 对应我的表名
sql = "select * from user"
try:
    cur.execute(sql)     #执行sql语句

    results = cur.fetchall()    #获取查询的所有记录
    print("id","name","password")
    #遍历结果
    for row in results :
        id = row[0]
        name = row[1]
        password = row[2]
        print(id,name,password)
except Exception as e:
    raise e
finally:
    db.close()    #关闭连接

插入操作

注：我个人习惯，一般把id设置为主键并且自动递增，所以我的SQL语句里面不会指定id。

import pymysql
#2.插入操作
db= pymysql.connect(host="localhost",user="root",password="123456",db="test",port=3306)

# 使用cursor()方法获取操作游标
cur = db.cursor()

sql_insert ="insert into user(id,username,password) values(4,'liu','1234')"

try:
    cur.execute(sql_insert)
    #提交，这句执行才能在数据库内看到记录
    db.commit()
except Exception as e:
    #错误回滚
    db.rollback() 
finally:
    db.close()

更新操作

更新操作
db= pymysql.connect(host="localhost",user="root",password="123456",db="test",port=3306)

# 使用cursor()方法获取操作游标
cur = db.cursor()

sql_update ="update user set username = '%s' where id = %d"

try:
    cur.execute(sql_update % ("xiongda",3))  #像sql语句传递参数
    #提交
    db.commit()
except Exception as e:
    #错误回滚
    db.rollback() 
finally:
    db.close()

删除

import pymysql
#4.删除操作
db= pymysql.connect(host="localhost",user="root",password="123456",db="test",port=3306)

# 使用cursor()方法获取操作游标
cur = db.cursor()

sql_delete ="delete from user where id = %d"

try:
    cur.execute(sql_delete % (3))  #像sql语句传递参数
    #提交
    db.commit()
except Exception as e:
    #错误回滚
    db.rollback() 
finally:
    db.close()

连接远程数据库

一开始直接把"localhost"改成服务器的IP，报错了。

db= pymysql.connect(host="localhost",user="root",password="123456",db="test",port=3306)

可能的问题有两个：
1.服务器防火墙拦截请求：先去服务器官网的控制台设置安全组开放端口；这种情况有可能还是访问不了，网上说还要去服务器上去设置防火墙的进、出站规则，我为了省事，直接就把防火墙关了。
2.数据库权限问题：数据库默认设置是只能"localhost"访问的，可以打开数据库“mysql”，打开数据表“user”查看用户权限（如下）

解决方法也有多种，比如添加新的远程的用户。较简单的方法就是把root用户的host从"localhost"改为"%"，代表ALL，接受任意IP的请求。在命令行内执行操作：

mysql -u root -p 
//打开数据库“mysql”
mysql>use mysql;
//执行SQL语句，进行替换
mysql>update user set host = '%' where user = 'root';
//看下结果
mysql>select host, user from user;

这样应该就没问题了。

记录： 乱码问题解决！！！

起因（废话请跳过）：

白天代码要爬公告不敢停，专门抽晚上的时间升级代码。整个代码写好并且在电脑上调试好，大概4点了，心满意足的准备部署到服务器睡觉。嘿嘿，服务器上调试的时候，突然出现了下图这样一条数据（id+用户名）：中文乱码！而且这个用户已经在数据库内了（因为中文乱码，用户重复了），人傻了。

然后去网上找解决方法，几乎都找遍了，都没用！一直调到了早上7点半，还是没解决。10点要上班，就把先前的用户信息写到一个固定的列表内，写了一个低配版本的脚本，白天先运行。赶紧睡了两小时，好在当天晚上最终解决了问题。
我的问题总结：

1. 服务器MySQL编码有问题
2. 服务器navicat编码有问题

乱码的来源

先归纳，乱码的来源就三个地方：

1. 代码内提交数据编码问题
2. 数据库编码
3. navicat编码问题！！

1.代码内的编码问题
可以参考下面两篇博客：（我没有这个问题）
Python读取mysql数据库数据出现中文乱码
(冲突)Python中使用pymysql插入中文数据问题
2.数据库编码问题
我最开始检查了我服务器mysql的编码，确实有问题。MySQL的默认编码并不是utf-8。
可以看下面这篇博客：
mysql字符编码的设置以及mysql中文乱码的解决方法
还有个简单暴力的方法是直接删了重装：附一个菜鸟教程的安装教程。重装还是比较快的：
MySQL安装
3.navicat编码问题
其实网上基本上是对前两个问题的解答，我几乎所有操作都搞了好几遍，数据库也删了重装了n次，服务器的navicat内还是几个？？？。最后我干脆控制变量，一个个试，用了本地的程序，本地的navicat，只有MySQL用的是服务器的，结果发现：没有问题！！！。然后我分别用服务器的navicat和本地的navicat连接MySQL，发现效果如下：

所以此时其实代码和数据库都是没问题的，其实只是navicat显示的问题，完全不影响业务。（暴风哭泣.jpg）
那么怎么解决这个显示问题呢？
在navicat连接属性内=>高级=>编码，选择“自动”即可。

写在后面

记录学习，欢迎交流，多多指教