Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.8k views
in Technique[技术] by (71.8m points)

python - "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)

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

reduce to 100 tuples:

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

reduce to 5 tuples:

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

This can insert to database

I have try to:

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

before excutemany() but it have an error:

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

How did i solve this.

Thank you.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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)

will throw

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

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)

By the way, the reason that

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

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...