pyodbc 不会引发 SQL Server 错误 [英] pyodbc does not throw on SQL Server error
问题描述
我正在尝试使用 pyodbc
(使用 Python 2.7)调用存储过程将记录插入到 SQL Server 2012 表中.我正在传递一个临时表.
I am trying to use pyodbc
(with Python 2.7) to call a stored procedure to insert records into a SQL Server 2012 table. I am passing a temporary table.
我转储了我的 sql,当通过 SQL Server 管理控制台执行时,它生成了以下外键错误:
I dumped out my sql and when executed through the SQL Server Management console, it generated the following Foreign Key error:
Msg 547, Level 16, State 0, Procedure spInsertBondTickerValues, Line 26
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__BondTickerValue__756D6ECB".
The conflict occurred in database "QuantDev", table "dbo.Tickers".
The statement has been terminated.
然而,pyodbc
没有引发异常.我将如何测试生成的游标或连接以了解发生了问题,以及如何获取错误消息?
However, pyodbc
did not raise an exception. How would I test the resulting cursor or connection to know that a problem occurred, and how do I get the error message?
非常感谢.
编辑这里是完整的sql文本:
EDIT Here is the full sql text:
DECLARE @rawTbl [dbo].TickerValueTableType
INSERT INTO @rawTbl (Ticker, BBName, LastValue, ValueTime, SourceDescr) VALUES
('IBM', 'Equity', 179.230000, '2013-11-01 00:00:00.000000', 'Bloomberg'),
('SPX', 'Index', 1803.710000, '2013-12-10 00:00:00.000000', 'Bloomberg')
EXEC [dbo].spInsertBondTickerValues @rawTbl
EDIT 2 这是相关的 Python 代码:
EDIT 2 Here is the relevant Python code:
def execSQLwithCommit(self, sql):
cursor = self.conn.cursor()
cursor.execute(sql)
self.conn.commit()
先前通过以下方式建立的连接
where the connection has been previously made via
self.conn = pyodbc.connect(app = appName,
driver = '{SQL Server Native client 11.0}',
server = server,
database = db,
Trusted_Connection = 'yes')
推荐答案
我能够使用以下代码重新创建您的问题,但无提示地失败了:
I was able to recreate your issue using the following code, which fails silently:
import pyodbc
cnxn = pyodbc.connect('DSN=myDb;')
cursor = cnxn.cursor()
sql = """
DECLARE @rawTbl dbo.ClientAddressInputType;
INSERT INTO @rawTbl (ClientID, Addr1) VALUES
(2, 'higgy'),
(3, 'jiggy');
EXEC dbo.AddClientAddress @rawTbl
"""
cursor.execute(sql)
cursor.commit()
cnxn.close()
但是,我可以通过在 sql
的开头简单地添加 SET NOCOUNT ON;
来让代码抛出适当的 IntegrityError
异常字符串:
However, I can get the code to throw the appropriate IntegrityError
exception by simply adding SET NOCOUNT ON;
at the beginning of the sql
string:
import pyodbc
cnxn = pyodbc.connect('DSN=myDb;')
cursor = cnxn.cursor()
sql = """
SET NOCOUNT ON;
DECLARE @rawTbl dbo.ClientAddressInputType;
INSERT INTO @rawTbl (ClientID, Addr1) VALUES
(2, 'higgy'),
(3, 'jiggy');
EXEC dbo.AddClientAddress @rawTbl
"""
cursor.execute(sql)
cursor.commit()
cnxn.close()
导致
Traceback (most recent call last):
File "C:\Users\Gord\Desktop\pyOdbc.py", line 12, in <module>
cursor.execute(sql)
IntegrityError: ('23000', '[23000] [Microsoft][SQL Server Native Client 10.0][SQL Server]The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ClientAddresses_Clients". The conflict occurred in database "myDb", table "dbo.Clients", column \'ClientID\'. (547) (SQLExecDirectW); [01000] [Microsoft][SQL Server Native Client 10.0][SQL Server]The statement has been terminated. (3621)')
这篇关于pyodbc 不会引发 SQL Server 错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!