Skip to content

Commit bdbf535

Browse files
zhanghe06zhanghe06
committed
更新postgres工具类
1 parent 3df72c7 commit bdbf535

1 file changed

Lines changed: 121 additions & 111 deletions

File tree

‎tools/postgres.py‎

Lines changed: 121 additions & 111 deletions
Original file line numberDiff line numberDiff line change
@@ -77,21 +77,23 @@ def truncate(self, table_name):
7777
if self.is_conn_open() is False:
7878
logger.error('连接已断开')
7979
return []
80+
# 参数判断
81+
if table_name is None:
82+
logger.error('查询表名缺少参数')
83+
return []
84+
sql = 'truncate table %s' % table_name
85+
logger.info(sql)
86+
cursor = self.conn.cursor()
8087
try:
81-
# 参数判断
82-
if table_name is None:
83-
logger.error('查询表名缺少参数')
84-
return []
85-
sql = 'truncate table %s' % table_name
86-
logger.info(sql)
87-
cursor = self.conn.cursor()
8888
cursor.execute(sql)
8989
self.conn.commit()
9090
logger.info('更新行数：%s' % cursor.rowcount)
9191
cursor.close()
9292
return True
9393
except Exception, e:
9494
logger.error(e)
95+
finally:
96+
cursor.close()
9597

9698
def get_columns_name(self, table_name):
9799
"""
@@ -102,21 +104,22 @@ def get_columns_name(self, table_name):
102104
if self.is_conn_open() is False:
103105
logger.error('连接已断开')
104106
return []
107+
# 参数判断
108+
if table_name is None:
109+
logger.error('查询表名缺少参数')
110+
return []
111+
cursor = self.conn.cursor()
112+
sql = "select column_name from information_schema.columns where table_name = '%s'" % table_name
113+
logger.info(sql)
105114
try:
106-
# 参数判断
107-
if table_name is None:
108-
logger.error('查询表名缺少参数')
109-
return []
110-
cursor = self.conn.cursor()
111-
sql = "select column_name from information_schema.columns where table_name = '%s'" % table_name
112-
logger.info(sql)
113115
cursor.execute(sql)
114116
result = cursor.fetchall()
115117
row = [item[0] for item in result]
116-
cursor.close()
117118
return row
118119
except Exception, e:
119120
logger.error(e)
121+
finally:
122+
cursor.close()
120123

121124
def get_row(self, table_name, condition=None):
122125
"""
@@ -126,29 +129,30 @@ def get_row(self, table_name, condition=None):
126129
if self.is_conn_open() is False:
127130
logger.error('连接已断开')
128131
return None
132+
# 参数判断
133+
if table_name is None:
134+
logger.error('查询表名缺少参数')
135+
return None
136+
if condition and not isinstance(condition, list):
137+
logger.error('查询条件参数格式错误')
138+
return None
139+
# 组装查询条件
140+
if condition:
141+
sql_condition = 'where '
142+
sql_condition += ' and '.join(condition)
143+
else:
144+
sql_condition = ''
145+
sql = 'select * from %s %s limit 1' % (table_name, sql_condition)
146+
logger.info(sql)
147+
cursor = self.conn.cursor()
129148
try:
130-
# 参数判断
131-
if table_name is None:
132-
logger.error('查询表名缺少参数')
133-
return None
134-
if condition and not isinstance(condition, list):
135-
logger.error('查询条件参数格式错误')
136-
return None
137-
# 组装查询条件
138-
if condition:
139-
sql_condition = 'where '
140-
sql_condition += ' and '.join(condition)
141-
else:
142-
sql_condition = ''
143-
sql = 'select * from %s %s limit 1' % (table_name, sql_condition)
144-
logger.info(sql)
145-
cursor = self.conn.cursor()
146149
cursor.execute(sql)
147150
row = cursor.fetchone()
148-
cursor.close()
149151
return row
150152
except Exception, e:
151153
logger.error(e)
154+
finally:
155+
cursor.close()
152156

