aaPanel/class/panelMssql.py

#coding: utf-8
# +-------------------------------------------------------------------
# | 宝塔Windows面板
# +-------------------------------------------------------------------
# | Copyright (c) 2015-2099 aaPanel(www.aapanel.com) All rights reserved.
# +-------------------------------------------------------------------
# | Author: 沐落 <cjx@aapanel.com>
# +-------------------------------------------------------------------

import re,os,sys,public

class panelMssql:
    __DB_PASS = None
    __DB_USER = 'sa'
    __DB_PORT = 1433
    __DB_HOST = '127.0.0.1'
    __DB_CONN = None
    __DB_CUR  = None
    __DB_ERR  = None
    __DB_SERVER = 'MSSQLSERVER'

    __DB_CLOUD = 0 #远程数据库
    def __init__(self):
        self.__DB_CLOUD = 0


    def set_host(self,host,port,name,username,password,prefix = ''):
        self.__DB_HOST = host
        self.__DB_PORT = int(port)
        self.__DB_NAME = name
        if self.__DB_NAME: self.__DB_NAME = str(self.__DB_NAME)
        self.__DB_USER = str(username)
        self._USER = str(username)
        self.__DB_PASS = str(password)
        self.__DB_PREFIX = prefix
        self.__DB_CLOUD = 1
        return self

    def __Conn(self):
        """
        连接MSSQL数据库
        """
        try:
            import pymssql
        except :
            os.system("btpip install pymssql==2.3.0")
            # os.system("btpip install pymssql==2.1.4")
            import pymssql        
               
        
        if not self.__DB_CLOUD:            
            sa_path = 'data/sa.pl'
            if os.path.exists(sa_path): self.__DB_PASS = public.readFile(sa_path)       
            self.__DB_PORT = self.get_port()

        try:

            if self.__DB_CLOUD:
                try:
                    self.__DB_CONN = pymssql.connect(server=self.__DB_HOST, port=str(self.__DB_PORT),
                                                     user=self.__DB_USER,
                                                     password=self.__DB_PASS, database=None, login_timeout=30,
                                                     timeout=0, autocommit=True, charset="CP936", tds_version='7.0')
                except:
                    self.__DB_ERR = 'Failed to connect to database! Check that the remote database information is correct'
                    return False
            #     self.__DB_CONN = pymssql.connect(server=self.__DB_HOST, port=str(self.__DB_PORT),
            #                                      user=self.__DB_USER,
            #                                      password=self.__DB_PASS, database=None, login_timeout=30,
            #                                      timeout=0, autocommit=True, charset="CP936", tds_version='7.0')
            else:
                self.__DB_CONN = pymssql.connect(server=self.__DB_HOST, port=str(self.__DB_PORT), login_timeout=30,
                                                 timeout=0, autocommit=True, charset="CP936", tds_version='7.0')
            self.__DB_CUR = self.__DB_CONN.cursor()  # 将数据库连接信息，赋值给cur。
            self.__DB_CUR = self.__DB_CONN.cursor()  # 将数据库连接信息，赋值给cur。
            if self.__DB_CUR:
                return True
            else:
                self.__DB_ERR = 'Failed to connect to the database, please check whether SQL Server is installed'
                return False
        except Exception as ex:
            self.__DB_ERR = public.get_error_info()
       
        return False

    def execute(self,sql):

        #执行SQL语句返回受影响行
        if not self.__Conn(): return self.__DB_ERR
        try:
            result = self.__DB_CUR.execute(sql)
          
            self.__Close()
            return result;    
        except Exception as ex:
            self.__DB_ERR = public.get_error_info()
            return self.__DB_ERR
    
    def query(self,sql):
        #执行SQL语句返回数据集
        if not self.__Conn(): return self.__DB_ERR
        try:
            self.__DB_CUR.execute(sql)
            result = self.__DB_CUR.fetchall()
            
            #print(result)
            #将元组转换成列表
            data = list(map(list,result))
            self.__Close()
            return data
        except Exception as ex:          
            self.__DB_ERR = public.get_error_info()
            #public.WriteLog('SQL Server查询异常', self.__DB_ERR);
            return str(ex)
        
     
    #关闭连接        
    def __Close(self):
        self.__DB_CUR.close()
        self.__DB_CONN.close()