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

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