根据CI模式弄的一个python3访问MySql数据库的class

python 3及以上的。mysql的。可能会有小问题。主要是没时间仔细测试

#!/usr/bin/python

-- coding: utf-8 --

######################

数据库操作类

######################

from sqlite3 import Cursor
import pymysql
import string
import re
import datetime
import time
import traceback
import sys

class mysql:
test_db = {“host”: “localhost”, “user”: “root”,
“passwd”: “*******”, “db”: “adsys”, “port”: “3306”, “charset”: “utf8”}

def __init__(self, conn_config):
    self.conn_config = conn_config
    self.conn = None
    self.cur = None
    self.where_sql = ""
    self.where_params = []

def __del__(self):
    if self.conn != None:
        self.conn.close
        self.conn = None
    if self.where_sql != None and self.where_sql != "":
        self.where_sql = None
        self.where_params = None

def close(self):
    if self.conn != None:
        self.conn.close
        self.conn = None
    if self.where_sql != None and self.where_sql != "":
        self.where_sql = ""
        self.where_params = []

def connection(self):
    if self.conn == None:
        self.conn = pymysql.connect(host=self.conn_config["host"], user=self.conn_config["user"], passwd=self.conn_config["passwd"],
                                    db=self.conn_config["db"], port=string.atoi(self.conn_config["port"]), charset=self.conn_config["charset"])
        self.cur = self.conn.cursor(Cursor="DictCursor")

# 执行SQL并根据SQL访问数据
# 如SQL是select语句则返回全部查询数据
# 如SQL是insert语句则返回插入的ID
# 其它的不返回
def exec_sql(self, sql, args=None, disconnect=True):
    try:
        self.connection()
        if re.search("^\s*SELECT", sql, re.IGNORECASE) != None:
            if args != None:
                self.cur.execute(sql, args)
            else:
                self.cur.execute(sql)
            data = self.cur.fetchall()
            self.conn.commit()
            return data

        if re.search("^\s*INSERT\s+INTO[\s\S]+VALUES", sql, re.IGNORECASE) != None:
            if args != None:
                self.cur.execute(sql, args)
            else:
                self.cur.execute(sql)
            insert_id = self.conn.insert_id()
            self.conn.commit()
            return insert_id

        if args != None:
            self.cur.execute(sql, args)
        else:
            self.cur.execute(sql)
        self.conn.commit()
    except Exception as e:
        if self.conn != None:
            self.conn.rollback()
        raise e
    finally:
        if disconnect and self.conn != None:
            self.conn.close()
            self.conn = None

# 插入[insert]
# input table 表名
# input data 数据的关联字典
# out insert_id
def insert(self, table, data, disconnect=True):
    sql = ""
    try:
        keys = ""
        values = ""
        sqlparams = []
        for key, value in data.items():
            keys += "," + key if len(keys) > 0 else key
            values += ",%s" if len(values) > 0 else "%s"
            if type(value) == datetime.datetime:
                sqlparams.append(value.strftime("%Y-%m-%d %H:%M:%S"))
                continue
            if type(value) == time.struct_time:
                sqlparams.append(time.strftime("%Y-%m-%d %H:%M:%S", value))
                continue
            sqlparams.append(value)
        sql = "INSERT INTO " + table + \
            " (" + keys + ") VALUES (" + values + ")"
        self.connection()
        self.cur.execute(sql, tuple(sqlparams))
        insert_id = self.conn.insert_id()
        self.conn.commit()
        return insert_id
    except Exception as e:
        if self.conn != None:
            self.conn.rollback()
        raise e
    finally:
        if disconnect and self.conn != None:
            self.conn.close()
            self.conn = None
        if self.where_sql != None and self.where_sql != "":
            self.where_sql = ""
            self.where_params = []

# 批量插入[insert_batch]
# input table 表名
# input data 数据的关联元组字典
# out insert_id
def insert_batch(self, table, data, disconnect=True):
    sql = ""
    try:
        keys = ""
        values = ""
        args = range(len(data))
        for key, value in data[0].items():
            keys += "," + key if len(keys) > 0 else key
            values += ",%s" if len(values) > 0 else "%s"
        sql = "INSERT INTO " + table + \
            " (" + keys + ") VALUES (" + values + ")"
        i = 0
        for row in data:
            sqlparams = []
            for key, value in data[i].items():
                if type(value) == datetime.datetime:
                    sqlparams.append(value.strftime("%Y-%m-%d %H:%M:%S"))
                    continue
                if type(value) == time.struct_time:
                    sqlparams.append(time.strftime(
                        "%Y-%m-%d %H:%M:%S", value))
                    continue
                sqlparams.append(value)
            args[i] = tuple(sqlparams)
            i += 1
        self.connection()
        self.cur.executemany(sql, args)
        insert_id = self.conn.insert_id()
        self.conn.commit()
        return insert_id
    except Exception as e:
        if self.conn != None:
            self.conn.rollback()
        raise e
    finally:
        if disconnect and self.conn != None:
            self.conn.close()
            self.conn = None
        if self.where_sql != None and self.where_sql != "":
            self.where_sql = ""
            self.where_params = []

