返回列表 发帖

CentOS8使用PyMySQL管理MySQL5.5

笺注:这是在 CentOS6.9编译安装Zabbix4.4.5 的基础上进行的。


MySQL服务器要在防火墙打开TCP 3306 端口:
[root@Zabbix_server_01 ~]# iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
[root@Zabbix_server_01 ~]# iptables-save > /etc/sysconfig/iptables


还必须要赋权给客户端:
mysql> grant all on *.* to zhuohua@'%' identified by '888';
Query OK, 0 rows affected (0.07 sec)

查看用户(zhuohua@'%')的权限:
mysql> show grants for zhuohua@'%';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@%                                                                                            |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zhuohua'@'%' IDENTIFIED BY PASSWORD '*41DDB5DFD213B288EE050BD64DC6AA36815A3486' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


直接使用之前已经创建好的库(zabbix):
mysql> show create database zabbix;
图片1.png
2020-11-2 11:15



笺注:假如只是在远程客户端通过Python3脚本管理MySQL数据库,那么MySQL服务器连Python3都不需要安装。





## 客户端(CentOS8)安装第三方库(PyMySQL),可参考:CentOS8使用PyMySQL管理MySQL8.0

查看软件版本:
[root@centos8 ~]# pip3 freeze
PyMySQL==0.10.1

注释:PyMySQL可以管理MySQL,也可以管理MariaDB





######

客户端远程测试与数据库的连接的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8

import pymysql

def db_connect():
        db = pymysql.connect("192.168.168.130", "zhuohua", "888", "zabbix",3306,charset="utf8") # 打开数据库连接;远程MySQL服务器的IP地址(192.168.168.130)、用户名、密码、其中一个库的名称、端口号、字符集。
        cursor=db.cursor()
        cursor.execute("SELECT VERSION()")
        Results=cursor.fetchone()

        print (Results)
        print (Results[0])
        print () #输出空行
        a = Results[0]
        Results = (a[:-4]) #去掉最后四个字符
        print(Results)
        print ("Database version : %s " % Results) #显示数据库的版本信息
       
        db.close()# 关闭数据库连接

def main():
        try:
                db_connect()
        except Exception as e:
                print(e)

if __name__ == "__main__":
        main()


设置脚本权限:
[root@centos8 ~]# chmod a+x xx.py

脚本运行的结果:
[root@centos8 ~]# python3 xx.py
('5.5.48-log',)
5.5.48-log

5.5.48
Database version : 5.5.48






######

查看MySQL的最大连接数的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8

import pymysql

def db_connect():
        db = pymysql.connect("192.168.168.130", "zhuohua", "888", "zabbix",3306,charset="utf8")
        cursor=db.cursor()
       
        sql="show variables like '%max_connections%'" #查看MySQL的最大连接数
        cursor.execute(sql)
        Results=cursor.fetchall()
        print(Results)
        print(Results[0])
        print(type(Results[0]))
        print() #输出空行
        a=(Results[0])
        aa=a[1]
        print(aa)
        print(type(aa))
        aaa=int(aa) #转换为整型
        print(aaa)
        print(type(aaa))
       
        db.close()

def main():
        try:
                db_connect()
        except Exception as e:
                print(e)

if __name__ == "__main__":
        main()


脚本运行的结果:
[root@centos8 ~]# python3 xx.py
(('max_connections', '500'),)
('max_connections', '500')
<class 'tuple'>

500
<class 'str'>
500
<class 'int'>




###

查看MySQL当前打开的连接数的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8

import pymysql

def db_connect():
        db = pymysql.connect("192.168.168.130", "zhuohua", "888", "zabbix",3306,charset="utf8")
        cursor=db.cursor()
       
        sql="show status like 'threads_connected'" #查看MySQL当前打开的连接数
        cursor.execute(sql)
        Results=cursor.fetchone()
        print(Results)
        print(Results[1])
        print(type(Results[1]))
        print()
        a=Results[1]
        b=float(Results[1]) #转换为数值(有小数点)
        print(b)
        print(type(b))
       
        db.close()

