To combine ON CONFLICT DO NOTHING to ensure data integrity and improve insertion efficiency during batch insertions, you can follow these steps to modify: First, you need to make sure that the database you're using (SQLite in this case) supports the ON CONFLICT DO NOTHING statement. For your existing code, suppose you want to insert the table is MarketData, can modify bulk_insert_mappings part code like this: the from sqlalchemy. Dialects. Sqlite import inserts
data_to_insert = [ { 'date': date.fromtimestamp(ts), 'time': some_time, 'asset': some_asset_id, 'opening': some_opening_value, 'high': some_high_value, 'low': some_low_value, 'closing': some_closing_value, 'volume': some_volume_value } for ts, some_time, some_asset_id, some_opening_value, some_high_value, some_low_value, some_closing_value, some_volume_value in your_data_source ]
insert_stmt = insert(MarketData).values(data_to_insert) on_conflict_stmt = insert_stmt.on_conflict_do_nothing(index_elements=['date']) # Assume that the 'date' column is a possible conflict column session.execute(on_conflict_stmt) session.commit() In the above code, you first create an insert statement insert_stmt and then use the on_conflict_do_nothing method to specify that no operation is performed in the event of a conflict (based on the specified column date). Finally, the statement is executed through session.execute and the transaction is committed. In this way, when the newly inserted data conflicts with the existing data in the date column, no operation is performed. In this way, the insertion efficiency is improved while data integrity is ensured. Note that you need to modify the column values in the data_to_insert and specify appropriate columns that may conflict based on the actual table structure and data.