运维开发网

python 之 pymysql 数据库操作类封装

运维开发网 https://www.qedev.com 2021-03-24 19:41 出处:51CTO 作者:wx57cf7bc212633
最近正在研究python,用到了pymysql,但找了很久都没有找到现成的数据库操作类,自己封装了一个。类文件名字:mysql.py代码如下:coding=utf-8importpymysqlclassmysql(object):"""[summary]mysql数据库操作工具类Args:object([type]):[description]""

最近正在研究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) # 翻页查询

0

精彩评论

暂无评论...
验证码 换一张
取 消