python——MySQL封装操作

超、凢脫俗 2024-04-03 09:18 40阅读 0赞

![在这里插入图片描述][df523001dd7942cca600cd8280374439.jpeg_pic_center]

## 一、python与MySQL交互时需要用到的函数 ##

> **result=cur.execute(sql,params) ：表示这条sql语句去数据库中操作了几行数据  
> conn.commit()：当对数据库中数据进行增删改时，需要commit（）提交，才可以生效  
> conn.rollback()：回滚**

### 操作单条数据 ###

from pymysql import *
    
    conn = connect(host='localhost',
                   port=3306,
                   user='root',
                   password='root',
                   db='ceshi_platform',
                   charset='utf8')
    
    cur=conn.cursor()
    sql='select * from auth_user where username=%s'
    params=('kobe1',)
    try:
        result=cur.execute(sql,params)  #todo result：表示这条sql语句去数据库中操作了几行数据
        print(result)
        conn.commit()
    except Exception as e:
        conn.rollback()
    finally:
        if cur:
            cur.close()
        if conn:
            conn.close()

### 操作多条数据 ###

> **result = cur.fetchone()：获取1条数据  
> result = cur.fetchall()：获取所有的数据  
> result = cur.fetchmany(5)：表示获取5条数据；获取多条数据，  
> cur = conn.cursor() ：创建一个普通游标  
> cur = conn.cursor(pymysql.cursors.DictCursor) :创建一个字典游标，输出结果包含字段名称  
> cur.executemany(sql, params)：用于批量插入或者批量修改，不用于批量查询，因为批量查询只用了1次sql**

from pymysql import *
    import pymysql
    conn = connect(host='localhost',
                   port=3306,
                   user='root',
                   password='root',
                   db='ceshi_platform',
                   charset='utf8')
    cur = conn.cursor()                           #默认的游标
    cur = conn.cursor(pymysql.cursors.DictCursor)  # todo 字典游标
    sql = 'select * from auth_user where id>%s'
    params = ('3',)
    try:
        cur.execute(sql, params)
        #result = cur.fetchone()
        result = cur.fetchall()
        print(result)
        conn.commit()
    except Exception as e:
        conn.rollback()
    finally:
        if cur:
            cur.close()
        if conn:
            conn.close()

\[\{‘id’: 4, ‘password’: ‘666666’, ‘last\_login’: datetime.datetime(2022, 10, 5, 11, 12, 13, 420781), ‘is\_superuser’: 0, ‘username’: ‘kobe1’, ‘first\_name’: ‘kobe’, ‘last\_name’: ‘kobe’, ‘email’: ‘123456@126.com’, ‘is\_staff’: 0, ‘is\_active’: 1, ‘date\_joined’: datetime.datetime(2022, 10, 5, 11, 12, 13, 420781)\}, \{‘id’: 5, ‘password’: ‘pbkdf2\_sha256 150000 150000 150000WEhS5oO3hVtr$8AhnyeRLCUppqfXY8nEhphgzmLpmPbGZ0dBQF5UHfLY=’, ‘last\_login’: None, ‘is\_superuser’: 0, ‘username’: ‘3yyy67’, ‘first\_name’: ‘’, ‘last\_name’: ‘’, ‘email’: ‘36617@qq.com’, ‘is\_staff’: 0, ‘is\_active’: 1, ‘date\_joined’: datetime.datetime(2022, 10, 6, 3, 21, 45, 208626)\}\]

--------------------

## 二、封装源码 ##