def main():
        try:
                db_connect()
        except Exception as e:
                print(e)

if __name__ == "__main__":
        main()


脚本运行的结果:
[root@centos8 ~]# python3 xx.py
('Threads_connected', '26')
26
<class 'str'>

26.0
<class 'float'>





###

查看MySQL当前处于激活状态的连接数的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8

import pymysql

def db_connect():
        db = pymysql.connect("192.168.168.130", "zhuohua", "888", "zabbix",3306,charset="utf8")
        cursor=db.cursor()
       
        sql="show status like 'threads_running'" #查看MySQL当前处于激活状态的连接数
        cursor.execute(sql)
        Results=cursor.fetchone()
        print(Results)
        print(Results[1])
        print(type(Results[1]))
       
        db.close()

def main():
        try:
                db_connect()
        except Exception as e:
                print(e)

if __name__ == "__main__":
        main()


脚本运行的结果:
[root@centos8 ~]# python3 xx.py
('Threads_running', '1')
1
<class 'str'>








######

查看某个库的大小的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8

import pymysql

def db_connect():
        db = pymysql.connect("192.168.168.130", "zhuohua", "888", "zabbix",3306,charset="utf8")
        cursor=db.cursor()
       
        sql="select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema = 'zabbix'" #查看库(zabbix)的大小。
        cursor.execute(sql)
        Results=cursor.fetchone()
        print(Results)
        print(Results[0])
       
        db.close()

def main():
        try:
                db_connect()
        except Exception as e:
                print(e)

if __name__ == "__main__":
        main()


脚本运行的结果:
[root@centos8 ~]# python3 xx.py
('9.53MB',)
9.53MB




###

查看所有库的总大小的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8

import pymysql

def db_connect():
        db = pymysql.connect("192.168.168.130", "zhuohua", "888", "zabbix",3306,charset="utf8")
        cursor=db.cursor()
       
        sql="select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES" #查看所有库的总大小。
        cursor.execute(sql)
        Results=cursor.fetchone()
        print(Results)
        print(Results[0])
        print()
        a = Results[0]
        b = (a[:-2]) #去掉最后两个字符
        print(b)
        print(type(b))
       
        db.close()

def main():
        try:
                db_connect()
        except Exception as e:
                print(e)

if __name__ == "__main__":
        main()


脚本运行的结果:
[root@centos8 ~]# python3 xx.py
('10.10MB',)
10.10MB

10.10
<class 'str'>






######

创建表的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8

import pymysql

def create_table():

        db = pymysql.connect("192.168.168.130", "zhuohua", "888", "zabbix",3306,charset="utf8")
       
        cursor = db.cursor()
       
        sql = """CREATE TABLE table1 (
        id int not null auto_increment,
        name varchar(20) NOT NULL,
        math float,
        primary key(id)
        )"""
       
        try:
                cursor.execute(sql) #执行SQL语句
                print("创建表成功。")

        except Exception as e:
                print("创建表失败,CASE:%s" % e)

        finally:
                db.close() # 关闭数据库连接

               
if __name__ == "__main__":
       
        create_table()


脚本运行的结果:
[root@centos8 ~]# python3 xx.py
创建表成功。

图片2.png
2020-11-2 11:19







######

插入记录的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8

import pymysql

def insert_record():

        db = pymysql.connect("192.168.168.130", "zhuohua", "888", "zabbix",3306,charset="utf8")
       
        cursor=db.cursor()
       
        try:
                sql_1 = "INSERT INTO table1(name,math)" \
                " VALUES('李大杰',18)"
               
                cursor.execute(sql_1)

                sql_2 = "INSERT INTO table1(name,math)" \
                " VALUES('范小冰',80)"
               
                cursor.execute(sql_2)

                sql_3 = "INSERT INTO table1(name,math)" \
                " VALUES('zhuohua',70.5)"
               
                cursor.execute(sql_3)
               
               
                db.commit() #提交到数据库执行;必须是所有SQL语句都执行成功才会提交

                print("插入记录成功。")

        except Exception as e:

                print("插入记录失败,CASE:%s" % e)

                db.rollback() # 如果发生错误就回滚

        finally:
                db.close() # 关闭数据库连接


