“无效的精度或比例值”错误来自SQL Server，其中pypyodbc为十进制（'0.00'），十进制（'0.12'）

I am developing an application in python. I am inserting some financial data into database. I have approx. 1300 tables all with same schema, one table for each company.

我正在使用python开发一个应用程序。我正在将一些财务数据插入数据库。我有约。 1300个表都具有相同的模式,每个公司有一个表。

I am using pypyodbc 1.3.3(latest).

我正在使用pypyodbc 1.3.3(最新版)。

I have Decimal as datatype. I am using precision of as Decimal(8,2). In my table, datatypes are DATE, DECIMAL, TINYINT and INT. I am inserting the following row in one of the table from my python program.

我有十进制数据类型。我使用精度为十进制(8,2)。在我的表中,数据类型是DATE,DECIMAL,TINYINT和INT。我在我的python程序的一个表中插入以下行。

['05-JAN-2015', Decimal('30.90'), Decimal('31.40'), Decimal('30.40'), Decimal('30.85'), None, Decimal('0.00'), Decimal('0.00'), None, Decimal('0.00'), Decimal('0.00'), None, Decimal('0.00'), Decimal('0.00'), None, None, Decimal('30.88'), Decimal('0.00'), Decimal('0.00'), Decimal('0.00'), Decimal('0.00'), 32024, 321, Decimal('0.00'), Decimal('0.00'), None, Decimal('0.00'), None, Decimal('0.00')]

Datatype of 05-JAN-2015 is DATE, All None have datatype as TINYINT, and value 32024, 321 have datatype as INT. But still I am getting the SQL exception as

05-JAN-2015的数据类型为DATE,All None的数据类型为TINYINT,值32024,321的数据类型为INT。但我仍然得到SQL异常

  File "C:\Python27\lib\site-packages\pypyodbc.py", line 1470, in execute
    self._BindParams(param_types)
  File "C:\Python27\lib\site-packages\pypyodbc.py", line 1433, in _BindParams
    check_success(self, ret)
  File "C:\Python27\lib\site-packages\pypyodbc.py", line 986, in check_success
    ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
  File "C:\Python27\lib\site-packages\pypyodbc.py", line 964, in ctrl_err
    raise Error(state,err_text)
Error: (u'HY104', u'[HY104] [Microsoft][SQL Server Native Client 10.0]Invalid precision or scale value')

When I try to insert the same data directly into SQL server from management studio, then It get inserted successfully. I have cross checked the datatype of the variables and all are perfect.

当我尝试从管理工作室直接将相同的数据插入SQL服务器时,它会成功插入。我已经交叉检查了变量的数据类型,并且一切都很完美。

As I am inserting data into ~1300 tables in a loop somewhat like

因为我在一个循环中将数据插入到~1300个表中

for i in range(tables_number):
    insertion_data = calculate_values()
    insert_into_db(insertion_data)

,does that causes the problem? I have also tried by using the delay of 0.1 second in a loop.

,这会导致问题吗?我也试过在循环中使用0.1秒的延迟。

Does Anybody have any idea? Is this bug in driver? Or Is there any other cause that I am missing?

有人有什么想法吗?驱动程序中有这个错误吗?或者我还有其他原因吗?

UPDATE:

After debugging the code, I found in the script pypyodbc.py, function SQLBindParameter(<many parameters>) returns -1 which is SQL_ERROR for parameter Decimal('0.00') or Decimal(0.12) or Decimal(-0.12) means decimal of the form 0.*

在调试代码之后,我在脚本pypyodbc.py中找到函数SQLBindParameter( )返回-1,这是参数Decimal('0.00')或Decimal(0.12)或Decimal(-0.12)的SQL_ERROR形式0. *

1 个解决方案

#1

It appears to be a bug in pypyodbc. This code, using pypyodbc 1.3.3, fails just as you describe ...

它似乎是pypyodbc中的一个错误。这个代码,使用pypyodbc 1.3.3,就像你描述的那样失败了......

from decimal import *
import pypyodbc
connStr = "DSN=myDb_SQLEXPRESS;"
cnxn = pypyodbc.connect(connStr, autocommit=True)
crsr = cnxn.cursor()
sql = "INSERT INTO Invoices (InvoiceAmount) VALUES (?)"
invamt = Decimal('0.01')
crsr.execute(sql, [invamt])
crsr.close()
cnxn.close()

... but doing the exact same thing using pyodbc works okay:

...但使用pyodbc做同样的事情可以正常工作:

from decimal import *
import pyodbc
connStr = "DSN=myDb_SQLEXPRESS;"
cnxn = pyodbc.connect(connStr, autocommit=True)
crsr = cnxn.cursor()
sql = "INSERT INTO Invoices (InvoiceAmount) VALUES (?)"
invamt = Decimal('0.01')
crsr.execute(sql, [invamt])
crsr.close()
cnxn.close()

Furthermore, if I run the pypyodbc version against an identical table in a MySQL database ...

此外,如果我对MySQL数据库中的相同表运行pypyodbc版本...

from decimal import *
import pypyodbc
connStr = "DSN=usbMySQL;"
cnxn = pypyodbc.connect(connStr, autocommit=True)
crsr = cnxn.cursor()
sql = "INSERT INTO Invoices (InvoiceAmount) VALUES (?)"
invamt = Decimal('0.01')
crsr.execute(sql, [invamt])
crsr.close()
cnxn.close()

... the value that gets inserted into the database is 0.10, not 0.01.

...插入数据库的值是0.10,而不是0.01。

#1