返回列表 发帖

CentOS8使用PyMySQL管理MariaDB

MariaDB的安装可参考:CentOS8编译安装zabbix4.4.5


MariaDB服务器要在防火墙(Firewalld)打开TCP 3306 端口:
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

查看防火墙(Firewalld)所有打开的端口:
[root@centos8 ~]# firewall-cmd --zone=public --list-ports
80/tcp 3306/tcp


还必须要赋权给远程客户端:
MariaDB [(none)]> grant all on *.* to zhuohua@'192.168.168.159' identified by '888';
图片1.png
2020-11-2 14:54



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


直接使用之前已经创建好的库(zabbix):
MariaDB [(none)]> show create database zabbix;
图片2.png
2020-11-2 14:55




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




## Windows7客户端(192.168.168.159)安装第三方库(PyMySQL),可参考:Python3脚本管理Windows下的MySQL

查看软件版本:
pip3 freeze
图片3.png
2020-11-2 14:56


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




######

Windows客户端远程测试与数据库的连接的脚本:

#coding=utf-8

import pymysql

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

        print ("Database version : %s " % Results) #显示数据库的版本信息
        print (Results)
        print ("%s" % Results)

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

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

if __name__ == "__main__":
        main()


脚本运行的结果:
图片4.png
2020-11-2 14:57







######

查看MariaDB的最大连接数的脚本:

#coding=utf-8

import pymysql

def db_connect():
        db = pymysql.connect("192.168.168.154", "zhuohua", "888", "zabbix",3306,charset="utf8")
        cursor=db.cursor()
       
        sql="show variables like '%max_connections%'" #查看MariaDB的最大连接数
        cursor.execute(sql)
        Results=cursor.fetchall()
        print(Results)
        print(Results[1])
        print(type(Results[1]))
        print() #输出空行
        a=(Results[1])
        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()


脚本运行的结果:
图片5.png
2020-11-2 14:58







######

创建表的脚本:

#coding=utf-8

import pymysql

