UiAuto|SQLServer插件

从数字员工的角度来看，可以连接各类数据库应该是最基本的功能。有些失望的是拿到的UiAuto标准产品没有封装SQLServer的功能，这里自己简单写一个，先满足简单的查询、更新。后续会根据使用情况及时更新。插件源码如下：

package.json

{
  "id": "MSSQL",
  "name": "MSSQL",
  "description": "sqlserver数据库插件，可通过该插件执行一系列的数据库相关操作",
  "version": "1.0.2",
  "author": "chendi",
  "language": "python",
  "license": "Official",
  "main": "index.py",
  "uiauto_config": {
    "attribution_id": "database",
    "attribution_name": "数据库",
    "operations": [
      {
        "category_id": "mssql_database",
        "category_name": "SqlServer",
        "operation_id": "connectMSsql",
        "operation_name": "连接数据库",
        "method": "connectMSsql",
        "type": "Convention",
        "input": [
          {
            "name": "必填属性",
            "id": "required_params",
            "properties": [
              {
                "id": "database_name",
                "name": "数据库名",
                "type": "text",
                "required": true,
                "value": ""
              },
              {
                "id": "username",
                "name": "用户名",
                "type": "text",
                "required": true,
                "value": ""
              },
              {
                "id": "password",
                "name": "密码",
                "type": "password",
                "required": true,
                "value": ""
              },
              {
                "id": "host",
                "name": "服务器",
                "type": "text",
                "required": true,
                "value": ""
              },
              {
                "id": "port",
                "name": "端口",
                "type": "text",
                "required": true,
                "value": "1433"
              },
              {
                "id": "encoding",
                "name": "字符编码",
                "type": "text",
                "required": true,
                "value": "cp936"
              }
            ]
          }
        ],
        "output": {
          "is_allow_global_use": true,
          "description": "数据库连接对象",
          "value": "MSSQL"
        }
      },
      {
        "category_id": "mssql_database",
        "category_name": "MSSQL",
        "operation_id": "executeSQL",
        "operation_name": "执行SQL",
        "method": "executeSQL",
        "type": "Convention",
        "input": [
          {
            "name": "必填属性",
            "id": "required_params",
            "properties": [
              {
                "id": "connect_message",
                "name": "数据库对象",
                "type": "text",
                "required": true,
                "value": "${mssql}"
              },
              {
                "id": "sql",
                "name": "SQL语句",
                "type": "code",
                "required": true,
                "value": "SELECT * FROM "
              }
            ]
          }
        ],
        "output": {
          "is_allow_global_use": true,
          "description": "SQL执行返回值",
          "value": "execute_result"
        }
      },
      {
        "category_id": "mssql_database",
        "category_name": "MSSQL",
        "operation_id": "closeConnect",
        "operation_name": "关闭数据库",
        "method": "closeConnect",
        "type": "Convention",
        "input": [
          {
            "name": "必填属性",
            "id": "required_params",
            "properties": [
              {
                "id": "connect_message",
                "name": "数据库对象",
                "type": "text",
                "required": true,
                "value": "${mssql}"
              }
            ]
          }
        ],
        "output": {
          "is_allow_global_use": true,
          "description": "关闭结果，True or False",
          "value": ""
        }
      }
    ]
  }
}

index.py

# -*- encoding:utf-8 -*-
'''
@description: 基于pymssql进行SQLServer数据库的对接
@Time    :    2023-05-29
@Author  :    CHENDI
@Version :    1.0.0
@Contact ：   henanchendi@163.com
'''
import pymssql

def connectMSsql(params):
    """
    连接数据库
    """
    try:
        if 'host' not in params or 'port' not in params or 'username' not in params or 'password' not in params or 'database_name' not in params:
            raise Exception('缺少参数')
        host = params.get('host')
        port = params.get('port', 1433)  
        try:
            port = int(port)
        except Exception:
            return "端口信息错误"
        username = params.get('username')
        password = params.get('password')
        database_name = params.get('database_name')
        encoding = params.get('encoding')
        conn = pymssql.connect(host=host,port=port, user=username, password=password, database=database_name,charset=encoding)
        return conn
    except pymssql._pymssql.OperationalError:
        return "数据库连接超时"
    except Exception as e:
        raise e

def executeSQL(params):
    """
    执行SQL语句,先实现select ,update,insert
    """
    conn = params.get('connect_message')
    sql = params.get('sql')
    try:
        cursor = conn.cursor()
        if sql.startswith("select") or sql.startswith("SELECT"):
            cursor.execute(sql)
            result = cursor.fetchall()
            return result
        elif sql.startswith("update") or sql.startswith("UPDATE"):
            cursor.execute(sql)
            conn.commit()
            return {}
        elif sql.startswith("insert") or sql.startswith("INSERT"):
            cursor.execute(sql)
            conn.commit()
            return {}
        else:
            return {}
    except Exception as e:
        raise e


def closeConnect(params):
    """
    关闭数据库
    """
    try:
        conn = params.get('connect_message')
        conn.close()
        return True
    except IndexError as e:
        return f"请检查：{e.__str__()}"
    except Exception:
        return False

if __name__ == '__main__':
    pass