How to insert Null Value in database using teradatasql on a SMALLINT, Numeric overflow occurred during computation

Question

So I use the None object to define null values in pandas dataframe. This is an example of the row in the dataframe with the NaN and None

27     XXX         None        41.0          NaN           1.0        50.0          NaN           1.0        71.0          3.0  0.9826382611          162.0             3.0  0.9952958893        MTD

When I insert None in dataframe it becomes NaN which then when inserted into a small integer field in Teradata, I get an insert error. How can I tell teradata to insert those NaN to null values in teradata using python?

Numeric overflow occurred during computation.

In the below example, parameter 8 is NaN

Insert Exception in function insertDtframe, activated rollback: [Version 16.20.0.54] [Session 2258656] [Teradata SQL Driver] [Error 502] Batch row 3 bound parameter 8 type VARCHAR (448) differs from batch row 1 type FLOAT (480)
 at gosqldriver/teradatasql.(*teradataConnection).makeDriverErrorCode TeradataConnection.go:1074
 at gosqldriver/teradatasql.(*TeradataRows).validateBatchValues TeradataRows.go:947
 at gosqldriver/teradatasql.newTeradataRows TeradataRows.go:361
 at gosqldriver/teradatasql.(*teradataStatement).QueryContext TeradataStatement.go:122
 at gosqldriver/teradatasql.(*teradataConnection).QueryContext TeradataConnection.go:2036
 at database/sql.ctxDriverQuery ctxutil.go:48
 at database/sql.(*DB).queryDC.func1 sql.go:1464
 at database/sql.withLock sql.go:3032
 at database/sql.(*DB).queryDC sql.go:1459
 at database/sql.(*Conn).QueryContext sql.go:1701
 at main.goCreateRows goside.go:599
 at main._cgoexpwrap_e6e101e164fa_goCreateRows _cgo_gotypes.go:331
 at runtime.call64 asm_amd64.s:574
 at runtime.cgocallbackg1 cgocall.go:316
 at runtime.cgocallbackg cgocall.go:194
 at runtime.cgocallback_gofunc asm_amd64.s:826
 at runtime.goexit asm_amd64.s:2361
Completed Time: 2019-12-04 15:41:06.967610

Answer 1

As Fred suggested, use the function pandas.notnull to replace NaN to None

Initially when you insert a None object to pandas, it replaces it with NaN. If you want to insert into a database you need to convert those NaN to None. This is because the database will think you are inserting a string, especially on integer data type fields. Below is the code to convert NaN to any specific value. In my scenario it should be None.

cNoneDtframe = parsedDtframe.where((pandas.notnull(parsedDtframe)), None)