Error while loading data from csv to table using pandas dataframe

Question

I am having the csv file, when I am trying to load CSV to the table using pandas dataframe I am getting an error please help me

import pyodbc
import pandas as pd
df = pd.read_csv("C:\PYTHON\csv.csv")
conn = pyodbc.connect('Driver=ODBC Driver 17 for SQL Server;'
                      'Server=SIS10647\MSSQLSERVER14;'
                      'Database=LeelaVenkatesh;'
                      'Trusted_Connection=yes;')
cursor = conn.cursor()
for index, row in df.iterrows():
    print(row)
    cursor.execute("INSERT INTO pandas(NAME,DEPART,COLUM) values (?,?,?)", row.NAME, row.DEPART, row.COLUM)
    conn.commit()
    cursor.close()

iam getting following error :

Traceback (most recent call last):
  File "C:/Users/lrangu/PycharmProjects/pythonProject/PANDAS.py", line 10, in <module>
    cursor.execute("INSERT INTO pandas(NAME,DEPART,COLUM) values (?,?,?)", row.NAME, row.DEPART, row.COLUM)
pyodbc.ProgrammingError: ('Invalid parameter type.  param-index=0 param-type=numpy.int64', 'HY105')

Answer 1

From the error that you got:

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

Some of the elements in the parameters (row.NAME, row.DEPART, row.COLUM) are numpy.int64 that can not be used directly as a parameter for the SQL statement. So, you need to make sure that all of the parameters are in the correct and supported type before you execute the SQL statement.

SOLUTION

Specify the datatype as Int64:

df = pd.read_csv("C:\PYTHON\csv.csv", dtype="Int64")