[ 功能建议 ]sqlserver 查询存储过程

[AceAttorney]

目前遇到的问题/使用障碍

未来有计划开发这个功能么？我们dba提的需求。

希望如何解决/实现它

倒是想自己试着加加，但是django这块不是很熟。

[LeoQuote]

你有相关方案吗？我没有在pyodbc中找到很好的调用存储过程的方法，类似 sp 之类的都没法做。

[LeoQuote]

https://github.com/mkleehammer/pyodbc/wiki/Calling-Stored-Procedures 这是pyodbc关于存储过程的文档，但我个人看不太明白，如果你能弄明白怎么调用，我可以再帮你包装到系统中。

[AceAttorney]

我以前倒是调过，我测试一下

[AceAttorney]

import pyodbc


# 建立连接时候需要带上库名
sqlconstr = '''DRIVER=ODBC Driver 17 for SQL Server;SERVER={0},{1};DATABASE={2};UID={3};PWD={4};
client charset = UTF-8;connect timeout=10;CHARSET=UTF8;'''.format(Server,Port,DBName,User,Password)

fullsql = '''
sp_helptext  '[Ana].[prCenterDataCntCheck]'
'''

conn = pyodbc.connect(sqlconstr)
cursor = conn.cursor()
cursor.execute(fullsql)
rows = cursor.fetchall()
print(rows)

[('-- =============================================\r\n', ), ('-- Author:\t\tzy\r\n', ), ('-- Create date: 2019年1月11日 11:48:19\r\n', ), ('-- Description:\t描述信息\r\n', ), ('-- =============================================\r\n', ), ('CREATE PROCEDURE Ana.prCenterDataCntCheck\r\n', ), ('\t@dbname NVARCHAR(32)\r\n', ), ('AS\r\n', ), ('BEGIN\r\n', ), ('\t\r\n', ), ('\tDECLARE @execsql NVARCHAR(2000)\r\n', ), ('\t\r\n', ), ('\tPRINT @dbname\r\n', ), ('\t\r\n', ), ("\tSET @execsql = 'USE '+@dbname+CHAR(13)+\r\n", ), ("\t\t\t\t\t'DECLARE cur_Track_max CURSOR FAST_FORWARD for\r\n", ), ("\t\t\t\t\tSELECT vehicleid FROM center.AiTrackM with(nolock)WHERE GPSTime >''2018-01-01'' ORDER BY vehicleid\r\n", ), ('\r\n', ), ('\t\t\t\t\tDECLARE @vehicleid INT \r\n', ), ('\r\n', ), ('\t\t\t\t\tOPEN cur_Track_max\r\n', ), ('\r\n', ), ('\t\t\t\t\tFETCH NEXT FROM cur_Track_max INTO @vehicleid\r\n', ), ('\r\n', ), ('\t\t\t\t\tWHILE(@@FETCH_STATUS=0)\r\n', ), ('\t\t\t\t\tBEGIN\r\n', ), ('\t\r\n', ), ('\t\t\t\t\t\tINSERT INTO e6managerdb.ana.centerdata_analize_LG\r\n', ), ('\t\t\t\t\t\tSELECT @dbname,@vehicleid,COUNT(*)  as cnt,GETDATE()\r\n', ), ('\t\t\t\t\t\tFROM center.AiTrackp with(nolock)\r\n', ), ('\t\t\t\t\t\tWHERE vehicleid =@vehicleid\r\n', ), ('\t\t\t\t\t\tAND gpstime >dateadd(HH,-6,GETDATE()) AND gpstime <dateadd(HH,-5,GETDATE()) \r\n', ), ("\t\t\t\t\t\t--AND gpstime >''2019-01-10 16:00'' AND gpstime <''2019-01-10 18:00''\r\n", ), ('\t\r\n', ), ('\t\r\n', ), ('\t\t\t\t\t\tFETCH NEXT FROM cur_Track_max INTO @vehicleid;\r\n', ), ('\t\t\t\t\tEND\r\n', ), ('\r\n', ), ('\t\t\t\t\tCLOSE cur_Track_max\r\n', ), ("\t\t\t\t\tDEALLOCATE cur_Track_max'\r\n", ), ('\t\t\t\t\t\r\n', ), ("\t EXECUTE SP_EXECUTESQL @execsql,N'@dbname NVARCHAR(32)',@dbname = @dbname\r\n", ), ('\r\n', ), ('\r\n', ), ('\r\n', ), ('END\r\n', )]

@LeoQuote 这个测试可以获取的结果是一个list，每一行都是一个 tuple，应该需要for一下拼成一个str。

[LeoQuote]

其实你不用懂django，我们对于mssql的调用全部都在这个文件中 https://github.com/hhyo/Archery/blob/master/sql/engines/mssql.py

我大概看了下，如果要支持存储过程、有两个点需要解决

1. 目前我们的查询逻辑里是会解析语法，然后把select 语句摘出来，并且只取第一句。这样的目的是保证安全，为了支持存储过程，我们需要把常用的存储过程列进白名单，以保证安全。
2. 据我观察这个结果有点不像是 “字段名，字段名\n 字段值，字段值” 而似乎是有一个分割符，然后在值中间做格式化，如果用目前的前端会不会有些不太好看？

我们的代码应该是比较清晰的，欢迎你为这个功能点做一些修改，只要单元测试全过，逻辑没有大问题就会接受

[AceAttorney]

看了下代码

1. 我想可不可以和查看表结构一样做一个类似的功能，只允许传存储过程的名，应该可以不用在限定select语句
2. 展示这块，必须用表格展示出来么，我拼成一个字符串直接返回可以么。前端看起来就是一个文本框。

[LeoQuote]

我觉得类似表结构这样做不太好，因为没法复用查询的流程和前端，我建议穷举一下sp_ 这样的命令，在检查的时候放过这些语句就可以。

[AceAttorney]

@LeoQuote

测试了一下 在query_check这里直接放行了sp_helptext语句后可以正常使用。

    def query_check(self, db_name=None, sql=''):
        # 其他代码省略
        if re.match(r"^select|^sp_helptext", sql_lower) is None:
            result['bad_query'] = True
            result['msg'] = '仅支持^select和^sp_helptext语法!'
            return result

实际效果

查询语句

返回结果

合并的话有啥问题么？

[LeoQuote]

就是不太好看，你觉得这种表格方式展示可以接受吗？

[LeoQuote]

放行不要这么写，要写成一堆关键词列表，然后根据列表放行，这样比较方便修改。

[AceAttorney]

放行不要这么写，要写成一堆关键词列表，然后根据列表放行，这样比较方便修改。

是打算这么弄，不过暂时和我们dba沟通没有需要更多的关键词，就先这样测试一下。

就是不太好看，你觉得这种表格方式展示可以接受吗？

前端我也不太懂，暂时没啥想法，不过这个可以当作整段文本复制出来，我还能接受。。。

[LeoQuote]

如果觉得前端可以接受可以就这么做, 那就只需要在检查里把语句放行就可以.