“无效的参数类型" (numpy.int64)使用executemany()插入行时 [英] "Invalid parameter type" (numpy.int64) when inserting rows with executemany()

问题描述

我尝试将一堆数据插入数据库

I try to insert bunch of data to database

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)

cursor.executemany(sql, insert_list)

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

pyodbc.ProgrammingError: ('Invalid parameter type. param-index=4 param-type=numpy.int64', 'HY105')

减少到100个元组:

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

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

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

pyodbc.ProgrammingError:('无效的参数类型.param-index = 4 param-type = numpy.int64'，'HY105') 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()之前，但有错误:

before excutemany() but it have an error:

pyodbc.ProgrammingError :('42000'，"[42000] [FreeTDS] [SQL Server]'GLOBAL'不是公认的SET选项.(195)(SQLExecDirectW))

pyodbc.ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]'GLOBAL' is not a recognized SET option. (195) (SQLExecDirectW)")

我如何解决这个问题.

谢谢.

推荐答案

您的问题不在于数据本身的数量，而是您的某些元组包含numpy.int64值，而这些值不能直接用作SQL语句的参数值.例如

Your problem is not with the volume of data per se, it is that some of your tuples contain numpy.int64 values that cannot be used directly as parameter values for your SQL statement. For example,

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')

ProgrammingError: ('Invalid parameter type. param-index=2 param-type=numpy.int64', 'HY105')

因为第三个参数值是numpy数组a中的numpy.int64元素.用int()转换该值可以避免该问题:

because the third parameter value is a numpy.int64 element from your numpy array a. Converting that value with int() will avoid the issue:

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没有任何意义.

didn't work is that max_allowed_packet is a MySQL setting that does not have any meaning for Microsoft SQL Server.

这篇关于“无效的参数类型" (numpy.int64)使用executemany()插入行时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！