Python使用多个参数调用MS SQL 2019过程

Question

我在MS SQL 2019中插入python时遇到字符串大小问题。

此处列出的要插入的变量

_ID = 'XXXYY770-009999',
_APName = 'LOB XX SALES AND REPORTING TOOLS',
_BID = '000111222',
_SID = '000333444',
_TID = '000555666',
_AskC = '2012-02-09T00:00:00.000Z',
Alias = 'AppsNAme (XXX YY),HEART,AppsNAme,YYY,OXW,SAR,DISTOXS,relovations ES DX',
description = 'APPNAME software is a BI tool that uses data input from data sources within company to provide critical business reporting for Sales and  guidelines mandated Compliance Reporting;This SQL set of applications supports the Sales and Compliance reporting for Industry and Partners Reporting Division.  It supplies the data for the AppName BI reporting tool;The App Data warehouse contains call center data for agents and customers as well as product and production data for the M&R division.  Its used in compliance and sales reporting for the division'

MS SQL过程输入参数

Procedure dbo.AppProcess(
@_ID varchar(50),
@_APName varchar(255),
@_BID varchar(25),
@_SID varchar(25),
@_TID varchar(25),
@_AskC varchar(30),
@Alias varchar(255),
@Description varchar(1000)

Python插入脚本

def sqlInsert(_ID, _APName , _BID, _SID ,_TID ,_AskC ,Alias ,Description):
conn_str = (
r'DRIVER={SQL SERVER Native Client 11.0};'
r'SERVER=SomeServer;'
r'DATABASE=SomeDatabase;'
r'Trusted_Connection=yes;'
)
cnxn = pyodbc.connect(conn_str)
cursor = cnxn.cursor()
params = (_ID, _APName , _BID, _SID ,_TID ,_AskC ,Alias ,Description)
sqlcmd = "Exec [EnviromentData].dbo.AskIdAppProcess @_ID = ?, @_APName = ?, @_BID = ?, @_SID = ?, @_TID = ?,@_AskC = ?, @Alias = ?,@Description = ?"
cursor.setinputsizes([(pyodbc.SQL_WVARCHAR,50,0),(pyodbc.SQL_WVARCHAR,255,0),(pyodbc.SQL_WVARCHAR,25,0),(pyodbc.SQL_WVARCHAR,25,0),(pyodbc.SQL_WVARCHAR,25,0),(pyodbc.SQL_WVARCHAR,30,0),(pyodbc.SQL_WVARCHAR,255,0),(pyodbc.SQL_WVARCHAR,1000,0)])
cursor.fast_executemany = True
cursor.execute( sqlcmd, params )
cnxn.commit()

cursor.close()
cnxn.close()
return

这是正在制作的

sqlInsert(_ID,_APName,_BID,_SID,_TID,Alias,description,_AskC)

脚本在尝试插入别名和描述时失败。错误

DataError: ('22001', '[22001] [Microsoft][SQL Server Native Client 11.0]String data, right truncation (0) (SQLExecDirectW)')

然而当我做len(别名(=66len(描述(=558

数字可能会有一些变化，因为出于隐私原因，我编辑了字符串，但分配给每个字符串的大小应该足够合适。插入语句是否有问题导致失败？

Answer 1

我需要将sql命令封装在3个双引号中。

sqlcmd = """Exec [EnviromentData].dbo.AskIdAppProcess @_ID = ?, @_APName = ?, @_BID = ?, @_SID = ?, @_TID = ?,@_AskC = ?, @Alias = ?,@Description = ?"""