python 连接远程数据库

2020-01-09
总结自菜鸟教程
以下所有操作基于pymysql库

数据库连接

使用pymysql.connect方法

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import pymysql
#import MySQLdb

# connect方法 连接数据库
db = pymysql.connect("192.168.24.144","chy","123456","mysql")
# cursor() 创建游标对象
cursor = db.cursor()

# 使用 execute()  方法执行 SQL 查询 
cursor.execute("SELECT VERSION()")

# fetchone() 方法获取单条数据
data = cursor.fetchone()

print ("Database version : %s " % data)

执行后报错

(1045, u"Access denied for user 'chy'@'192.168.24.123' (using password: YES)")

连接失败,在服务端查看用户chy的相关情报

mysql> SELECT host ,user , authentication_string  FROM user WHERE user = 'chy';
+-----------+------+-------------------------------------------+
| host      | user | authentication_string                     |
+-----------+------+-------------------------------------------+
| localhost | chy  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)

目测是host需要修改,和上午记载的问题基本一致
使用上午的方法进行修改
update user set host = '%' where user ='chy';
修改后问题依然存在

(1044, u"Access denied for user 'chy'@'%' to database 'mysql'")

再查发现SELECT host ,user , authentication_string FROM user WHERE user = 'chy';不太够用,需要查看一些别的权限,使用SELECT host,user,Grant_priv,Super_priv FROM mysql.user;

mysql> SELECT host,user,Grant_priv,Super_priv FROM mysql.user;
+-----------+------------------+------------+------------+
| host      | user             | Grant_priv | Super_priv |
+-----------+------------------+------------+------------+
| %         | root             | Y          | Y          |
| localhost | mysql.session    | N          | Y          |
| localhost | mysql.sys        | N          | N          |
| localhost | debian-sys-maint | Y          | Y          |
| %         | chy              | N          | N          |
+-----------+------------------+------------+------------+
5 rows in set (0.00 sec)

修改权限Grant_priv 和 Super_priv 等会查查这些权限都是什么意思

mysql> UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='chy';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

解决以上问题,执行上面的代码脚本,终端输出如下:

chy:~/code_bak/MY_SQL$ python mysql.py 
Database version : 5.7.28-0ubuntu0.16.04.2