封装对MySQL数据库操作
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb
class OperateDB(object):
def __init__(self, host_db="localhost", user_db="root", passwd_db="123456", name_db="apitest", port_db=3306):
try:
self.conn = MySQLdb.connect(host=host_db, user=user_db, passwd=passwd_db, db=name_db, port=port_db,
charset="utf8")
self.cur = self.conn.cursor(cursorclass=MySQLdb.cursors.DictCursor) # 返回字典
print u"数据库连接成功"
except MySQLdb.Error as e:
print u"数据库连接失败 %d:%s" % (e.args[0], e.args[1])
def queryOne(self, sql):
try:
self.cur.execute(sql)
results = self.cur.fetchone()
result = {"code": 200, "message": u"单条查询成功", "data": results}
print result
return result
except MySQLdb.Error as e:
print "查询失败 %d:%s" % (e.args[0], e.args[1])
def modifyOne(self, sql):
try:
results = self.cur.execute(sql)
self.conn.commit()
result = {"code": 200, "message": "单条增删改成功", "data": int(results)}
print result
return result
except MySQLdb.Error as e:
print "单条增删改失败 %d:%s" % (e.args[0], e.args[1])
def insertMany(self, sql, content):
'''
insertMany("insert into config_total(key_config,value_config) values (%s,%s)",[("test1","test1"),("test2","test2")])
'''
try:
results = self.cur.executemany(sql, content)
self.conn.commit()
result = {"code": 200, "message": "增加多条记录成功", "data": int(results)}
print result
return result
except MySQLdb.Error.args as e:
print "增加多条记录失败 %d:%s" % (e.args[0], e.args[1])
def queryMany(self, sql):
try:
self.cur.execute(sql)
results = self.cur.fetchall()
result = {"code": 200, "message": "查询多条记录成功", "data": results}
print result
return result
except MySQLdb.Error.args as e:
print "查询多条记录失败 %d:%s" % (e.args[0], e.args[1])
def __del__(self):
if self.cur != None:
self.cur.close()
if self.conn != None:
self.conn.close()
if __name__ == "__main__":
op = OperateDB()
op.queryOne("select * from config_total limit 1")
# op.modifyOne("insert into config_total(key_config,value_config) values('test','test')")
# op.modifyOne("update config_total set key_config='test1' where key_config like 'test%'")
# op.modifyOne("delete from config_total where key_config like 'test%'")
# op.insertMany("insert into config_total(key_config,value_config) values (%s,%s)",
# [("test1", "test1"), ("test2", "test2")])
# op.queryMany("select * from config_total")
