jsonからSQLITEのテーブル作成データ収集
「APIからデータ収集。sqliteに保存したい。JSONの構造を確認して
キーからカラムを入力して。。。。ってキーが１００個近くあるんですが・・・」
CREATE文書くだけで１時間とかありえない！
保存のためのテーブルをサクッと作りたい。ツール作りました。
ツールページは下記↓
[使い方]
1 jsonを入力して解析
テキストボックスにjsonをテキスト入力。
実行するボタンをクリック後 結果をすべて表示するにチェック
2 jsonから取り出す構造を探す。
下部エディターに整形されたjson構造体が表示される
構造は、 ｛ ・・・｝でマーカーされるのでその中の [ ]をコピーする
コピーしたデータを再度１の枠に入れて実行する。
3.SQL文の取り出しを行いテーブルを作成。
sql create tableのエディタ部にコマンドが表示される。後は、コピーしてSQLを実行するだけ。
table_nameは、書き換えてください。
なお、カラム名にヘッダーをつけるに文字を入れると「ヘッダー文字＋キー」で生成されます。
4．具体的な使用方法
さて、テーブルを作ったけどこれでどうするの？という場合のサンプル。
PHPで収集する時のサンプル。
ひとまず、create文でsqliteのテーブルは作ったとする。
|
1 2 3 4 5 6 7 8 |
//file_get_contentsで目当てのjsonを取得 $target_url="https://sumpl.com?json=1"; $dom=file_get_contents($target_url); $json=str_replace(""",'"',$dom); $json=str_replace(" ","",$json); $json=str_replace("\n","",$json); $obj = json_decode($json,true); //最終的にjsonを配列に変換する |
上記でurlからjsonを取得し、配列にする。
ここで問題なのがjsonの中のキーがいつでも存在するわけでは無いということです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
ki:{ "バナナ”："フィリピン", "りんご”："青森", ”みかん”:”愛媛” } ki:{ "バナナ”："フィリピン", ”みかん”:”愛媛”, } |
上記の様に、kiの二回目にはりんごがない場合だってある。
これを定形の クエリ文で INSERTしたりupdateしたりすると、対応しないキーに苦しめられることもある。
nullで入力するとしても、変数を個数分だけ書かなくては行けないため現実的でない。
なので
|
1 2 |
$keyset_sql=["data_りんご","data_バナナ","data_みかん"]; //配列文字列SQL用から取得 $keyset_json=["りんご","バナナ","みかん"];//配列文字列JSON側から取得 |
などとして 予めキーの配列を作っておき、ループで該当したキーとデータを再度収集する。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
//objがjson取得後配列にした状態 $total_keyset=array(); //キーが存在した場合にキー名を入れる $c_data=array(); //キーが存在した場合に中身を入れる foreach($obj["hits"]["hits"] as $data){ for($i=0;$i< count( $keyset_ki);$i++){ $target=$data["_source"]["ki"]["{$keyset_ki[$i]}"]; if(is_array($target)){//入れ子になっている場合は、テキスト化してjsonとして再度保存する。 $target=json_encode($target,JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES); } array_push($c_data,$target); array_push($total_keyset,"ki_".$keyset_ki[$i]); } } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
$t=""; $tc=0; $q=""; //バインド用の?,?,?,?,?,?,?,?,?,?,?,?,・・・・ foreach($c_data as $c){ $q=$q."?,"; $t=$t.$total_keyset[$tc].","; $tc=$tc+1; } $q=rtrim($q,","); $t=rtrim($t,","); define('Sqlfilepath', 'D:\new_sql.db'); $db = new SQLite3(Sqlfilepath); $stmt2 = $db->prepare("insert into table_name($t) values($q);"); $n=0; foreach($c_data as $c){ $n=$n+1; $stmt2->bindValue($n, $c, SQLITE3_TEXT); } $ex=$stmt2->execute(); |
あとは、取り出したデータの回数分の bindValueと、それに該当する「?,?,?」を生成(rtrimで最後の1文字[,]削除を忘れないように)
これでターゲットのURLを変えて繰り返すだけ。
＊＊注意点 カラム名に(を入れてはいけないよ。
最近のコメント