Remove Timestamp from tuple before writing to DB- python-CodePudding

i am converting values from a dataframe as a tuple before inserting into table. some of the values are TimeStamp. Conversion to tuple puts datetime values with in Timestamp(dt). THere are many fields which are timestamp not all are populated at the same time. How do i remove the Timestamp (dt) surrounding the dt

Sample row/tuple below. i need to insert close to million rows efficiently

(None, 443097820.0, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, Timestamp('2020-03-14 09:49:00'), 39.0, 42.0)

once i attach to the Insert, it will be like this

Insert into TABL1 (field1, field2,field3...... fieldn) values (None, 443097820.0, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, Timestamp('2020-03-14 09:49:00'), 39.0, 42.0)

Now the insert will fail because of the timestamp with this error SQL Insertion Exception occured! (195, b"'Timestamp' is not a recognized built-in function name.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")

Also, i cannot convert the dataframe to str(df) because, some of the fields are smallint, int etc and None will get me an error

Any help is really appreciated.

thanks

CodePudding user response：

df = df.astype(str)

This changes all the items in your DataFrame as strings including the datetime objects. Then you can just convert them to tuples afterwards.