python——MySQL封装操作

一、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’: ‘[email protected]’, ‘is_staff’: 0, ‘is_active’: 1, ‘date_joined’: datetime.datetime(2022, 10, 6, 3, 21, 45, 208626)}]

二、封装源码

# encoding=utf8from pymysql import*classMysqlHelper(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 取一条数据defget_one(self, sql, params):
        result =Nonetry:
            self.__connect()
            self.__cursor.execute(sql, params)
            result = self.__cursor.fetchone()
            self.__close()except Exception as e:print(e)return result

    # todo 取所有数据defget_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 增加数据definsert(self, sql, params):return self.__edit(sql, params)# todo 修改数据defupdate(self, sql, params):return self.__edit(sql, params)# todo 删除数据defdelete(self, sql, params):return self.__edit(sql, params)# todo 写数据操作具体实现，增删改操作都是调用这个方法来实现，这是个私有方法，不允许类外部调用def__edit(self, sql, params):
        count =0try:
            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’, ‘’, ‘’, ‘[email protected]’, 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","[email protected]",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条数据