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