Python操作数据库类 Oracle、Sqlserver、PostgreSQL
我在工作中经常使用Python，特点很明显，轻量，效率还不错，尤其在维护或者自动化方面。
下面是我使用到的访问数据库（Oracle、Sqlserver、PostgreSQL）的公共类。
一、Oracl访问类
import cx_Oracle class DBOrc: # Oracle 连接工具 SHOW_SQL = True def __init__(self, host, port, user, password, sid): self.host = host self.port = port self.user = user self.password = password self.sid = sid self._conn = None self .__GetConnect() def __GetConnect(self): if not self._conn: dsn_tns = cx_Oracle.makedsn(self.host, self.port, self.sid) dsn_tns = dsn_tns.replace('SID', 'SERVICE_NAME') self._conn = cx_Oracle.connect(self.user, self.password, dsn_tns) else: pass def __del__(self): if self._conn: self._conn.close() self._conn = None def _NewCursor(self): cur = self._conn.cursor() if cur: return cur else: print("#Error# Get New Cursor Failed.") return None def _DelCursor(self, cur): if cur: cur.close() # 检查是否允许执行的sql语句 def _PermitedUpdateSql(self, sql): rt = True lrsql = sql sql_elems = lrsql.strip().split() sql_elems2 = [x.upper() for x in sql_elems] # update和delete最少有四个单词项 if len(sql_elems2) < 4: rt = False # 更新删除语句，判断首单词，不带where语句的sql不予执行 elif ('UPDATE' in sql_elems2 or 'DELETE' in sql_elems2): if 'WHERE' not in sql_elems2: rt = False return rt def _commit(self): self._conn.commit() def _rollback(self): self._conn.rollback() # 检查是否允许执行的sql语句 def CheckSelectSql(self, sql): rt = True lrsql = sql sql_elems = lrsql.strip().split() sql_elems2 = [x.upper() for x in sql_elems] # update和delete最少有四个单词项 if len(sql_elems2) < 4: rt = False # 更新删除语句，判断首单词，不带where语句的sql不予执行 elif ('UPDATE' in sql_elems2 or 'DELETE' in sql_elems2 or 'INSERT' in sql_elems2): rt = False return rt # 查询 def Query(self, sql, nStart=0, nNum=- 1): rt = [] # 获取cursor cur = self._NewCursor() if not cur: return rt # 查询到列表 print(sql) cur.execute(sql) if (nStart == 0) and (nNum == 1): rt.append(cur.fetchone()) else: rs = cur. fetchall() if nNum == - 1: rt.extend(rs[nStart:]) else: rt.extend(rs[nStart:nStart + nNum]) # 释放cursor self._DelCursor(cur) return rt # 更新 def Exec(self, sql): # 获取cursor rt = None cur = self._NewCursor() if not cur: return rt # 判断sql是否允许其执行 if not self._PermitedUpdateSql(sql): return rt # 执行语句 print(sql) try: rt = cur.execute(sql) self._commit() # 释放cursor self._DelCursor(cur) # return rt return "success" except Exception as e: print(e) self._conn.rollback() return str(e) # 更新list def Execlist(self, sqls): # 获取cursor rt = None cur = self._NewCursor() if not cur: return rt try: for i in sqls: # 判断sql是否允许其执行 if len(i.strip()) < 1: continue if not self._PermitedUpdateSql(i): self._rollback() return rt # 执行语句 print(i) rt = cur.execute(i) self._commit() # 释放cursor self._DelCursor(cur) # return rt return "success" except Exception as e: print(e) self._conn.rollback() return str(e) # 导出结果为文件 def Export(self, sql, file_name, colfg='||'): rt = self. Query(sql) if rt: with open(file_name, 'a') as fd: for row in rt: ln_info = '' for col in row: ln_info += str(col) + colfg ln_info += '\n' fd .write(ln_info)
oracle版本使用方法：
# 查询单个sql orc = DBOrc(host, port, db, user, password) sql = "select c1,c2,c3 from table1" orc.Query(sql ) # 更新单个sql sql = "update table1 set c1=0" orc.Exec(sql ) # 更新多条sql sqllist = [] sqllist.append("update table1 set c1=0") sqllist.append("update table1 set c2=0") orc.Execlist(sqllist)
二、Sqlserver访问类
import pymssql from pymssql import _mssql from pymssql import _pymssql class DBMSS: # MSS 连接工具 def __init__(self, host, port, user, password, db): self.host = host+":"+port self.user = user self.password = password self.db = db self._conn = None self .__GetConnect() def __GetConnect(self): if not self._conn: self._conn = pymssql.connect(host=self.host, user=self.user, password=self.password, database=self.db, charset="utf8") else: pass def __del__(self): if self._conn: self._conn.close() self._conn = None def _NewCursor(self): cur = self._conn.cursor() if cur: return cur else: print("#Error# Get New Cursor Failed.") return None def _DelCursor(self, cur): if cur: cur.close() # 检查是否允许执行的sql语句 def _PermitedUpdateSql(self, sql): rt = True lrsql = sql sql_elems = lrsql.strip().split() sql_elems2 = [x.upper() for x in sql_elems] # update和delete最少有四个单词项 if len(sql_elems2) < 4: rt = False # 更新删除语句，判断首单词，不带where语句的sql不予执行 elif ('UPDATE' in sql_elems2 or 'DELETE' in sql_elems2): if 'WHERE' not in sql_elems2: rt = False return rt def _commit(self): self._conn.commit() def _rollback(self): self._conn.rollback() # 检查是否允许执行的sql语句 def CheckSelectSql(self, sql): rt = True lrsql = sql sql_elems = lrsql.strip().split() sql_elems2 = [x.upper() for x in sql_elems] # update和delete最少有四个单词项 if len(sql_elems2) < 4: rt = False # 更新删除语句，判断首单词，不带where语句的sql不予执行 elif ('UPDATE' in sql_elems2 or 'DELETE' in sql_elems2 or 'INSERT' in sql_elems2): rt = False return rt # 查询 def Query(self, sql, nStart=0, nNum=- 1): rt = [] # 获取cursor cur = self._NewCursor() if not cur: return rt # 查询到列表 print(sql) cur.execute(sql) if (nStart == 0) and (nNum == 1): rt.append(cur.fetchone()) else: rs = cur. fetchall() if nNum == - 1: rt.extend(rs[nStart:]) else: rt.extend(rs[nStart:nStart + nNum]) # 释放cursor self._DelCursor(cur) return rt # 更新 def Exec(self, sql): # 获取cursor rt = None cur = self._NewCursor() if not cur: return rt # 判断sql是否允许其执行 if not self._PermitedUpdateSql(sql): return rt # 执行语句 print(sql) try: rt = cur.execute(sql) self._commit() # 释放cursor self._DelCursor(cur) # return rt return "success" except Exception as e: print(e) self._conn.rollback() return str(e) # 更新list def Execlist(self, sqls): # 获取cursor rt = None cur = self._NewCursor() if not cur: return rt try: for i in sqls: # 判断sql是否允许其执行 if len(i.strip()) < 1: continue if not self._PermitedUpdateSql(i): self._rollback() return rt # 执行语句 print(i) rt = cur.execute(i) self._commit() # 释放cursor self._DelCursor(cur) # return rt return "success" except Exception as e: print(e) self._conn.rollback() return str(e) # 导出结果为文件 def Export(self, sql, file_name, colfg='||'): rt = self. Query(sql) if rt: with open(file_name, 'a') as fd: for row in rt: ln_info = '' for col in row: ln_info += str(col) + colfg ln_info += '\n' fd .write(ln_info)
SqlServer版使用方法：
# 查询单个sql mss = DBMSS(host, port, db, user, password) sql = "select c1,c2,c3 from table1" mss.Query(sql ) # 更新单个sql sql = "update table1 set c1=0" mss.Exec(sql ) # 更新多条sql sqllist = [] sqllist.append("update table1 set c1=0") sqllist.append("update table1 set c2=0") mss.Execlist(sqllist)
三、PostgreSQL访问类
import psycopg2 class DBPg: # PG 连接工具 def __init__(self, host, port, db, user, password): self.host = host self.port = port self.db = db self.user = user self.password = password self._conn = None self._conn = self.__GetConnect() def try_except(self): def wrapper(*args, **kwargs): try: self(*args, **kwargs) except Exception as e: print("get error: %s" % e) return wrapper # @try_except def __GetConnect(self): if not self._conn: return psycopg2.connect(database=self.db, user=self.user, password=self.password, host=self.host, port=self.port) else: pass def __del__(self): if self._conn: self._conn.close() self._conn = None def _NewCursor(self): cur = self._conn.cursor() if cur: return cur else: print("#Error# Get New Cursor Failed.") return None def _DelCursor(self, cur): if cur: cur.close() # 检查是否允许执行的sql语句 def _PermitedUpdateSql(self, sql): rt = True lrsql = sql sql_elems = lrsql.strip().split() sql_elems2 = [x.upper() for x in sql_elems] # update和delete最少有四个单词项 if len(sql_elems2) < 4: rt = False # 更新删除语句，判断首单词，不带where语句的sql不予执行 elif ('UPDATE' in sql_elems2 or 'DELETE' in sql_elems2): if 'WHERE' not in sql_elems2: rt = False return rt def _commit(self): self._conn.commit() def _rollback(self): self._conn.rollback() # 检查是否允许执行的sql语句 def CheckSelectSql(self, sql): rt = True lrsql = sql sql_elems = lrsql.strip().split() sql_elems2 = [x.upper() for x in sql_elems] # update和delete最少有四个单词项 if len(sql_elems2) < 4: rt = False # 更新删除语句，判断首单词，不带where语句的sql不予执行 elif ('UPDATE' in sql_elems2 or 'DELETE' in sql_elems2 or 'INSERT' in sql_elems2): rt = False return rt # 查询 def Query(self, sql, nStart=0, nNum=- 1): rt = [] # 获取cursor cur = self._NewCursor() if not cur: return rt # 查询到列表 print(sql) cur.execute(sql) if (nStart == 0) and (nNum == 1): rt.append(cur.fetchone()) else: rs = cur. fetchall() if nNum == - 1: rt.extend(rs[nStart:]) else: rt.extend(rs[nStart:nStart + nNum]) # 释放cursor self._DelCursor(cur) return rt # 更新 def Exec(self, sql): # 获取cursor rt = None cur = self._NewCursor() if not cur: return rt # 判断sql是否允许其执行 if not self._PermitedUpdateSql(sql): return rt # 执行语句 print(sql) try: rt = cur.execute(sql) self._commit() # 释放cursor self._DelCursor(cur) # return rt return "success" except Exception as e: print(e) self._conn.rollback() return str(e) # 更新list def Execlist(self, sqls): # 获取cursor rt = None cur = self._NewCursor() if not cur: return rt try: for i in sqls: # 判断sql是否允许其执行 if len(i.strip()) < 1: continue if not self._PermitedUpdateSql(i): self._rollback() return rt # 执行语句 print(i) rt = cur.execute(i) self._commit() # 释放cursor self._DelCursor(cur) # return rt return "success" except Exception as e: print(e) self._conn.rollback() return str(e) # 导出结果为文件 def Export(self, sql, file_name, colfg='||'): rt = self. Query(sql) if rt: with open(file_name, 'a') as fd: for row in rt: ln_info = '' for col in row: ln_info += str(col) + colfg ln_info += '\n' fd .write(ln_info)
PG版本使用方法：
# 查询单个sql pg = DBPg(host, port, db, user, password) sql = "select c1,c2,c3 from table1" pg.Query(sql ) # 更新单个sql sql = "update table1 set c1=0" pg.Exec(sql ) # 更新多条sql sqllist = [] sqllist.append("update table1 set c1=0") sqllist.append("update table1 set c2=0") pg.Execlist(sqllist)
其他版本如Mysql，原理类似，欢迎热心的同学补充
浙公网安备 33010602011771号