Hatena::ブログ(Diary)

arupaka-_-arupakaの日記

2015-02-27 [sql][sqlite3] sqlite3でレコードがなければ追加、あれば更新 UPSE

Rでデータベース(sqlite3)に逐次的にデータを詰める

Rでデータベース(sqlite3)に逐次的にデータを詰める.

  • 行うこと

(1)更新したい日が、更新中のデータベースの更新日付と同じかを確認->異なるなら新しいtableに更新tableを置き換える.

(2)更新tableをにデータを追加していく.(データに列がたりない場合は追加).

  • tableは

ID|日付1|日付2|日付3|...という形式

(IDをprimary keyとすることでreplace文を利用する).

  • Rコード
library("RSQLite")

data_list<-list()
####疑似データ生成(いらない)###
for(i in 1:10){
        date1=as.Date("2006-11-1")+1:10
        val<-1:10+rnorm(10)
        d_trend<-data.frame(1:length(date1),format(date1),val)
        data_list[[i]]<-d_trend

}
####疑似データ#########



#更新した日が、更新中のデータベースの日付と同じかを確認
drv<-dbDriver("SQLite")
con<-dbConnect(drv,"syoku_tmp.sqlite3")
now_update<-unlist(dbGetQuery(con,"select now_update from update_info"))
want_update="2011-01-01"

if(want_update != now_update){
        cat(now_update,"\n")
        cat("test\n")

        try(dbGetQuery(con,"drop table trend_time_series_new;"))
        try(dbGetQuery(con,"drop table freq_mean_time_series_new;"))


        dbGetQuery(con,"create table trend_time_series_new(id primary key);")
        dbGetQuery(con,"create table freq_mean_time_series_new(id primary key);")

}



for(i in 1:length(data_list)){
        id=i
        d<-data_list[[i]]
        column_names_trend=d[,2]
        trend_data=d[,3]

    #tableに列があるかどうか調べる.なければ追加.
        v<-length(dbGetQuery(con,"pragma table_info(trend_time_series_new)")[,1])
        if(v==1){
                for(iiii in 1:length(column_names_trend)){
                      
                        query=paste(collapse="",sep="","alter table trend_time_series_new add [",column_names_trend[iiii],"];")
                        cat(query,"\n")
                        dbGetQuery(con,query)


                }


        }


        keys<-(dbGetQuery(con,"pragma table_info(trend_time_series_new)")[,2])
        #keys_date<-paste(collapse=",","[",keys[-1],"]")

        keys_date<-paste(collapse=",",sep="","[",column_names_trend,"]")
        keys<-paste(collapse=",",keys[1],",",keys_date)
        values<-paste(collapse=",",c(id,trend_data))

        query=paste(sep="","replace into trend_time_series_new(",keys,")"," values(",values,")")
        #cat(query)
        cat("data_inasert",i,"\n")
        dbGetQuery(con,query)

}
        #cat(d,"\n")



  • 結果のtable

select * from trend_time_series_new;

sqlite> select * from trend_time_series_new;

1|1.74216426642007|1.15746096911096|1.22604090764894|5.74384946199283|4.16048344102|6.35242713746178|6.53509265750246|8.32309181282388|9.69234347526966|9.1514501713778

2|0.688924429592484|2.28650453208968|3.79120527680377|3.74733483142553|4.52796319479229|4.68702036633908|7.20527548613953|8.04196013134207|9.65045919152365|9.71001102355752

3|1.10974853832513|1.61144018668012|1.5606415540936|4.75323024946711|3.6497842071526|6.71511555342707|6.182777490438|8.18276764840514|8.57641822395217|7.84627050663797

4|1.20730610096204|0.281577834874096|3.17703408481475|5.18286980965751|4.94179548197136|4.83402588687983|8.51900812416311|6.90504033171872|9.49530425149758|7.14295646107687

5|-0.134263141140184|0.855261155079369|3.37719644963614|4.73046226157005|4.14067542615129|5.40822985121723|6.76285129799583|7.34177145603216|8.72744714385967|10.0117366284731

6|2.30932391204585|3.17027510763729|3.37304652930384|4.56961553857534|5.22315801114618|5.37398822282897|7.50701131302169|9.22080579004208|9.59761139805944|10.0453363696911

