SQLiteにpythonで数億行いれてみた

mongodbも限界がきた。

Mongodb 最短入門(1)EC2にインストール&起動&いきなり数万件入れてみた
Mongodb 最短入門(2)数万件いれて探してみた
Mongodb 最短入門(3)数百万件でも速くしてみた

数千万件のデータから厳しいmongoさん。
億の壁も見えてきたので、意外なところでSQLLiteの登場。

衝撃 NoSQL KVS お手軽DB mongo redis sqlite ローカルで神速だったのは。。。

加工が必要ない場合は↑　こちらが最強でした。

シビアにいうと、ファイルはgzのままCSVリーダーにかましたほうがよいです。
通常のディスクからファイルを読みだすより、圧縮をメモリで展開するほうが物理的には速いからです。
もちろんメモリよりファイルをリードするのが速いケース(SSD?nvme?)は除いて。
それで、CSVにはわりとカラムがあるよって時にはプレースホルダを使いたいです。

SQLが見やすいように、:column_name1 ってカラム名でプレースホルダ使って、エスケープとか自動でサニタイズしてもらいたいです。

CSVの行リストにカラム名をキーにして一発で辞書にする:

data=dict(zip(keys,row))

keys はカラム名のリスト　例:('id','title')
row はCSVの行を区切り文字でわけたリスト 例: 1111"\t"aaaa
これでdata は{ id:1111,title:aaaa} みたいな辞書になっている。魔法だ。

cursor.execute('insert into table (:id,:title)',data)

mysqlみたいにプレースホルダが使える。辞書でないときは?でもいい

cursor.execute('insert into table (?,?)',row)

あと、おまじない
conn.text_factory = str

というのを誰も教えてくれなかったので、怒られたではないですか!
sqlite3.ProgrammingError: You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings

# coding: utf-8
import sqlite3
import csv
import gzip

conn = sqlite3.connect("honyahonya.db")
conn.text_factory = str
csr = conn.cursor()

keys=(
'id',
'title',
'date',
'article'
)

with gzip.open("honyahonya.csv.gz", 'rU') as f:
    reader = csv.reader(f, delimiter="\t")
    for row in reader:
        #データに自信がある場合は、カラム数の確認なんて省略したほうが速い
        if len(row)==len(key):
            d=dict(zip(keys,row))
            csr.execute('insert into tracks (id,title,date,article) values(:id,:title,:date,:article)' , d)
            conn.commit()