def create_table():

        db = pymysql.connect("192.168.168.154", "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()


脚本运行的结果:
图片6.png
2020-11-2 14:58


图片7.png
2020-11-2 14:58







######

插入记录的脚本:

#coding=utf-8

import pymysql

def insert_record():

        db = pymysql.connect("192.168.168.154", "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() # 这种方法可以确保要么所有记录都插入成功,或都插入失败


脚本运行的结果:
图片8.png
2020-11-2 14:59








######

查询记录的脚本:

#coding=utf-8

import pymysql

def query_data():

        try:
                db = pymysql.connect("192.168.168.154", "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()


脚本运行的结果:
图片9.png
2020-11-2 14:59








######

查询记录的脚本:

#coding=utf-8

import pymysql

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

def query_data():

        db = pymysql.connect("192.168.168.154", "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()


脚本运行的结果:(假如找到匹配的记录)
图片10.png
2020-11-2 15:00


脚本运行的结果:(假如没有找到匹配的记录)
图片11.png
2020-11-2 15:00







######

查询记录的脚本:(查询中只取一个值)

#coding=utf-8

import pymysql

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

def query_data(sql):

        db = pymysql.connect("192.168.168.154", "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)


脚本运行的结果:(假如找到匹配的记录)
图片12.png
2020-11-2 15:01


脚本运行的结果:(假如没有找到匹配的记录)
图片13.png
2020-11-2 15:01







######

查询记录的脚本:(查询中使用like语句)

#coding=utf-8

import pymysql

sql_1 = "SELECT id FROM table1 WHERE name like '%大%'"

def query_data(sql):

        db = pymysql.connect("192.168.168.154", "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)


脚本运行的结果:(假如找到匹配的记录)
图片14.png
2020-11-2 15:01


脚本运行的结果:(假如没有找到匹配的记录)
图片15.png
2020-11-2 15:02








######

查询记录的脚本:(查询中使用聚会函数语句)

#coding=utf-8

import pymysql

sql_1 = "SELECT avg(math) FROM table1"

def query_data(sql):

        db = pymysql.connect("192.168.168.154", "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)


脚本运行的结果:
图片16.png
2020-11-2 15:02









######

更改记录的脚本:

#coding=utf-8

import pymysql

db = pymysql.connect("192.168.168.154", "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()


脚本运行的结果:
图片17.png
2020-11-2 15:03


图片18.png
2020-11-2 15:03


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






######

删除记录的脚本:

#coding=utf-8

import pymysql

db = pymysql.connect("192.168.168.154", "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()


脚本运行的结果:
图片19.png
2020-11-2 15:04


图片20.png
2020-11-2 15:04


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













######

## 假如在服务器(CentOS8)运行Python3脚本,那服务器除了要安装Python3,还要安装第三方库(PyMySQL),可参考:CentOS8使用PyMySQL管理MySQL8.0

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


在MariaDB创建用户(zhuohua@'localhost'):(此用户仅允许本地登录)
MariaDB [(none)]> grant all on *.* to zhuohua@'localhost' identified by '888';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> show grants for zhuohua@'localhost';
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@localhost                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zhuohua'@'localhost' IDENTIFIED BY PASSWORD '*41DDB5DFD213B288EE050BD64DC6AA36815A3486' |
+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)



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

import pymysql

def db_connect():
        db = pymysql.connect("localhost", "zhuohua", "888", "zabbix",3306,charset="utf8")
        cursor=db.cursor()
       
        sql="SELECT VERSION()" #显示数据库的版本信息
        cursor.execute(sql)
        Results=cursor.fetchone()
        print(Results[0])
        print(type(Results[0]))

        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
10.3.17-MariaDB
<class 'str'>








###

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

import pymysql

def db_connect():
        db = pymysql.connect("localhost", "zhuohua", "888", "zabbix",3306,charset="utf8")
        cursor=db.cursor()
       
        sql="show variables like '%max_connections%'" #查看MariaDB的最大连接数
        cursor.execute(sql)
        Results=cursor.fetchall()
        print(Results)
        print(Results[1])
        print(type(Results[1]))
        print() #输出空行
        a=(Results[1])
        aa=a[1]
        print(aa)
        print(type(aa))
        aaa=float(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
(('extra_max_connections', '1'), ('max_connections', '151'))
('max_connections', '151')
<class 'tuple'>

151
<class 'str'>
151.0
<class 'float'>








###

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

import pymysql

def db_connect():
        db = pymysql.connect("localhost", "zhuohua", "888", "zabbix",3306,charset="utf8")
        cursor=db.cursor()
       
        sql="show status like 'threads_connected'" #查看MariaDB当前打开的连接数
        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_connected', '27')
27
<class 'str'>







###

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

import pymysql

def db_connect():
        db = pymysql.connect("localhost", "zhuohua", "888", "zabbix",3306,charset="utf8")
        cursor=db.cursor()
       
        sql="show status like 'threads_running'" #查看MariaDB当前处于激活状态的连接数
        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("localhost", "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.61MB',)
9.61MB







###

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

import pymysql

def db_connect():
        db = pymysql.connect("localhost", "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.59MB',)
10.59MB

10.59
<class 'str'>







######

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

import pymysql

sql_1 = "SELECT name FROM table1 WHERE math = 66.5"

def query_data(sql):

        db = pymysql.connect("localhost", "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]))

        except Exception as e:
                pass #占位符
        finally:
                db.close()


if __name__ == "__main__":

        Result_1 = query_data(sql_1)


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


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








######

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

import pymysql

def db_truncate():
        db = pymysql.connect("localhost", "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
表已清空

图片21.png
2020-11-2 15:08








###

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

import pymysql

def del_table():

        db = pymysql.connect("localhost", "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
表不存在

图片22.png
2020-11-2 15:08





相关文章:
CentOS8使用PyMySQL管理MySQL5.5

返回列表