最近正在研究python，用到了pymysql，但找了很久都没有找到现成的数据库操作类，自己封装了一个。 类文件名字：mysql.py 代码如下：

coding=utf-8

import pymysql

class mysql(object): """[summary] mysql数据库操作工具类 Args: object ([type]): [description] """ def init (self): print('初始化') self.conn = pymysql.connect('127.0.0.1','root','123456','monitor_system') self.cursor=self.conn.cursor(cursor=pymysql.cursors.DictCursor)

 def get_all(self,sql,args):
    """[summary]
        返回全部符合条件的数据，args可以有效的防止sql注入
    Args:
        sql ([string]): [select id,name from user where id=%s and name=%s]
        args ([tuple]): [('123','张三'),没有参数时传None]

    Returns:
        [type]: [description]
    """
    # 返回多条数据

    res = ''
    try:
        print('get_all:',sql)
        print('args',args)
        self.cursor.execute(sql,args)
        res = self.cursor.fetchall()

    except Exception as e:
        print(e)

    finally:
        self.get_close()
    return res

def get_one(self,sql,args):
    """[summary]
        返回符合条件的第一条记录，args可以有效的防止sql注入
    Args:
        sql ([string]): [select id,name from user where id=%s and name=%s]
        args ([tuple]): [('123','张三'),没有参数时传None]
    """
    # 返回单条数据
    res = ''
    try:
        print('get_one:',sql)
        print('args:',args)
        self.cursor.execute(sql, args)
        res = self.cursor.fetchone()
    except Exception as e:
        print(e)
    finally:
        self.get_close()
    return res

def run_sql(self,sql,args):
    """[summary]
        执行sql(无返回值), 可以用于添加和修改操作，args可以有效的防止sql注入
    Args:
        sql ([string]): [insert into user (id,name) values (%s,%s)]
        args ([tuple]): [('123','张三'),没有参数时传None]
    """
    try:
        print('run_sql:',sql)
        print('args:',args)
        self.cursor.execute(sql,args)
        self.conn.commit()
    except Exception as e:
        print(e)
    finally:
        self.get_close()    

def run_sql_id(self,sql,args):
    """[summary]
        执行sql(有回值，一般为主键), 可以用于添加和修改操作，args可以有效的防止sql注入
    Args:
        sql ([string]): [insert into user (name,pwd) values (%s,%s)]
        args ([tuple]): [('123','张三'),没有参数时传None]

    Returns:
        [type]: [description]
    """
    try:
        print('run_sql_id:',sql)
        print('args:',args)
        self.cursor.execute(sql,args)
        self.conn.commit()
    except Exception as e:
        print(e)
    finally:
        self.get_close()  

    return self.cursor.lastrowid

def get_date_page(self,cols,tables,orders,pageSize,pageNumber,args):
    """[summary]
        带翻页带数据
    Args:
        cols ([string]): [需要查询的字段，例如：id,name,pwd]
        tables ([string]): [需要查询的表和搜索条件，例如：user where name=%s;如果需要用到like采用:name like concat('%','名字','%')]
        orders ([string]): [需要排序的字段，例如：order by name desc]
        pageSize ([int]): [每页显示多少条，例如：10]
        pageNumber ([int]): [当前页(从1开始)，例如3]
        args ([tuple]): [('123','张三'),没有参数时传None]
    Returns:
        [type]: [数据集合,总记录数]
    """
    data = ''
    recCount = 0
    try:
        recBegin = (pageNumber-1)*pageSize  #开始记录
        sql = f'select {cols} from {tables} {orders} limit {recBegin},{pageSize} '

        print('get_date_page:',sql)
        print('args:',args)

        self.cursor.execute(sql,args)
        data = self.cursor.fetchall()
        sqlCount = f'select count(1) as count from {tables} '

        self.cursor.execute(sqlCount,args)
        dataCount = self.cursor.fetchone()

        if dataCount is not None and dataCount['count']>0:
            recCount = dataCount['count']

    except Exception as e:
        print(e)
    finally:
        self.get_close()        
    page = [data,recCount]
    return page

def get_close(self):
    # 释放数据库链接
    self.cursor.close()
    self.conn.close()     

调用方法

from 路径.mysql import mysql

c = mysql() # 实例化

list1 = c.get_date_page('id,name','test_user ','order by name desc',5,1,None) # 翻页查询

查看更多关于python 之 pymysql 数据库操作类封装的详细内容...