python pyodbc mssql

　参考：https://cloud.tencent.com/developer/article/1849526

　　

import pyodbc
import datetime

class sqlDb:


    def __init__(self, server='111.21.4.21', database='dbname', userid='sa', password='123456'):
        ''''' initialization
        '''
        self.server = server
        self.database = database
        self.userid = userid
        self.password = password

    def __GetConnect(self):
        ''''' Connect to the DB '''
        if not self.database:
            raise (NameError, "no setting db info")
        # engine = create_engine("mssql+pyodbc://{}:{}@{}:{}/{}?driver= ".format(self.userid, self.password, self.server, '1433',
        #                                                     self.database, 'ODBC Driver 18 for SQL Server'))
        #engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')
        # engine = create_engine("mssql+pyodbc://{}:{}@{}:{}/{}?driver= ".format(self.userid, self.password, self.server, '1433',
        #                                                     self.database, 'ODBC Driver 18 for SQL Server'))
        # self.conn = engine.connect()
        self.conn = pyodbc.connect(DRIVER='{ODBC Driver 17 for SQL Server}', SERVER=self.server, DATABASE=self.database,UID=self.userid,PWD=self.password, charset="UTF-8")
        # self.conn = pyodbc.connect(DRIVER='{SQL Server Native Client 10.0}', SERVER=self.server, DATABASE=self.database,UID=self.userid,PWD=self.password, charset="UTF-8")
        # self.conn = "DRIVER={SQL Server Native Client 10.0};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s" % (self.server,self.database,self.userid,self.password)
        cur = self.conn.cursor()
        if not cur:
            raise (NameError, "connected failed!")
        else:
            return cur

    def ExecQuery(self, sql):
        ''''' Perform one Sql statement '''
        try:
            cur = self.__GetConnect()
            cur.execute(sql)
            ret = cur.fetchall()
            cur.close()
            self.conn.close()
            return ret
        except Exception as ex:
            self.conn.close
            return None


    def ExecNoQuery(self, sql):
        ''''' Person one Sql statement like write data, or create table, database and so on'''
        try:
            cur = self.__GetConnect()
            cur.execute(sql)
            self.conn.commit()
            cur.close()
        except Exception as ex:
            self.conn.close()


    def GetSingleData(self,sql):
        try:
            cur = self.__GetConnect()
            cur.execute(sql)
            ret = cur.fetchall()
            cur.close()
            self.conn.close()
            return ret[0][0]
        except Exception as ex:
            self.conn.close()
            return None

    def CloseConn(self):
        self.conn.close()