Sqlite3 Tips 1

# import from csv
sqlite> .separator ,
sqlite> .import id_q4.csv q4

# output to csv

sqlite> .mode csv
sqlite> .output ***.csv
sqlite> .select * from master;

.mode html もある

# シェルスクリプトを使って、SQLiteのレコードを取得する

#!/bin/sh
SQL="SELECT * FROM table_name"
COMMAND="sqlite -separator , database_name"
echo $SQL | $COMMAND

## loop
for i in {1..35};
do
    echo "=================== q${i} =================="
    SQL="SELECT customer_id, q${i} FROM master;"
    #echo $SQL | $COMMAND | sed -e "s/\|/,/"  > id_q${i}_tmp.csv
    numelem=`echo "select numelem from label where name='q${i}';" | $COMMAND`
    #python extract.py id_q${i}.csv ${i} ${numelem} > dump${i}.csv
done

# sql read

sqlite> .read createtable.sql