sql日付データをつっこんでみる

入力データ
0_s.csv

2005-11-1,2005-11-1,43,31
2005-11-2,2005-11-3,44,33
2005-11-2,2005-11-3,45,32
...

1_s.csv

2005-11-1,2005-11-1,3,1
2005-11-2,2005-11-3,4,3
2005-11-2,2005-11-3,5,2
...

....

3000_s.csv

プログラム

require 'rubygems'
require 'sqlite3'

puts "h1"
db=SQLite3::Database.new("syoku.sqlite3")


db.execute("drop table if exists food_count_data")
db.execute("create table if not exists food_count_data(\"a\")")

id_list=(0..100).to_a

k=0;

id_list.each{|i|
        key_list=[]
        value_list=[]

        puts "id"+","+k.to_s+","+i.to_s
        #f=open("syoku_data_base/#{i}_ss.csv","r")

        f=open("syoku_data_base/#{i}_s.csv","r")
        list1=[]
        f.each{|j|
                j.chomp!
                j2=j.split(",")
                list1.push(j2[3])
                #puts j2[0]
                #j2[0]="test"
                #db.execute("alter table food_count_data add (\"#{j2[0]}\")")
                #exit

                if k==0 then
                        puts j2[0],j2[3]
                        db.execute("alter table food_count_data add \"#{j2[0]}\"")
                end
                key_list.push("\"#{j2[0]}\"")
                value_list.push("\"#{j2[2]}\"")

                #db.execute("insert into food_count_data(\"#{j2[0]}\") values(#{j2[2]})")




        }
        keys=key_list.join(",")

        values=value_list.join(",")
        #puts keys
        #puts values

        db.execute("insert into food_count_data(#{keys}) values(#{values})")
                #str2=list1.join(",")
                #str2="1,1,1,1"
        #       str2=j2.join(",")

        #       j2=db.execute("insert into food_count_data values(\"#{str2}\")")

        k=k+1
        #if k==100 then
        #       exit
        #end
}
db.close()

これでつっこむと、行が列名になり、普通にselect文でとれなくなるので注意

[ ]で囲む必要あり。
なしだと引き算，'とか"だと文字列と解釈される

 select [2006-11-01] from food_count_data;

http://nozawashinichi.sakura.ne.jp/usingmt/2013/04/sqlite.html
本当は数字が頭の列名はいけないみたい。。

こっちのが一般的

require 'rubygems'
require 'sqlite3'

puts "h1"
#db=SQLite3::Database.new("syoku.sqlite3")

db=SQLite3::Database.new("syoku.sqlite3")
list_table="adj_list"
data_table="adj_count_data"
input_dir="../total/data/blog_all"
tail_text="_ss.csv"
list_file="../total/id_adj.txt"

puts "h2"
db.execute("drop table if exists #{list_table}")
db.execute("create table if not exists #{list_table}(id integer,foodname text,record_date text,last_update text,status text,update_status text)")


puts "h3"
f=open("#{list_file}")

record_date="2014-10-08"
status="T"
last_up_date="2014-08-10"

f.each{|i|
        i.chomp!
        puts i;
        i=i.split(",")
        db.execute("insert into #{list_table} values(#{i[0]},\"#{i[1]}\",\"#{last_up_date}\",\"#{record_date}\",\"#{status}\",\"#{status}\")")
}


puts "h4"
id_list=db.execute("select id from #{list_table}").flatten
puts "h5"

db.execute("drop table if exists #{data_table}")
db.execute("create table if not exists #{data_table}(\"id\" text)")

k=0;
id_list.each{|i|
        key_list=["id"]
        value_list=[k.to_s]

        puts "id"+","+k.to_s+","+i.to_s
        #f=open("syoku_data_base/#{i}_ss.csv","r")

        f=open("#{input_dir}/#{i}#{tail_text}","r")
        list1=[]
        f.each{|j|
                j.chomp!
                j2=j.split(",")
                list1.push(j2[3])
                #puts j2[0]
                #j2[0]="test"
                #db.execute("alter table food_count_data add (\"#{j2[0]}\")")
                #exit

                if k==0 then
                        puts "test123",j2[0],j2[3]
                        db.execute("alter table #{data_table} add \"#{j2[0]}\" numeric")
                end
                key_list.push("\"#{j2[0]}\"")
                value_list.push("\"#{j2[2]}\"")

                #db.execute("insert into food_count_data(\"#{j2[0]}\") values(#{j2[2]})")




        }
        keys=key_list.join(",")

        values=value_list.join(",")
        #puts keys
        #puts values

        db.execute("insert into #{data_table}(#{keys}) values(#{values})")
                #str2=list1.join(",")
                #str2="1,1,1,1"
        #       str2=j2.join(",")

        #       j2=db.execute("insert into food_count_data values(\"#{str2}\")")

        k=k+1
        #if k==100 then
        #       exit
        #end
}
db.close()