if __name__ == "__main__":

        insert_record() # 这种方法可以确保要么所有记录都插入成功,或都插入失败


脚本运行的结果:
[root@centos8 ~]# python3 xx.py
插入记录成功。





######

查询记录的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8

import pymysql

def query_data():

        try:
                db = pymysql.connect("192.168.168.130", "zhuohua", "888", "zabbix",3306,charset="utf8")
                cursor=db.cursor()
                sql="select * from table1 order by id desc" #SQL语句
                cursor.execute(sql) # 执行SQL语句
                Results=cursor.fetchall() # 获取数据
                print(Results) # 输出数据

        except Exception as e:
                print(e) # 显示异常对象真正的异常信息
        finally:
                db.close() # 关闭数据库连接
               
if __name__ == "__main__":
        query_data()


脚本运行的结果:
[root@centos8 ~]# python3 xx.py
((3, 'zhuohua', 70.5), (2, '范小冰', 80.0), (1, '李大杰', 18.0))




######

查询记录的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8

import pymysql

sql = "SELECT * FROM table1 WHERE math >= 60"  # SQL语句

def query_data():

        db = pymysql.connect("192.168.168.130", "zhuohua", "888", "zabbix",3306,charset="utf8")
       
        cursor = db.cursor()

        try:

                a = cursor.execute(sql) # 给变量进行赋值时,已经执行了SQL语句

                #print(a)

                if a is 0:
                        print("没有找到匹配的记录")

                Results = cursor.fetchall()

                for row in Results:
                        id1 = row[0]
                        name1 = row[1]
                        math1 = row[2]

                        print("id=%s,name=%s,\n"
                        "math=%s" %(id1,name1,math1))

        except Exception as e:

                print("Error: unable to fecth data.Error info:%s" % e)

        finally:
                db.close() # 关闭数据库连接


if __name__ == "__main__":
        query_data()


脚本运行的结果:(假如找到匹配的记录)
[root@centos8 ~]# python3 xx.py
id=2,name=范小冰,
math=80.0
id=3,name=zhuohua,
math=70.5

脚本运行的结果:(假如没有找到匹配的记录)
[root@centos8 ~]# python3 xx.py
没有找到匹配的记录





######

查询记录的脚本:(查询中只取一个值)
[root@centos8 ~]# cat xx.py
#coding=utf-8

import pymysql

sql_1 = "SELECT id FROM table1 WHERE name = 'Zhuohua'" #MySQL/MariaDB中关键字的查询不区分英文大小写

def query_data(sql):

        db = pymysql.connect("192.168.168.130", "zhuohua", "888", "zabbix",3306,charset="utf8")
       
        cursor = db.cursor()

        try:

                a = cursor.execute(sql)

                if a == 0:
                        print("没有找到匹配的记录")

                Results = cursor.fetchone()

                print(Results[0]) #输出结果的第一个区块
                print(type(Results[0]))

        except Exception as e:
                pass #占位符
        finally:
                db.close() # 关闭数据库连接


if __name__ == "__main__":

        Result_1 = query_data(sql_1)


脚本运行的结果:(假如找到匹配的记录)
[root@centos8 ~]# python3 xx.py
3
<class 'int'>

脚本运行的结果:(假如没有找到匹配的记录)
[root@centos8 ~]# python3 xx.py
没有找到匹配的记录





######

查询记录的脚本:(查询中使用like语句)
[root@centos8 ~]# cat xx.py
#coding=utf-8

import pymysql

sql_1 = "SELECT math FROM table1 WHERE name like '%大_'"
def query_data(sql):

        db = pymysql.connect("192.168.168.130", "zhuohua", "888", "zabbix",3306,charset="utf8")
       
        cursor = db.cursor()
        try:

                a = cursor.execute(sql)

                if a is 0:
                        print("没有找到匹配的记录")

                Results = cursor.fetchone()

                print(Results[0]) #输出结果的第一个区块
                print(type(Results[0]))
                print()
                a=Results[0]
                b=str(Results[0]) #转换为字符串
                print(b)
                print(type(b))
       
        except Exception as e:
                pass #占位符
        finally:
                db.close() # 关闭数据库连接

