How to Ignore errors in Dataframe to_sql method

Question

I am using Pandas DataFrame.to_SQL() to insert data from dataframe to table. Nothing gets loaded if even a single record has loading issues, for example: if a integer column has character data in one row or date format is not valid in one record etc.

How to ignore these errors? I want to load atleast the data which is valid instead of not loading anything at all in this case.

Answer 1

It seems that you are looking for Python's try and except block.

Python executes code following the try statement as a “normal” part of the program. The code that follows the except statement is the program’s response to any exceptions in the preceding try clause. (Source)

For your specific use case, as you want to use pandas.DataFrame.to_sql, the following should do the work

def insert_data(df, table_name): # df is a dataframe, table_name is a string
    try: # if there are errors, ignore them and load the valid data
        df.to_sql(table_name, con=engine, if_exists='append', index=False) # if_exists='append' means that if the table already exists, it will append the new data to the existing table
    except Exception as e:
        print(e) # print the error
        df.to_sql(table_name, con=engine, if_exists='append', index=False, chunksize=1000) # chunksize=1000 means that the data will be loaded in chunks of 1000 rows