120 lines
4.5 KiB
Python
120 lines
4.5 KiB
Python
|
#coding: utf-8
|
|||
|
|
# +-------------------------------------------------------------------
|
|||
|
|
# | YakPanel Windows 面板
|
|||
|
|
# +-------------------------------------------------------------------
|
|||
|
|
# | Copyright (c) 2015-2099 YakPanel(www.yakpanel.com) All rights reserved.
|
|||
|
|
# +-------------------------------------------------------------------
|
|||
|
|
# | Author: 沐落 <cjx@yakpanel.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()
|