153157
def get_rows(self, table_name, condition=None, limit='limit 10 offset 0'):
154158
"""
@@ -159,29 +163,30 @@ def get_rows(self, table_name, condition=None, limit='limit 10 offset 0'):
159163
if self.is_conn_open() is False:
160164
logger.error('连接已断开')
161165
return None
166+
# 参数判断
167+
if table_name is None:
168+
logger.error('查询表名缺少参数')
169+
return None
170+
if condition and not isinstance(condition, list):
171+
logger.error('查询条件参数格式错误')
172+
return None
173+
# 组装查询条件
174+
if condition:
175+
sql_condition = 'where '
176+
sql_condition += ' and '.join(condition)
177+
else:
178+
sql_condition = ''
179+
sql = 'select * from %s %s %s' % (table_name, sql_condition, limit)
180+
logger.info(sql)
181+
cursor = self.conn.cursor()
162182
try:
163-
# 参数判断
164-
if table_name is None:
165-
logger.error('查询表名缺少参数')
166-
return None
167-
if condition and not isinstance(condition, list):
168-
logger.error('查询条件参数格式错误')
169-
return None
170-
# 组装查询条件
171-
if condition:
172-
sql_condition = 'where '
173-
sql_condition += ' and '.join(condition)
174-
else:
175-
sql_condition = ''
176-
sql = 'select * from %s %s %s' % (table_name, sql_condition, limit)
177-
logger.info(sql)
178-
cursor = self.conn.cursor()
179183
cursor.execute(sql)
180184
rows = cursor.fetchall()
181-
cursor.close()
182185
return rows
183186
except Exception, e:
184187
logger.error(e)
188+
finally:
189+
cursor.close()
185190

186191
def get_count(self, table_name, condition=None):
187192
"""
@@ -191,30 +196,31 @@ def get_count(self, table_name, condition=None):
191196
if self.is_conn_open() is False:
192197
logger.error('连接已断开')
193198
return 0
199+
# 参数判断
200+
if table_name is None:
201+
logger.error('查询表名缺少参数')
202+
return 0
203+
if condition and not isinstance(condition, list):
204+
logger.error('查询条件参数格式错误')
205+
return 0
206+
# 组装查询条件
207+
if condition:
208+
sql_condition = 'where '
209+
sql_condition += ' and '.join(condition)
210+
else:
211+
sql_condition = ''
212+
sql = 'select count(*) from %s %s' % (table_name, sql_condition)
213+
logger.info(sql)
214+
cursor = self.conn.cursor()
194215
try:
195-
# 参数判断
196-
if table_name is None:
197-
logger.error('查询表名缺少参数')
198-
return 0
199-
if condition and not isinstance(condition, list):
200-
logger.error('查询条件参数格式错误')
201-
return 0
202-
# 组装查询条件
203-
if condition:
204-
sql_condition = 'where '
205-
sql_condition += ' and '.join(condition)
206-
else:
207-
sql_condition = ''
208-
sql = 'select count(*) from %s %s' % (table_name, sql_condition)
209-
logger.info(sql)
210-
cursor = self.conn.cursor()
211216
cursor.execute(sql)
212217
row = cursor.fetchone()
213218
count = row[0]
214-
cursor.close()
215219
return count
216220
except Exception, e:
217221
logger.error(e)
222+
finally:
223+
cursor.close()
218224

