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$WEhS5oO3hVtr$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$KmwkWLxdqfWC$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条数据

---