python对mysql操作封装
python 对MySQL操作进行封装
后面直接拿来用就ok
import pymysql
class MysqlManager(object):
'''mysql管理器'''
def __init__(self, db, user, passwd, host='localhost', port=3306, charset='utf8'):
'''初始化数据库'''
self.__db = db
self.__user = user
self.__passwd = passwd
self.__host = host
self.__port = port
self.__charset = charset
self.__connect = None
self.__cursor = None
def _connect_db(self):
"""
dbManager._connect_db()
连接数据库
"""
params = {
"db": self.__db,
"user": self.__user,
"passwd": self.__passwd,
"host": self.__host,
"port": self.__port,
"charset": self.__charset
}
self.__connect = MySQLdb.connect(**params)
self.__cursor = self.__connect.cursor()
def _close_db(self):
'''
dbManager._close_db()
关闭数据库
'''
self.__cursor.close()
self.__connect.close()
def insert(self, table, insert_data):
'''
dbManager.insert(table, insert_data)
添加数据到数据库
str -> table 为字符串
[{},{}] -> 为列表中嵌套字典类型
'''
# 用户传入数据字典列表数据，根据key, value添加进数据库
# 连接数据库
self._connect_db()
try:
for data in insert_data:
# 提取插入的字段
key = ','.join(data.keys())
# 提取插入的值
values = map(self._deal_values, data.values())
insert_data = ', '.join(values)
# 构建sql语句
sql = "insert into {table}({key}) values ({val})".format(table=table, key=key, val=insert_data)
self.__cursor.execute(sql)
self.__connect.commit()
except Exception as error:
print error
finally:
self._close_db()
def delete(self, table, condition):
'''
dbManager.delete(table, condition)
删除数据库中的数据
str -> table 字符串类型
dict -> condition 字典类型
'''
self._connect_db()
# 处理删除的条件
condition_list = self._deal_values(condition)
condition_data = ' and '.join(condition_list)
# 构建sql语句
sql = "delete from {table} where {condition}".format(table=table, condition=condition_data)
self.__cursor.execute(sql)
self.__connect.commit()
self._close_db()
def update(self, table, data, condition=None):
"""
dbManager.update(table, data, [condition])
更新数据
str -> table 字符串类型
dict -> data 字典类型
dict -> condition 字典类型
"""
self._connect_db()
# 处理传入的数据
update_list = self._deal_values(data)
update_data = ",".join(update_list)
# 判断是否有条件
if condition is not None:
# 处理传入的条件
condition_list = self._deal_values(condition)
condition_data = ' and '.join(condition_list)
sql = "update {table} set {values} where {condition}".format(table=table, values=update_data, condition=condition_data)
else:
sql = "update {table} set {values}".format(table=table, values=update_data)
print sql
self.__cursor.execute(sql)
self.__connect.commit()
self._close_db()
def get(self, table, show_list, condition=None, get_one=False):
"""
dbManager.get(table, show_list, [condition, get_one]) -> tupe
获取数据 返回一个元祖
str -> table 字符串类型
list -> show_list 列表类型
dict -> condition 字典类型
boolean -> get_one 布尔类型
"""
self._connect_db()
# 处理显示的数据
show_list = ",".join(show_list)
sql = "select {key} from {table}".format(key=show_list, table=table)
# 处理传入的条件
if condition:
condition_list = self._deal_values(condition)
condition_data = 'and'.join(condition_list)
sql = "select {key} from {table} where{condition}".format(key=show_list, table=table, condition=condition_data)
self.__cursor.execute(sql)
# 返回一条数据还是所有数据
if get_one:
result = self.__cursor.fetchone()
else:
result = self.__cursor.fetchall()
self._close_db()
return result
def _deal_values(self, value):
"""
self._deal_values(value) -> str or list
处理传进来的参数
"""
# 如果是字符串则加上''
if isinstance(value, str):
value = ("'{value}'".format(value=value))
# 如果是字典则变成key=value形式
elif isinstance(value, dict):
result = []
for key, value in value.items():
value = self._deal_values(value)
res = "{key}={value}".format(key=key, value=value)
result.append(res)
return result
else:
value = (str(value))
return value
方式二：去除mysql语句的操作
import pymysql
class MysqlHelper():
def __init__(self,host,port,db,user,passwd,charset='utf8'):
self.host = host
self.port = port
self.db = db
self.user = user
self.password = passwd
self.charset = charset
def open(self):
# 连接数据库
self.conn = pymysql.connect(host=self.host,port=self.port,db=self.db,
user=self.user,passwd=self.password,charset=self.charset)
# 创建游标对象
self.cursor = self.conn.cursor()
# 关闭
def close(self):
self.cursor.close()
self.conn.close()
# 增加、修改、删除命令语句
def cud(self,sql,params,msg="操作成功"):
try:
self.open()
# 处理逻辑数据，传入sql语句以及参数化
self.cursor.execute(sql,params)
# 执行事务
self.conn.commit()
self.close()
# 这样可以修改输出的操作成功信息提示
print(msg)
except Exception as e:
self.conn.rollback()
print("错误",e)
# 查询所有数据,多个值
def all(self,sql,params=()):
try:
self.open()
self.cursor.execute(sql,params)
data = self.cursor.fetchall()
self.close()
return data
except Exception as e:
print("错误", e)