if __name__ == "__main__":

        Result_1 = query_data(sql_1)


脚本运行的结果:(假如找到匹配的记录)
[root@centos8 ~]# python3 xx.py
18.0
<class 'float'>

18.0
<class 'str'>


脚本运行的结果:(假如没有找到匹配的记录)
[root@centos8 ~]# python3 xx.py
没有找到匹配的记录








######

查询记录的脚本:(查询中使用聚会函数语句)
[root@centos8 ~]# cat xx.py
#coding=utf-8

import pymysql

sql_1 = "SELECT avg(math) FROM table1"  # SQL语句

def query_data(sql):

        db = pymysql.connect("192.168.168.130", "zhuohua", "888", "zabbix",3306,charset="utf8")
       
        cursor = db.cursor()

        try:
                cursor.execute(sql)
                Results = cursor.fetchone()

                print(Results[0])
                print(type(Results[0]))
               
                a=Results[0]
                b=round(a,2) #保留两位小数,是四舍五入
                print(b)
               
                b=int(a) #转换为整型,不要小数部分;只是直接截取整数部分,非四舍五入
                print(b)


        except Exception as e:
                pass
        finally:
                db.close()

if __name__ == "__main__":

        Result_1 = query_data(sql_1)


脚本运行的结果:
[root@centos8 ~]# python3 xx.py
56.166666666666664
<class 'float'>
56.17
56







######

更改记录的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8

import pymysql

db = pymysql.connect("192.168.168.130", "zhuohua", "888", "zabbix",3306,charset="utf8")
       
cursor = db.cursor()

def update_table():

        try:

                sql1 = "UPDATE table1 SET math = 66.5 WHERE name = 'zhuoHUA'"  # 更新数据;MySQL/MariaDB中关键字的定位不区分英文大小写

                cursor.execute(sql1) # 执行SQL语句


                sql2 = "UPDATE table1 SET math = 77.5 WHERE name like '__杰%'"  # 更新数据

                cursor.execute(sql2) # 执行SQL语句


                db.commit() #提交到数据库执行;必须是两条SQL语句都执行成功才会提交

                print("UPDATE SUCCESS.")

        except Exception as e:

                print('UPDATE MySQL table failed.Case:%s' % e)
               
                db.rollback() # 发生错误时回滚

        finally:
                db.close() # 关闭数据库连接


if __name__ == "__main__":
        update_table()


脚本运行的结果:
[root@centos8 ~]# python3 xx.py
UPDATE SUCCESS.

图片3.png
2020-11-2 11:22


备注:假如没有找到匹配的记录进行更新,会直接跳过,不会报错的。







######

删除记录的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8

import pymysql

db = pymysql.connect("192.168.168.130", "zhuohua", "888", "zabbix",3306,charset="utf8")
       
cursor = db.cursor()

def delete_record():

        try:

                sql1 = "DELETE FROM table1 WHERE name = '范小冰'"  #删除记录

                cursor.execute(sql1) # 执行sql语句


                sql2 = "DELETE FROM table1 WHERE name like  '%杰'"  #删除记录

                cursor.execute(sql2) # 执行sql语句
               
                db.commit() #提交到数据库执行;必须是两条SQL语句都执行成功才会提交
               
                print("DELETE SUCCESS.")

        except Exception as e:

                print("DELETE RECORD FAILED.Case:%s" % e)

                db.rollback() # 发生错误时回滚

        finally:
                db.close() # 关闭数据库连接


if __name__ == "__main__":
        delete_record()


脚本运行的结果:
[root@centos8 ~]# python3 xx.py
DELETE SUCCESS.

图片4.png
2020-11-2 11:22


备注:假如没有找到匹配的记录进行删除,会直接跳过,不会报错的。








######

清空表的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8

import pymysql