219225
def output_row(self, table_name, condition=None, style=0):
220226
"""
@@ -297,37 +303,38 @@ def update(self, table_name, update_field, condition=None):
297303
if self.is_conn_open() is False:
298304
logger.error('连接已断开')
299305
return False
306+
# 参数判断
307+
if not table_name or not update_field:
308+
logger.error('更新数据缺少参数')
309+
return False
310+
if not isinstance(update_field, list) or (condition and not isinstance(condition, list)):
311+
logger.error('更新数据参数格式错误')
312+
return False
313+
# 组装更新字段
314+
if update_field:
315+
sql_update_field = 'set '
316+
sql_update_field += ' and '.join(update_field)
317+
else:
318+
sql_update_field = ''
319+
# 组装更新条件
320+
if condition:
321+
sql_condition = 'where '
322+
sql_condition += ' and '.join(condition)
323+
else:
324+
sql_condition = ''
325+
# 拼接sql语句
326+
sql = 'update %s %s %s' % (table_name, sql_update_field, sql_condition)
327+
logger.info(sql)
328+
cursor = self.conn.cursor()
300329
try:
301-
# 参数判断
302-
if not table_name or not update_field:
303-
logger.error('更新数据缺少参数')
304-
return False
305-
if not isinstance(update_field, list) or (condition and not isinstance(condition, list)):
306-
logger.error('更新数据参数格式错误')
307-
return False
308-
# 组装更新字段
309-
if update_field:
310-
sql_update_field = 'set '
311-
sql_update_field += ' and '.join(update_field)
312-
else:
313-
sql_update_field = ''
314-
# 组装更新条件
315-
if condition:
316-
sql_condition = 'where '
317-
sql_condition += ' and '.join(condition)
318-
else:
319-
sql_condition = ''
320-
# 拼接sql语句
321-
sql = 'update %s %s %s' % (table_name, sql_update_field, sql_condition)
322-
logger.info(sql)
323-
cursor = self.conn.cursor()
324330
cursor.execute(sql)
325331
self.conn.commit()
326332
logger.info('更新行数：%s' % cursor.rowcount)
327-
cursor.close()
328333
return True
329334
except Exception, e:
330335
logger.error(e)
336+
finally:
337+
cursor.close()
331338

332339
def delete(self, table_name, condition=None):
333340
"""
@@ -337,29 +344,30 @@ def delete(self, table_name, condition=None):
337344
if self.is_conn_open() is False:
338345
logger.error('连接已断开')
339346
return False
347+
# 参数判断
348+
if condition and not isinstance(condition, list):
349+
logger.error('删除数据参数格式错误')
350+
return False
351+
# 组装删除条件
352+
if condition:
353+
sql_condition = 'where '
354+
sql_condition += ' and '.join(condition)
355+
else:
356+
sql_condition = ''
357+
# 拼接sql语句
358+
sql = 'delete from %s %s' % (table_name, sql_condition)
359+
logger.info(sql)
360+
cursor = self.conn.cursor()
340361
try:
341-
# 参数判断
342-
if condition and not isinstance(condition, list):
343-
logger.error('删除数据参数格式错误')
344-
return False
345-
# 组装删除条件
346-
if condition:
347-
sql_condition = 'where '
348-
sql_condition += ' and '.join(condition)
349-
else:
350-
sql_condition = ''
351-
# 拼接sql语句
352-
sql = 'delete from %s %s' % (table_name, sql_condition)
353-
logger.info(sql)
354-
cursor = self.conn.cursor()
355362
cursor.execute(sql)
356363
self.conn.commit()
357364
logger.info('删除行数：%s' % cursor.rowcount)
358-
cursor.close()
359365
logger.info('删除成功')
360366
return True
361367
except Exception, e:
362368
logger.error(e)
369+
finally:
370+
cursor.close()
363371

364372
def query_by_sql(self, sql=None):
365373
"""
@@ -374,18 +382,19 @@ def query_by_sql(self, sql=None):
374382
return None
375383
# 安全性校验
376384
sql = sql.lower()
385+
logger.info(sql)
377386
if not sql.startswith('select'):
378387
logger.error('未授权的操作')
379388
return None
389+
cursor = self.conn.cursor()
380390
try:
381-
cursor = self.conn.cursor()
382-
logger.info(sql)
383391
cursor.execute(sql)
384392
rows = cursor.fetchall()
385-
cursor.close()
386393
return rows
387394
except Exception, e:
388395
logger.error(e)
396+
finally:
397+
cursor.close()
389398

390399
def update_by_sql(self, sql=None):
391400
"""
@@ -400,17 +409,18 @@ def update_by_sql(self, sql=None):
400409
return False
401410
# 安全性校验
402411
sql = sql.lower()
412+
logger.info(sql)
403413
if not (sql.startswith('update') or sql.startswith('insert') or sql.startswith('delete')):
404414
logger.error('未授权的操作')
405415
return False
416+
cursor = self.conn.cursor()
406417
try:
407-
logger.info(sql)
408-
cursor = self.conn.cursor()
409418
cursor.execute(sql)
410419
self.conn.commit()
411420
logger.info('影响行数：%s' % cursor.rowcount)
412-
cursor.close()
413421
logger.info('执行成功')
414422
return True
415423
except Exception, e:
416424
logger.error(e)
425+
finally:
426+
cursor.close()

0 commit comments

Comments
 (0)