#!/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))