0

I am trying to insert new data values into an existing table in Teradata from python. I was able to establish the connection but then it gave me this error: [Teradata database] (-2621) Bad character in format or data of df.columnX')

Below is the value I am trying to load in:

Column A    Column B     Column X
IH78        0.00          39
SK901       0.00          NaN

Code that I used (ref: Connecting Python with Teradata using Teradata module):

import teradata
import pandas as pd
import numpy as np

udaExec = teradata.UdaExec (appName="test", version="1.0", logConsole=False)

with udaExec.connect(method="odbc",system="DBName", username="UserName",
                      password="Password", driver="DriverName") as connect:

    #We can divide our huge_df to small chuncks. E.g. 100 churchs
    chunks_df = np.array_split(huge_df, 100)

    #Import chuncks to Teradata
    for i,_ in enumerate(chunks_df):

        data = [tuple(x) for x in chuncks_df[i].to_records(index=False)]
        connect.executemany("INSERT INTO DATABASE.TABLEWITH5COL values(?,?,?)",data,batch=True)
  • type in python for Column X: Int32
  • type in teradata for Column X: Integer

I can load the first line in with no issue at all but it is the second one that returns me an error message. Thank you all and any help would be appreciated!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Cherrie Wu
  • 1
  • 1
  • How is column X defined? – Andrew Jan 26 '22 at 17:02
  • @Andrew Int32 in python and Integer in teradata – Cherrie Wu Jan 26 '22 at 17:07
  • I don't do much python, but if you are getting `NaN' (not a number, I think?) back from python, then you won't be able to insert that value into an integer column in Teradata. – Andrew Jan 26 '22 at 17:26
  • @Andrew Thanks for the reply. Yes, I doubted that too. I have tried to change NaN to None and make this column X nullable in Teradata but none of these work. Do you have any suggestion on how to load null values to Teradata when the type on the column should be integer? – Cherrie Wu Jan 26 '22 at 18:09
  • Changing NaN to None is on the right track. See for example https://stackoverflow.com/questions/70803876/why-does-df-where-not-replace-all-null-values Also consider using supported `teradatasql` Python driver instead of `teradata` package (which requires separate Teradata ODBC driver install) – Fred Jan 26 '22 at 22:52

0 Answers0