#-*- encoding:utf-8 -*-
import MySQLdb
class MySql(object):
""" 封装一个mysql类，提供一些简单的方法 """
def __init__(self, host, user, passwd, db):
""" 初始化变量 """
self.host = host
self.user = user
self.passwd = passwd
self.db = db
def connect_db(self):
""" 连接数据库 """
try:
self.conn = MySQLdb.connect(host=self.host, user=self.user, passwd=self.passwd, db=self.db, charset="utf8")
self.conn.ping(True)
self.cursor = self.conn.cursor()
except:
self.conn = None
self.cursor = None
def insert_data(self, tb_name, **kwargs):
""" 向数据库中插入数据 """
#注掉的获取key和value的方法也可行
keys =",".join([str(i)for iin kwargs.keys()])
values ="','".join([str(i)for iin kwargs.values()])
insert_sql ="insert into %s (%s) values ('%s')" % (tb_name, keys, values)
try:
self.cursor.execute(insert_sql)
self.commit()
except:
self.rollback()
def select_data(self, tb_name, *args, **kwargs):
""" 根据给定的条件获取指定表中的数据 """
cmd = " AND ".join(["%s='%s'"% (key, value) for key, value in kwargs.iteritems()])
condition_cmd = " WHERE %s" % cmd if cmd else ""
select_sql = "select %s from %s %s" % (",".join(args), tb_name, condition_cmd)
self.cursor.execute(select_sql)
records = self.cursor.fetchall()
return records
def update_data(self, tb_name, new_data, **kwargs):
""" 更新数据库 new_data是一个字典"""
new_data_cmd = ", ".join(["%s='%s'" % (i, new_data[i]) for i in new_data])
update_cmd = " AND ".join(["%s='%s'"% (key, value) for key, value in kwargs.iteritems()])
condition_cmd = " WHERE %s" % update_cmd if update_cmd else ""
update_sql = "update %s SET %s %s" % (tb_name, new_data_cmd, condition_cmd)
try:
self.cursor.execute(update_sql)
self.commit()
except:
self.rollback()
def delete_data(self, tb_name, **kwargs):
""" 删除数据 """
cmd = " AND ".join(["%s='%s'"% (k, v) for k, v in kwargs.iteritems()])
delete_condition = " WHERE %s" % cmd if cmd else ""
delete_sql ="delete from %s %s" % (tb_name, delete_condition)
try:
self.cursor.execute(delete_sql)
self.commit()
except:
self.rollback()
def commit(self):
self.conn.commit()
def rollback(self):
self.conn.rollback()
def close_db(self):
""" 关闭数据库 """
self.cursor.close()
self.conn.close()
网友评论