1.安装pymssql第三方库
pip install pymssql
出现如下图，表示安装成功：
2.编写工具类，我这里取名为sql_server_util.py
import pymssqlclass SqlServerUtil:def __init__(self, ip, username, password, database):self.ip = ipself.username = usernameself.password = passwordself.database = databaseself.conn = Noneself.cursor = Nonedef connect(self):'''建立数据库连接'''try:self.conn = pymssql.connect(server=self.ip, user=self.username, password=self.password, database=self.database)self.cursor = self.conn.cursor(as_dict=True) # 将结果作为字典返回except pymssql.DatabaseError as e:print(e)def disconnect(self):'''关闭数据库连接'''if self.cursor:self.cursor.close()if self.conn:self.conn.close()def fetch_data(self, sql):'''执行查询语句，并返回结果集，该方法适用于DQL语句'''try:self.cursor.execute(sql)resultMapList = self.cursor.fetchall()return resultMapListexcept pymssql.DatabaseError as e:print(e)def execute_sql(self, sql):'''执行sql语句，该方法适用于DDL、DML和DCL语句'''try:self.cursor.execute(sql)# 如果是INSERT、UPDATE、DELETE、DROP、CREATE开头的语句，则需要提交事务if sql.strip().upper().startswith(('INSERT', 'UPDATE', 'DELETE', 'DROP', 'CREATE')):self.commit()except pymssql.DatabaseError as e:self.rollback() # 发生错误时，则需要回滚事务print(e)def commit(self):'''提交事务'''if self.conn:self.conn.commit()def rollback(self):'''回滚事务'''if self.conn:self.conn.rollback()if __name__ == '__main__':# 数据库信息ip = '192.168.215.1'username = 'admin'password = '123456'database = 'myDatabase'# 执行sql语句sqlServer = SqlServerUtil(ip, username, password, database)sqlServer.connect()sql = 'SELECT name FROM sys.tables ORDER BY name ASC'resultMapList = sqlServer.fetch_data(sql)print(resultMapList)sqlServer.disconnect()
