使用executemany()插入行时出现“无效的参数类型”(numpy.int64)。

Question

我尝试向数据库插入大量数据

insert_list = [(1,1,1,1,1,1),(2,2,2,2,2,2),(3,3,3,3,3,3),....] #up to 10000 tuples in this list

conn = pyodbc.connect('DRIVER={FreeTDS};SERVER=xxxxx;DATABASE=xxxx;UID=xx;PWD=xx;TDS_Version=7.0')
cursor = conn.cursor()

sql = "insert into ScanEMAxEMAHistoryDay(SecurityNumber, EMA1, EMA2, CrossType, DayCross, IsLocalMinMax) values (?, ?, ?, ?, ?, ?)"

cursor.executemany(sql, insert_list)

cursor.executemany(sql, insert_list)

pyodbc.ProgrammingError: ('无效的参数类型。param-index=4 param-type=numpy.int64', 'HY105')

将元组数量减少到100：

cursor.executemany(sql, insert_list[:100])

cursor.executemany(sql, insert_list[:100])

pyodbc.ProgrammingError: ('Invalid parameter type. param-index=4 param-type=numpy.int64', 'HY105') cursor.executemany(sql, insert_list[:100])

减少为5个元组:

cursor.executemany(sql, insert_list[:5])
conn.commit()

这可以插入到数据库中

我尝试过：

sql = 'SET GLOBAL max_allowed_packet=50*1024*1024'
cursor.execute(sql)

在执行excuteMany()之前出现了错误：

pyodbc.ProgrammingError: ('42000'，"[42000] [FreeTDS][SQL Server]'GLOBAL'不是一个已识别的SET选项。 （195）（SQLExecDirectW）")

我该如何解决这个问题。

谢谢。

- YONG BAGJNS

Answer 1

你的问题不在于数据量本身，而是你的一些元组包含了无法直接用作SQL语句参数值的numpy.int64值。例如，

a = numpy.array([10, 11, 12], dtype=numpy.int64)
params = (1, 1, a[1], 1, 1, 1)
crsr.execute(sql, params)

会抛出

ProgrammingError: ('参数类型无效。param-index=2 param-type=numpy.int64', 'HY105')

因为第三个参数值是你的numpy数组 a 中的 numpy.int64 元素。使用 int（）转换该值将避免此问题：

a = numpy.array([10, 11, 12], dtype=numpy.int64)
params = (1, 1, int(a[1]), 1, 1, 1)
crsr.execute(sql, params)

顺便提一下，之所以

sql = 'SET GLOBAL max_allowed_packet=50*1024*1024'
cursor.execute(sql)

无法工作的原因是max_allowed_packet是MySQL设置，在Microsoft SQL Server中没有任何意义。

- Gord Thompson

---

我该如何使用Pandas DataFrame来实现这个操作？我已经尝试了对每一列使用df[col].astype(int)，但是依然没有成功。 - Matt Camp

@MattCamp - 我刚刚尝试了一个简单的测试，使用 df.values.tolist()，它对我有效。如果您需要进一步的帮助，您应该提出一个新问题。 - Gord Thompson

是的，我已经解决了它。因为某个字段中有重复项，它不允许我提交数据框架，所以出现了一些问题。当我尝试减少记录时，它会抛出上述错误。但是，当我只是更改了重复记录以测试一下，它就可以工作了。谢谢。 - Matt Camp

当出现“无效参数类型”错误时，有没有好的方法可以确定哪些值导致了错误？例如，当有大量列时，并不总是明显哪一列导致了错误... - quantif

Answer 2

我和Robert做了相同的事情，把所有内容都转换为字符串。在我的情况下，这是一个pandas数据框，我将其转换为字符串类型：

data = pandas.read_json(...)
data.astype(str).to_sql(...)

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.astype.html

如果您检索的数据包含URL，可能会出现“未知协议”错误（或类似错误）。如果您在将其转换为字符串类型后仍然收到此错误，请尝试使用 StringIO 替代：

import requests
from io import StringIO
...
data = pandas.read_json(StringIO(response.text))

其中response是来自requests库的Response对象实例，它的属性text包含JSON文本数据。

- Marcos

Answer 3

对于阅读这篇文章的任何人来说，这个问题一定让你苦恼不已。

我的最终解决方案是将所有变量转换为'str'，这样就可以正常工作了。

- Robert Franklin

Answer 4

当我使用pyodbc将参数传递到SQL INSERT语句中时，遇到了相同的错误。正如Robert所提到的那样，只需将变量转换为字符串即可解决问题。

例如： 语法 - str()

cursor.execute("INSERT INTO Table ([col1],[col2],[col3]...])VALUES(?,'test',?)", str(value1), str(value3))

- Lateef Mohammed