How to transform a huge CSV into SQLite using Pandas?

Question

I have a huge table (about 60 GB) in form of an archived CSV file. I want to transform it into an SQLite file.

What I do at the moment in the following:

import pandas
import sqlite3
cnx = sqlite3.connect('db.sqlite')
df = pandas.read_csv('db.gz', compression='gzip')
df.to_sql('table_name', cnx)

It works fine for smaller files but with the huge files I have memory problem. The problem is that pandas reads the whole table into memory (RAM) and then saves it into SQLite file.

Is there an elegant solution to this problem?

Answer 1

I haven't done any work with CSVs of that size, but it sounds like the kind of thing Odo might solve quickly.

I did a cursory check of the docs, and it appears they've written something addressing the topic of larger-than-memory CSV parsing into SQL databases that specifically calls out SQLite3 as a destination.

Here's the example they publish for parsing a 33 GB text file.

In [1]: dshape = discover(resource('all.csv'))

In [2]: %time t = odo('all.no.header.csv', 'sqlite:///db.db::nyc',
   ...:               dshape=dshape)
CPU times: user 3.09 s, sys: 819 ms, total: 3.91 s
Wall time: 57min 31s

Answer 2

This is going to be problematic with pandas due to its size. Any reason you can't use the csv module and just iterate through the file.

Basic idea (untested):

import gzip
import csv
import sqlite3

with gzip.open('db.gz') as f, sqlite3.connect('db.sqlite') as cnx:
    reader = csv.reader(f)
    c = cnx.cursor()
    c.executemany('insert into table_name values (?,?,...)', reader)