7|-0.47419040940387|1.00486064112253|4.11148038417412|3.7703426730129|5.96332733459318|5.50658959889634|8.29465887067891|8.02579060977058|8.4711712672579|8.28311446962813

8|1.86615705093297|1.54959822340292|3.59102997187421|4.27356722126329|4.18571516042845|4.12003377550779|6.49102002199568|7.27969332422389|7.71675369855051|10.3055700402934

9|-0.0543844169168486|1.53941205253435|2.56373064913614|3.77976797328231|4.3256378014893|5.35177659593993|7.64559406325345|8.48013065052815|10.4075950737314|10.2263907468294

10|1.07984537947647|2.48262887205794|3.44561959903089|2.37301569285974|4.62380489538891|5.87463538778615|8.00319173291311|7.22764965875162|9.41912984414329|10.0110703209104

sqlite3でレコードがなければ追加、あれば更新 UPSERT

sqlite3でレコードがなければ追加、あれば更新 UPSERT

http://web-academia.org/it_business_web_development/%E3%83%97%E3%83%AD%E3%82%B0%E3%83%A9%E3%83%9F%E3%83%B3%E3%82%B0/482/

を参考にする.

  • SQLはREPLACE文を使う.
  • 使用するためにPrimary keyの設定が必要.

(1)primary keyがtableを作成

create table test_table(id text primary key, a data)

(2)これに対して,replace文

replace into test_table(id,val) values(2,4);

入っているかみる

select * from test_table;

出力例

2|4

(3−2)更新

replace into test_table(id,val) values(2,5);

更新されているか確認

select * from test_table;

出力例

2|5

(3−1)追加(新しいidをいれる)

replace into test_table(id,val) values(4,5);

追加されているか確認

select * from test_table;

出力例

2|5

4|5

2015-02-20

データベースsqlite3をrubyのCGI (Web)経由で使うときの注意

データベースsqlite3をrubyCGI (Web)経由で使うときの注意。。

権限がなくてデータベースに書き込めないときになにもエラーをはかないで

はまるので注意.

2014-12-03

sqlでデータ名が異なる場合テーブルに挿入

sqlでデータ名が異なる場合テーブルに挿入

require 'rubygems'
require 'sqlite3'



f=open("food_list_2014_12_03_b.txt")


f=f.map{|i| i.split(",")}.sort_by{|elem| elem[3];}

#f.each{|i|

#       puts i;
#}

#exit

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

f.each{|data|
     
        foodname=data[1]

        last_update="1001-01-01"
        recode_date=data[3].split(" ")[0];
        status="T"
        update_status1=""
        update_status2="F"

        id_list_max=db.execute("select id from food_list;").max
        id=id_list_max[0].to_i+1
        #puts recode_date;
        #puts id
        f=db.execute("select * from food_list where foodname=\"#{foodname}\";")


        #puts id_list;

        #exit

        if f.length==0 then

                db.execute("insert into food_list values(#{id},\"#{foodname}\",\"#{recode_date}\",\"#{last_update}\",\"#{status}\",\"#{update_status1}\",\"#{update_status2}\");")
                #f=db.execute("select * from food_list where foodname=\"#{data[1]}\";")
        end
}


2014-12-02

sqlのデータベースををコマンドラインから使う

sqlデータベースををコマンドラインから使う

echo "select * from adj_count_data where id="2";" | sqlite3 syoku.sqlite3  | sed "s/|/\n/g"

2014-12-01

CSVデータからsqlにデータをいれるためのスクリプトを書くスクリプト

CSVデータからsqlにデータをいれるためのスクリプトを書くスクリプト

CSVファイル

1,リンゴ

2,ミカン

3,おもち

4,桃

adj_list_hokan_tmp.sh

for i in `cat id_adj_hokan.txt`;
do

        j1=`echo $i | awk -F"," '{print $1}'`;
        #echo $j1

        j2=`echo $i | awk -F"," '{print $2}'`;
        #echo $j2
        echo "insert into adj_list values($j1,$j2,\"2014-08-10\",\"2014-08-10\",T,T)"

done

使い方,

sh adj_list_hokan_tmp.sh > tmp1
sqlite3 xxx.sql < tmp1