2020-01-09
總結自菜鳥教程
以下所有操作基于pymysql庫

數(shù)據(jù)庫連接

使用pymysql.connect方法

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

import pymysql
#import MySQLdb

# connect方法 連接數(shù)據(jù)庫
db = pymysql.connect("192.168.24.144","chy","123456","mysql")
# cursor() 創(chuàng)建游標對象
cursor = db.cursor()

# 使用 execute()  方法執(zhí)行 SQL 查詢 
cursor.execute("SELECT VERSION()")

# fetchone() 方法獲取單條數(shù)據(jù)
data = cursor.fetchone()

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

執(zhí)行后報錯

(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'")

再查發(fā)現(xiàn)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)

解決以上問題,執(zhí)行上面的代碼腳本,終端輸出如下:

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