How to import big CSV file into Sqlite3 in python?

Question

I'm having a really big CSV file that I need to load into a table in sqlite3. I can't load whole CSV content as a variable into RAM because data is so big, that event with defining types for each column it cannot fit into 64 GB of RAM.

I've tried to use numpy and pandas to load and convert data, but still jumping way above RAM limit.

I would like to somehow read CSV 1 row at a time (or in smaller batches) and progressively save them into the database to keep RAM usage low. Would be perfect if it could be done using more than one CPU core.

Answer 1

I've found a solution digging myself and combining answers from other Stack Overflow questions. Code should be like this:

import sqlite3
import pandas as pd

def add_to_db(row, con):
    # Function that make insert to your DB, make your own.

def process_chunk(chunk):
    # Handles one chunk of rows from pandas reader.
    con = sqlite3.connect("favorita.db")
    for row in chunk:
        add_to_db(row, con)
    con.commit()

for chunk in pd.read_csv('data.csv', chunksize=100000):
    # Adjust chunksize to your needs and RAM size.
    process_chunk(chunk.values)

This could surely be further adjusted to use multi-threading, but I couldn't do it due to deadlocks in database when doing inserts in parallel. But if you have time, this is a solid solution.