"Invalid parameter type" (numpy.int64) when inserting rows using executeemany () function

Question

I am trying to insert a bunch of data into a 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()

It can insert into the database

I tried:

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

before excutemany (), but it has an error:

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

.

.

Answer 1

, , numpy.int64, SQL. ,

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.int64 numpy a. 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.