Python调用MSSQL存储过程(pyodbc库)

Love The Way You Lie 2023-10-12 18:49 25阅读 0赞

**`DECLARE @SQL NVARCHAR(MAX);`语句声明了一个变量`@SQL`**

**通过`SET @SQL = '...'`语句，将动态生成的SQL查询语句赋值给`@SQL`变量，**后续通过`sp_executesql`函数执行。

**PYTHON pyodbc库调用mssql存储过程SELECT查询表格字段数据**  
存储过程接受两个输入参数 `@Column1Name` 和 `@Column2Name`，用于指定要选择的两个字段名。存储过程将构建一个动态 SQL 查询，并使用 `sp_executesql` 执行该查询。

在动态 SQL 查询中，使用**`QUOTENAME` 函数**来确保字段名在查询中被正确引用。这有助于防止 SQL 注入攻击，并处理字段名中可能包含的特殊字符。

import pyodbc
    conn = pyodbc.connect('DSN=sql2016')
    # 创建表
    create_table_query = '''
        CREATE TABLE Customers (
            CustomerName NVARCHAR(100),
            ContactName NVARCHAR(100),
            Address NVARCHAR(200),
            City NVARCHAR(100),
            PostalCode NVARCHAR(20),
            Country NVARCHAR(100)
        )
    '''
    cursor = conn.cursor()
    cursor.execute(create_table_query)
    conn.commit()
    print("Table created successfully.")
    
    # 定义存储过程
    # quotename()的主要作用就是在存储过程中，给列名、表名等加个[]、’’等以保证sql语句能正常执行。
    stored_procedure = '''
    CREATE PROCEDURE CustomersProc
        @Column1Name VARCHAR(50),
        @Column2Name VARCHAR(50)
    AS
    BEGIN
        DECLARE @SqlQuery NVARCHAR(MAX)
        SET @SqlQuery = 'SELECT ' + QUOTENAME(@Column1Name) + ', ' + QUOTENAME(@Column2Name) + ' FROM Customers'
    
        EXEC sp_executesql @SqlQuery
    END
    '''
    
    #创建存储过程
    cursor.execute(stored_procedure)
    conn.commit()
    print("Stored procedure created successfully.")
    
    # 插入数据
    # insert_query = "INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES (?, ?, ?, ?, ?, ?)"
    # values = ('John Doe', 'John', '123 Main St', 'New York', '10001', 'USA')
    # cursor.execute(insert_query, values)
    # conn.commit()
    # print("Data inserted successfully.")
    
    # # 查询数据
    # select_query = "SELECT * FROM Customers"
    # cursor.execute(select_query)
    # print("查询数据")
    # rows = cursor.fetchall()
    # for row in rows:
    #     print(row)
    
    # # 更新数据
    # update_query = "UPDATE Customers SET City = ? WHERE CustomerName = ?"
    # values = ('Los Angeles', 'John Doe')
    # cursor.execute(update_query, values)
    # conn.commit()
    # print("Data updated successfully:Los Angeles<----John Doe")
    
    # # 删除数据
    # delete_query = "DELETE FROM Customers WHERE CustomerName = ?"
    # value = 'John Doe'
    # cursor.execute(delete_query, value)
    # conn.commit()
    # print("Data deleted successfully.")
    
    
    
    # # 调用存储过程
    stored_procedure = "{CALL CustomersProc(?, ?)}"
    param1 = 'CustomerName'
    param2 = 'Country'
    cursor.execute(stored_procedure, (param1, param2))
    print("调用存储过程SELECT查询表格字段数据")
    rows = cursor.fetchall()
    for row in rows:
        print(row[0]," ， ",row[1])
         
    
    # 关闭游标和连接
    cursor.close()
    conn.close()

使用动态SQL查询语句的一个常见场景是，当查询语句的一部分是根据用户输入或其他动态条件生成的，而不是固定不变的。通过使用变量和动态SQL语句，我们可以在运行时根据不同的条件构建不同的查询语句，**从而实现更灵活和动态的查询操作。**

\---------------------------------------------------------------------

在这个存储过程中，我们首先构建了一个动态SQL查询语句，根据**传入的表名和字段名**生成查询条件。然后，使用`sp_executesql`函数执行这个动态SQL语句，返回包含指定字段的表格的字段信息。

EXEC sp_executesql N'
    CREATE PROCEDURE GetTableColumns
        @TableName NVARCHAR(255),
        @ColumnName1 NVARCHAR(255),
        @ColumnName2 NVARCHAR(255)
    AS
    BEGIN
        SET NOCOUNT ON;
    
        DECLARE @SQL NVARCHAR(MAX);
        SET @SQL = ''
            SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = '''' + @TableName + ''''
            AND COLUMN_NAME IN ('''' + @ColumnName1 + '''', ''' + @ColumnName2 + ''''')'';
    
        EXEC sp_executesql @SQL;
    END';

EXEC GetTableColumns 'TableName', 'ColumnName1', 'ColumnName2';

通过使用`SET NOCOUNT ON;`语句，我们可以禁止返回受影响的行数信息，从而减少网络传输的数据量，提高存储过程的执行性能。尤其是对于大型查询或结果集较大的情况下，禁用受影响的行数信息可以显著减少不必要的数据传输，提升性能。