python封装类连接mysql数据_Python封装的访问MySQL数据库的类及DEMO

1 #Filename：mysql_class.py

2 #Author:Rain.Zen; Date: 2014-04-15

3

4 importMySQLdb5

6 classMyDb:7

8 '''初始化[类似于构造函数]'''

9 def __init__(self, host, user, password, charset = 'utf8'):10 self.host =host11 self.user =user12 self.password =password13 self.charset =charset14 try:15 self.conn = MySQLdb.connect(host = self.host, user = self.user, passwd = self.password, charset =self.charset)16 self.cur =self.conn.cursor()17 exceptMySQLdb.Error as e:18 print('MySQL Error %d: %s' % (e.args[0], e.args[1]))19

20 '''组合字符串'''

21 def joinData(self, data, char = ','):22 returnchar.join(data)23

24 '''解析条件语句，参数类型[{dict}, 'AND|OR'] | {dict}'''

25 defparseMap(self, condition) :26 ifisinstance(condition, list) :27 dictMap =condition[0]28 logic = ' '+ condition[1] +' '

29 else:30 dictMap =condition31 logic = 'AND'

32

33 ifisinstance(dictMap, dict) :34 for k, v indictMap.items():35 dictMap[k] = "`"+ k +"` = '"+ str(v) +"'"

36 returnself.joinData(dictMap.values(), logic)37 else:38 return '1 = 1'

39

40 '''选择数据表'''

41 defselectDb(self, db):42 try:43 self.conn.select_db(db)44 exceptMySQLdb.Error as e:45 print('MySQL Error %d: %s' % (e.args[0], e.args[1]))46

47 '''执行SQL语句'''

48 defquery(self, sql):49 try:50 returnself.cur.execute(sql)51 exceptMySQLdb.Error as e:52 print 'MySQL Error: %s \nSQL: %s' %(e, sql)53

54 '''添加一条信息'''

55 defaddOne(self, table, dictData):56 for field, value indictData.items():57 dictData[field] = "'"+ str(value) +"'"

58 self.query('INSERT INTO' + table + "("+ self.joinData(dictData.keys()) +') VALUES('+ self.joinData(dictData.values()) +')')59 returnself.cur.lastrowid60

61 '''修改一条信息[根据条件]'''

62 defsaveOne(self, table, dictData, condition):63 for key, val indictData.items():64 dictData[key] = "`"+ key +"` = '"+ str(val) +"'"

65 save_sql = 'UPDATE' + table + 'SET' + self.joinData(dictData.values()) + 'WHERE' +self.parseMap(condition)66 returnself.query(save_sql)67

68 '''删除信息[根据条件]'''

69 defdeleteOne(self, table, condition):70 return self.query('DELETE FROM' + table + 'WHERE' +self.parseMap(condition))71

72 '''读取单条信息[根据条件]'''

73 def fetchOne(self, tabel, condition, fields = '*', dataType =0):74 field = isinstance(fields, list) and self.joinData(fields) orfields75 self.query('SELECT'+ field +'FROM' + tabel + 'WHERE' +self.parseMap(condition))76 tupleData =self.cur.fetchone()77 if dataType == 0 or fields == '*':78 returntupleData79 else:80 dictData ={}81 n =082 for k infields:83 dictData[k] =tupleData[n]84 n = n + 1

85 returndictData86

87 '''读取多条信息[根据条件]'''

88 def fetchSome(self, tabel, condition, fields = '*', dataType =0):89 field = isinstance(fields, list) and self.joinData(fields) orfields90 self.query('SELECT'+ field +'FROM' + tabel + 'WHERE' +self.parseMap(condition))91 tupleData =self.cur.fetchall()92 count =self.cur.rowcount93 if count >0:94 if dataType == 0 or fields == '*':95 return(int(count), tupleData)96 else:97 listData =[]98 for row intupleData:99 dictData ={}100 n =0101 for k infields:102 dictData[k] =row[n]103 n = n + 1

104 listData.append(dictData)105 return(int(count), listData)106 else:107 returnFalse108

109 '''获取信息总数[根据条件]'''

110 def getCount(self, tabel, condition =0):111 where = isinstance(condition, dict) and 'WHERE' + self.parseMap(condition) or ''

112 self.query('SELECT 0 FROM' + tabel +where)113 returnself.cur.rowcount114

115 '''提交事务'''

116 defcommit(self):117 self.conn.commit()118

119 '''关闭句柄和连接'''

120 defclose(self):121 self.cur.close()122 self.conn.close()