# 获取insert sql string[insert_string]
# input table 表名
# input data 数据的关联字典
def insert_string(self, table, data):
    keys = ''
    values = ''
    for key, value in data.items():
        keys += "," + key if len(keys) > 0 else key
        temp_value = ""
        if type(value) == datetime.datetime:
            temp_value = value.strftime("%Y-%m-%d %H:%M:%S")
            values += "," + temp_value if len(values) > 0 else temp_value
            continue
        if type(value) == time.struct_time:
            temp_value = time.strftime("%Y-%m-%d %H:%M:%S", value)
            values += "," + temp_value if len(values) > 0 else temp_value
            continue
        values += ",'%s'" % ('%s' % value).replace("'", "\\'") if len(
            values) > 0 else "'%s'" % ('%s' % value).replace("'", "\\'")
    sql = "INSERT INTO " + table + \
        " (" + keys + ") VALUES (" + values + ")"
    return sql

# 获取insert sql string ...  on duplicate key update ...  [insert_string]
# input table 表名
# input data 数据的关联字典
# input update_data 可以是字符串也可以是字典
# out sql string
def insert_on_duplicate_key_update_string(self, table, data, update_data=None):
    sql = self.insert_string(table, data)
    update_str = ''
    if update_data == None:
        update_data = data
    update_type = type(update_data)
    if isinstance(update_data, str):
        sql += " ON DUPLICATE KEY UPDATE " + update_data
    else:
        for k, v in update_data.items():
            update_str += "%s = '%s'" % (k, ('%s' % v).replace("'", "\\'")) if len(
                update_str) == 0 else ", %s = '%s'" % (k, ('%s' % v).replace("'", "\\'"))
        sql += " ON DUPLICATE KEY UPDATE " + update_str
    return sql

# where条件[where]默认为and
# input data 条件的关联字典
# 用法：{"字段名 >":"值"}
def where(self, data):
    for key, value in data.items():
        self.where_sql += " AND " + key + \
            " %s" if len(self.where_sql) > 0 else " WHERE " + key + " %s"
    self.where_params.extend(data.values())

# where or 条件[where]
# input data 条件的关联字典
# 用法：{"字段名 >":"值"} data为一组即如果data有多项时 and (a > 0 or b > 0)。如果一项时则 or a > 0
def where_or(self, data):
    data_items = data.items()
    if len(data_items) > 1:
        for key, value in data_items:
            self.where_sql += " OR " + key + \
                " %s" if len(
                    self.where_sql) > 0 else " WHERE ( " + key + " %s"
        self.where_sql += " )"
    else:
        if len(data_items) > 0:
            for key, value in data_items:
                self.where_sql += " OR " + key + \
                    " %s" if len(self.where_sql) > 0 else " WHERE " + key
    self.where_params.extend(data.values())

# 更新[update]
# input table 表名
# input data 数据的关联字典
# input where 条件的关联字典
def update(self, table, data, where=None, disconnect=True):
    sql = ""
    try:
        set_str = ""
        params = data.values()
        for key, value in data.items():
            set_str += ", " + key + \
                " = %s" if len(set_str) > 0 else key + " = %s"
        sql = "UPDATE " + table + " SET " + set_str
        if where != None:
            self.where(where)
        if len(self.where_sql) > 0:
            sql += self.where_sql
        if len(self.where_params) > 0:
            params.extend(self.where_params)
        self.connection()
        self.cur.execute(sql, tuple(params))
        self.conn.commit()
    except Exception as e:
        if self.conn != None:
            self.conn.rollback()
        raise e
    finally:
        if disconnect and self.conn != None:
            self.conn.close()
            self.conn = None
        if self.where_sql != None and self.where_sql != "":
            self.where_sql = ""
            self.where_params = []

# 获取update sql string[update_string]
# input table 表名
# input data 数据的关联字典
# input where 条件的关联字典
# out sql string
def update_string(self, table, data, where):
    set_str = ""
    for key, value in data.items():
        temp_value = ""
        if type(value) == datetime.datetime:
            temp_value = value.strftime("%Y-%m-%d %H:%M:%S")
            set_str += ", " + key + " = " + \
                temp_value if len(set_str) > 0 else key + \
                " = " + temp_value
            continue
        if type(value) == time.struct_time:
            temp_value = time.strftime("%Y-%m-%d %H:%M:%S", value)
            set_str += ", " + key + " = " + \
                temp_value if len(set_str) > 0 else key + \
                " = " + temp_value
            continue
        set_str += ", " + key + \
            " = %s" % value if len(set_str) > 0 else key + " = %s" % value
    sql = "UPDATE " + table + " SET " + set_str
    where_str = ""
    for key, value in where.items():
        where_str += " AND " + key + \
            " = %s" % value if len(
                where_str) > 0 else " WHERE " + key + " = %s" % value
    sql += where_str
    return sql

# 删除[delete]
# input table 表名
# input where 条件的关联字典
def delete(self, table, where=None, disconnect=True):
    sql = ""
    try:
        params = None
        sql = "DELETE FROM " + table
        if where != None:
            self.where(where)
        if len(self.where_sql) > 0:
            sql += self.where_sql
        if len(self.where_params) > 0:
            params = self.where_params
        self.connection()
        self.cur.execute(sql, tuple(params))
        self.conn.commit()
    except Exception as e:
        if self.conn != None:
            self.conn.rollback()
        raise e
    finally:
        if disconnect and self.conn != None:
            self.conn.close()
            self.conn = None
        if self.where_sql != None and self.where_sql != "":
            self.where_sql = ""
            self.where_params = []
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317