0

I try to load pandas dataframe to SQL Server. The dataframe has following columns:

  • userId - int
  • saleId - int
  • regTime - int
  • countryId - int
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
for index, row in users.iterrows():
     cursor.execute('INSERT INTO [dbo].[users]([userId],[saleId],[regTime],[countryId]) values (?,?,?,?)', 
                     row['userId'], 
                     row['saleId'], 
                     row['regTime'],
                     row['countryId'])
     cnxn.commit()
cursor.close()
cnxn.close()

But i have the following error:

---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-103-383c1873cad8> in <module>
      4                     row['saleId'],
      5                     row['regTime'],
----> 6                     row['countryId'])
      7     cnxn.commit()
      8 cursor.close()

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

countryId column has values from 0 to 9 (codes)

How can i correct it?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vlad Aleshin
  • 361
  • 4
  • 15
  • Please try this instead of the for loop and let me know if you face any issues `data_frame.to_sql(name='target_table_name', con=cnxn, if_exists = 'replace' )` – Equinox Aug 15 '20 at 08:19
  • I tried it, but i have another error: pyodbc.interfaceerror) ('im002', '[im002] – Vlad Aleshin Aug 15 '20 at 08:22
  • Can you share the complete error line? Also double check you are able to connect to database and execute some query `cursor.execute("select * from xxx")` and you have write access – Equinox Aug 15 '20 at 08:40
  • https://stackoverflow.com/questions/41973933/invalid-parameter-type-numpy-int64-when-inserting-rows-with-executemany – Kohelet Aug 15 '20 at 09:19

0 Answers0