def db_truncate():
        db = pymysql.connect("192.168.168.130", "zhuohua", "888", "zabbix",3306,charset="utf8")
        cursor=db.cursor()
        sql = "truncate table table1" #清空表(table1
        cursor.execute(sql) #执行SQL语句

        print ('表已清空')
       
        db.close()

def main():
        try:
                db_truncate()
        except Exception as e:
                print(e)

if __name__ == "__main__":
        main()


脚本运行的结果:
[root@centos8 ~]# python3 xx.py
表已清空

图片5.png
2020-11-2 11:23







######

删除表的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8

import pymysql

def del_table():

        db = pymysql.connect("192.168.168.130", "zhuohua", "888", "zabbix",3306,charset="utf8")
       
        cursor = db.cursor()
       
        sql = """show tables like 'table1'"""  # 检查表(table1)是否已经存在
       
        a = cursor.execute(sql) # 给变量进行赋值时,命令已经执行了
       
        if a == False:  # 0 就是False; 1 就是 True
       
                print("表不存在")

        else:

                sql = """DROP TABLE IF EXISTS table1"""  # 如果表存在就删除
               
                cursor.execute(sql) # 执行SQL语句

                print("删除表成功")
       
        db.close()
       
if __name__ == "__main__":
        del_table()


脚本运行的结果:(假如表存在)
[root@centos8 ~]# python3 xx.py
删除表成功

脚本运行的结果:(假如表不存在)
[root@centos8 ~]# python3 xx.py
表不存在












######

## 假如在MySQL服务器(CentOS6.9)运行Python3脚本,那MySQL服务器除了要安装Python3,还要安装第三方库(PyMySQL):
[root@Zabbix_server_01 ~]# pip3 install PyMySQL==0.10.1 -i http://mirrors.aliyun.com/pypi/simple --trusted-host=mirrors.aliyun.com

Looking in indexes: http://mirrors.aliyun.com/pypi/simple
Collecting PyMySQL==0.10.1
  Downloading http://mirrors.aliyun.com/pypi/packages/1a/ea/dd9c81e2d85efd03cfbf808736dd055bd9ea1a78aea9968888b1055c3263/PyMySQL-0.10.1-py2.py3-none-any.whl (47kB)
    100% |████████████████████████████████| 51kB 1.3MB/s
Installing collected packages: PyMySQL
Successfully installed PyMySQL-0.10.1

注释:安装时,可以指定软件版本。

查看已安装的软件:
[root@Zabbix_server_01 ~]# pip3 freeze
PyMySQL==0.10.1




在MySQL服务器上创建Python3脚本:(进行本地连接MySQL)
[root@Zabbix_server_01 ~]# cat xx.py
#coding=utf-8

import pymysql

def db_connect():
        db = pymysql.connect("127.0.0.1", "zhuohua", "888", "zabbix",3306,charset="utf8")
        cursor=db.cursor()
       
        sql="SELECT VERSION()" #显示数据库的版本信息
        cursor.execute(sql)
        Results=cursor.fetchone()
       
        print (Results)
        print(Results[0])
        print () #输出空行
        a = Results[0]
        Results = (a[:-4]) #去掉最后四个字符
        print(Results)
        print ("Database version : %s " % Results) #显示数据库的版本信息

        db.close()

if __name__ == "__main__":
        db_connect()


运行脚本的结果:
[root@Zabbix_server_01 ~]# ll xx.py
-rw-r--r-- 1 root root 593 11月  1 16:53 xx.py
[root@Zabbix_server_01 ~]#
[root@Zabbix_server_01 ~]# python3 xx.py
('5.5.48-log',)
5.5.48-log

5.5.48
Database version : 5.5.48




相关文章:
MySQL的SQL语句
MySQL的最大连接数/最大可打开文件数

CentOS8_Apache2.4反向代理+Flask

Python3脚本管理Windows下的MySQL
Python3脚本管理Windows下的MariaDB

Zabbix调用Python3脚本监控MySQL(主从同步状态)

CentOS8使用PyMySQL管理MySQL8.0
CentOS8使用PyMySQL管理MariaDB

字符串的截取+去除空格

返回列表