最近正在研究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()
