【Python】‌数据库工具类，使用python连接sql server数据库

1.安装pymssql第三方库

pip install pymssql

出现如下图，表示安装成功：

2.编写工具类，我这里取名为sql_server_util.py

import pymssql


class SqlServerUtil:
    def __init__(self, ip, username, password, database):
        self.ip = ip
        self.username = username
        self.password = password
        self.database = database
        self.conn = None
        self.cursor = None

    def 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 resultMapList
        except 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()