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

JayPeerachai · Accepted Answer · 2020-12-17T06:54:29.963

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

edited Dec 17 '20 at 06:54

answered Dec 17 '20 at 05:54

JayPeerachai

3,499
3
14
29

Since all are integer datatype but still is not coming even i tried row[0],row[1],row[2] still the same – Chanukya Dec 17 '20 at 06:03
Have you tried the integer like 1, 2, 3? Was it worked? – JayPeerachai Dec 17 '20 at 06:06
It is weired. Can you try to specify the datatype of the columns when you read csv as `Int64` – JayPeerachai Dec 17 '20 at 06:13
Can you please help me where i have to sppecify – Chanukya Dec 17 '20 at 06:15
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/226078/discussion-between-chanukya-and-jaypeerachai). – Chanukya Dec 17 '20 at 06:18

Error while loading data from csv to table using pandas dataframe

1 Answers1

SOLUTION