Python数据库访问类，Oracle、Sqlserver、PostgreSQL

Python下访问数据库的工具类，工作中常用到Oracle、Sqlserver、PostgreSQL，有兴趣的同学帮补充上Mysql吧。直接上代码：

import pymssql
from pymssql import _mssql
from pymssql import _pymssql
import psycopg2
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)


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)


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)