#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
from warnings import filterwarnings
filterwarnings('error', category = MySQLdb.Warning)

class MySQL :

    __conn = None
    __cursor = None

    def __init__(self, host, user, passwd, db ,port = 3306, charset = 'utf8'):
        try:
            self.__conn = MySQLdb.connect(host=host, user=user, passwd=passwd, db=db, port=port,charset=charset)
            self.__cursor = self.__conn.cursor()
        except MySQLdb.Warning, w:
            print "警告信息 %s" % str(w)
        except MySQLdb.Error, e:
            print "错误信息 %d %s" % (e.args[0], e.args[1])
    #插入数据
    def insert(self, table, dict):
        if len(dict) > 0 :
            keys = dict.keys()
            keyStr = ""
            valStr = ""
            for key in keys:
                keyStr += "`" + str(key) + '`,'
                valStr += "'" + str(dict[key]) + "',"
            keyStr = keyStr.rstrip(',')
            valStr = valStr.rstrip(',')
            sql = "INSERT INTO `" + str(table) + "` (" + keyStr + ") VALUES " + "("+ valStr +")"
            print '执行语句: %s' % sql
            try :
                self.__cursor.execute(sql)
                #lastId = self.__conn.insert_id() #这个一定要在commit之前！！！
                lastId = self.__cursor.lastrowid
                self.__conn.commit()
                return lastId
            except:
                print '插入数据异常!执行回滚!'
                self.__conn.rollback()
                return False
        else:
            return False

    #查询一条数据
    def find(self, table, fields = "*", where = "", order = 'id desc'):
        sql = "select " + str(fields) + " from `"+ str(table) + "` "
        if where != '':
            sql += " where " + str(where)
        sql += " order by " + str(order) + " limit 1"
        print "执行语句: %s" % sql
        try:
            self.__cursor.execute(sql)
            return self.__cursor.fetchone()
        except :
            print '查询失败'
            return False

    #查询多条数据
    def fetchall(self, table, fields = "*", where = "", order = "id desc", offset = "0", limit = '20'):
        sql = "select " + str(fields) + " from `" + str(table) + "` "
        if where != '':
            sql += " where " + str(where)
        sql += " order by " + str(order) + " limit " + str(offset) + "," + str(limit)
        print "执行语句: %s" % sql
        try:
            self.__cursor.execute(sql)
            return self.__cursor.fetchall()
        except:
            print '查询失败'
            return False
    # 删除数据
    def delete(self, table, where, limit = 1):
        sql = "delete from `" + str(table) + "` where " + str(where)
        if limit > 0 :
            sql += " limit " + str(limit)
        print '执行语句: %s' % sql
        try:
           self.__cursor.execute(sql)
           self.__conn.commit()
           return True
        except:
            print '删除异常！执行回滚!'
            self.__conn.rollback()
            return False

    # 更新数据
    def update(self, table, dict, where):
        keys = dict.keys()
        updateStr = ""
        for key in keys:
            updateStr += "`" + str(key) + "` = '" + str(dict[key]) +"',"
        updateStr = updateStr.rstrip(',')
        sql = "update `"+str(table)+"` set "+updateStr+" where " + where
        print '执行语句: %s' % sql
        try:
            self.__cursor.execute(sql)
            self.__conn.commit()
            return True
        except:
            print '更新异常！执行回滚！'
            self.__conn.rollback()
    # 释放资源
    def __del__(self):
        self.__cursor.close()
        self.__conn.close()

############################################案例##############

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import time
from MySQL import *
mysql = MySQL(host='10.200.10.203', user='test', passwd='test', db='test')
# print mysql.find('test', 'count(*) as t')
# print mysql.fetchall('test')
# print mysql.delete('test', 'id = 69')
create_time = time.strftime('%Y-%m-%d %H:%M:%S')
content ='这里是测试内容'
name = '测试'
dict = {'name':name,'create_time':create_time,'content':content}
lastid = mysql.insert('test', dict)
name = '测试最新的！！！'
dict = {'name':name}
print mysql.update('test',dict,"id = " + str(lastid))