python通过pymssql调用sql server存储过程

要通过pymssql库在Python中调用SQL Server存储过程，可以使用以下示例代码：

import pymssql

# 连接到SQL Server数据库
conn = pymssql.connect(server='SERVER_NAME', database='DB_NAME', user='USER_NAME', password='PASSWORD')

# 创建游标对象
cursor = conn.cursor()

# 调用存储过程
cursor.callproc('STORED_PROCEDURE_NAME', ('param1_value', 'param2_value'))

# 获取输出参数的值
output_param = cursor.fetchall()

# 提交事务
conn.commit()

# 关闭连接
conn.close()

请注意替换上面代码中的 SERVER_NAME、DB_NAME、USER_NAME、PASSWORD 和 STORED_PROCEDURE_NAME 为实际值。此外，如果存储过程有输出参数，可以使用 fetchall 方法获取输出参数的值。

相关问题

python调用存储过程

在Python中调用存储过程有多种方法。其中一种方法是使用adodbapi库。你可以使用以下代码来调用存储过程：

from adodbapi import connect

server = 'dbserver'
user = 'username'
password = 'password'
database = 'database'
sp = 'sp'
station = 'station'
sn = 'sn'

try:
    db = connect('Provider=SQLOLEDB.1;DataSource=%s;InitialCatalog=%s;UserID=%s;Password=%s;' % (server, database, user, password))
except Exception as e:
    print(e)
else:
    cur = db.cursor()
    msg = cur.callproc(sp, (station, sn))
    if len(msg) > 1:
        if msg\[-1\] is None:
            print('sn is ok. Can be tested at this station')
        else:
            print(msg\[-1\])
finally:
    try:
        db.close()
    except:
        pass

另一种方法是使用pymysql库。你可以使用以下代码来调用存储过程：

import pymysql

conn = pymysql.connect(host='127.0.0.1', user='root', password='root', database='fruitdb', charset='utf8')
cur = conn.cursor()
cur.callproc('searchAllFruit')
conn.commit()
result = cur.fetchall()
print('{0} {1} {2} {3}'.format('名称', '价格', '数量', '总价'))
for row in result:
    name = row\[0\]
    price = row\[1\]
    count = row\[2\]
    total = row\[3\]
    print('{0} {1} {2} {3}'.format(name, price, count, total))
conn.close()

还有一种方法是使用pymssql库。你可以使用以下代码来调用存储过程：

from pymssql import connect

server = 'dbserver'
user = 'user'
password = 'password'
database = 'database'
sp = 'sp'
station = 'station'
sn = 'sn'

sql = \[
    'set nocount on',
    'declare @Msg varchar(500)',
    'declare @return_value varchar',
    "exec @return_value = %s @Station='%s', @SN='%s', @Info='@Info', @Msg=@Msg output",
    'select @Msg, @return_value'
\]
sql = '/n'.join(sql) % (sp, station, sn)

def ffchk(server, user, password, database, sql):
    try:
        db = connect(host=server, database=database, user=user, password=password, login_timeout=10)
        cur = db.cursor()
        cur.execute(sql)
    except Exception as e:
        print(e)
    else:
        cur.nextset()
        print(cur.fetchone())
    finally:
        try:
            db.close()
        except:
            pass

以上是三种常用的Python调用存储过程的方法。你可以根据自己的需求选择其中一种方法来使用。
#### 引用[.reference_title]
- *1* *3* [Python如何执行存储过程，获取存储过程返回值](https://blog.csdn.net/andoring/article/details/6564654)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item]
- *2* [在python中调用存储过程](https://blog.csdn.net/weixin_38052444/article/details/81450604)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item]
[ .reference_list ]