# encoding=utf8
    
    from pymysql import *
    
    
    class MysqlHelper(object):
        # todo 数据库连接参数，可以定义多个，比如conn_params1，conn_params2，用于连接多个数据库，在类实例化时指定
        conn_params = {
        
            'host': 'localhost',
            'port': 3306,
            'user': 'root',
            'mm': 'root',
            'db': 'ceshi_platform',
            'charset': 'utf8'}
    
        # todo 类的构造函数，主要用于类的初始化
        def __init__(self, conn_params):
            self.__host = conn_params['host']
            self.__port = conn_params['port']
            self.__db = conn_params['db']
            self.__user = conn_params['user']
            self.__passwd = conn_params['passwd']
            self.__charset = conn_params['charset']
    
        # todo 建立数据库连接和打开游标
        def __connect(self):
            self.__conn = connect(host=self.__host,
                                  port=self.__port,
                                  db=self.__db,
                                  user=self.__user,
                                  passwd=self.__passwd,
                                  charset=self.__charset)
            self.__cursor = self.__conn.cursor()
    
        # todo 关闭游标和关闭连接
        def __close(self):
            self.__cursor.close()
            self.__conn.close()
    
        # todo 取一条数据
        def get_one(self, sql, params):
            result = None
            try:
                self.__connect()
                self.__cursor.execute(sql, params)
                result = self.__cursor.fetchone()
                self.__close()
            except Exception as e:
                print(e)
            return result
    
        # todo 取所有数据
        def get_all(self, sql, params):
            lst = ()
            try:
                self.__connect()
                self.__cursor.execute(sql, params)
                lst = self.__cursor.fetchall()
                self.__close()
            except Exception as e:
                print(e)
            return lst
    
        # todo 增加数据
        def insert(self, sql, params):
            return self.__edit(sql, params)
    
        # todo 修改数据
        def update(self, sql, params):
            return self.__edit(sql, params)
    
        # todo 删除数据
        def delete(self, sql, params):
            return self.__edit(sql, params)
    
        # todo 写数据操作具体实现，增删改操作都是调用这个方法来实现，这是个私有方法，不允许类外部调用
        def __edit(self, sql, params):
            count = 0
            try:
                self.__connect()
                count = self.__cursor.execute(sql, params)
                self.__conn.commit()
                self.__close()
            except Exception as e:
                print(e)
            return count

## 三、数据库操作 ##

### 1、查询操作 ###

from MysqlHelper import *
    from datetime import datetime
    
    #查询数据
    mysqlhelper = MysqlHelper(MysqlHelper.conn_params)
    sql = "select * from auth_user where username=%s"
    params = ('butler',)
    result = mysqlhelper.get_all(sql,params)
    
    #todo 获取查询结果条数
    print(len(result))
    
    #todo 打印执行结果
    for row in result:
        print(row)

1  
(3, ‘pbkdf2\_sha256 150000 150000 150000KmwkWLxdqfWC$0thi/gJTJinBQAYK72Vf8Ft5xRnFvNx7QOlkXpVFrGc=’, None, 0, ‘butler’, ‘’, ‘’, ‘111111111@qq.com’, 0, 1, datetime.datetime(2022, 10, 2, 15, 42, 31, 67825))

## 2、增加数据 ##

mysqlhelper = MysqlHelper(MysqlHelper.conn_params)
    sql = "insert into auth_user values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    params = (4,"666666",datetime.now(),0,"kobe","kobe","kobe","123456@126.com",0,1,datetime.now())
    rowcount = mysqlhelper.insert(sql,params)
    
    print("已增加"+str(rowcount)+"条数据")

已增加1条数据

## 3、删除数据 ##

mysqlhelper = MysqlHelper(MysqlHelper.conn_params)
    sql = "delete from auth_user where id=%s"
    params = (4,)
    rowcount = mysqlhelper.delete(sql,params)
    
    print("已删除"+str(rowcount)+"条数据")

已删除1条数据

## 4、修改数据 ##

mysqlhelper = MysqlHelper(MysqlHelper.conn_params)
    sql = "update auth_user set username=%s where id=%s"
    params = ('kobe1', 4)
    rowcount = mysqlhelper.update(sql, params)
    
    print("已修改" + str(rowcount) + "条数据")

已修改1条数据

--------------------

![在这里插入图片描述][896ae90bc36241ffbac9e7829c9f1bd9.jpeg_pic_center]

[df523001dd7942cca600cd8280374439.jpeg_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/fd02d1d1ad4a4f84ade86609a98b3de4.jpeg
[896ae90bc36241ffbac9e7829c9f1bd9.jpeg_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/180f4fbb194543e2b487d88ed9b532e7.jpeg