# -*- coding:utf-8 -*-

import pymssql

import pandas as pd

class MSSQL(object):

    def __init__(self,host,user,pwd,db):

        self.host = host

        self.user = user

        self.pwd = pwd

        self.db = db

    def __GetConnect(self):

        if not self.db:

            raise(NameError,"没有设置数据库信息")

        self.conn = pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset="utf8")

        cursor = self.conn.cursor()

        if not cursor:

            raise(NameError,"连接数据库失败")

        else:

            return cursor

    def ExecQuery(self,sql):

        cursor = self.__GetConnect()

        cursor.execute(sql)

        # 调出数据

        resList = cursor.fetchall()

        #查询完毕后必须关闭连接

        self.conn.close()

        return resList

    def ExecQueryToDataFrame(self,sql):

        cursor = self.__GetConnect()

        cursor.execute(sql)

        # 调出数据

        resList = cursor.fetchall()

        # cols为字段信息 例如((''))

        cols = cursor.description

        #查询完毕后必须关闭连接

        self.conn.close()

        # 将数据转换为DataFrame

        col = []

        for i in cols:

            col.append(i[0])

        data = list(map(list, resList))

        data = pd.DataFrame(data,columns=col) 

        return data

    def ExecNonQuery(self,sql):

        cursor = self.__GetConnect()

        cursor.execute(sql)

        self.conn.commit()

        self.conn.close()