mysql完美封装_Mysql封装

#-*- 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()