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