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() |