参考: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()