SQLite!不止是資料庫系統,也能快速轉換資料格式

SQLite除了是一個輕量型的關聯式資料庫管理系統,也是很方便的資料轉換工具。例如:

  1. CSV格式轉換成JSON、HTML或Markdown等格式
  2. 匯入CSV檔案成資料表,再產生INSERT敘述
  3. 直接讀取JSON資料做查詢

1. SQLite 3的安裝

2. SQLite 3命令行格式

格式: sqlite3 [OPTIONS] FILENAME [SQL]

  • 顯示命令行選項:-help
  • FILENAME即資料庫檔案,若檔案不存在會自行建立
  • 進入sqlite後即為交互式操作介面,可輸入命令或SQL敘述
    • 命令以半形的 . 開頭,如 .help、.quit、.tables等,可輸入部份字串
    • SQL敘述以半形分號結尾

Windows的字碼必須是 950(繁體中文)或936(簡體中文)才能正確顯示中文。

用chcp 950或chcp 936切換。

3. import CSV檔

  • 執行sqlite3並建立 test.db
    shell
    sqlite3 test.db
  • 用 .import 命令匯入.csv檔以建立資料表
    .import --csv covid19_global_cases_and_deaths.csv covid19
  • .tables.schema檢查資料表
    sqlite> .schema covid19
    CREATE TABLE IF NOT EXISTS "covid19"(
    "country_ch" TEXT,
    "country_en" TEXT,
    "cases" TEXT,
    "deaths" TEXT
    );
  • 檢視資料表covid19
    select * from covid19;
  • 數值加上千位逗點
    select country_ch, printf ("%,d",cases) as Cases from covid19 order by cases desc;

4. 變更欄位型態

  • 原始的.csv檔將cases與deaths欄位設定成文字,應該要使用數值型態才能做數值運算
  • sqlite沒有直接變更欄位型態的語法,必須用資料表替換的操作

4.1. 變更資料內容

  • 用SQL敘述將cases與deaths裡的逗點移除
updat covid19 set cases=REPLACE(cases, ',', '');

4.2. 改名並重建資料表

alter table covid19 RENAME TO _old;

create table covid19 (
  "country_ch" TEXT,
  "country_en" TEXT,
  "cases" integer,
  "deaths" integer
);

4.3. 插入資料

insert into covid19 (country_ch,country_en,cases,deaths)
select country_ch,country_en,cases,deaths from _old;

5. 匯出檔案

5.1. 匯出CSV格式

.mode csv
.output covid19.csv
select * from covid19 order by cases;
.output
.shell notepad covid19.csv

5.2. 匯出JSON格式

.mode json
.output covid19.json
select * from covid19 order by cases;
.output
.shell notepad covid19.json

5.3. 可使用的匯出格式

sqlite> .help .mode
.mode MODE ?TABLE?       Set output mode
   MODE is one of:
     ascii     Columns/rows delimited by 0x1F and 0x1E
     box       Tables using unicode box-drawing characters
     csv       Comma-separated values
     column    Output in columns.  (See .width)
     html      HTML table code
     insert    SQL insert statements for TABLE
     json      Results in a JSON array
     line      One value per line
     list      Values delimited by "|"
     markdown  Markdown table format
     quote     Escape answers as for SQL
     table     ASCII-art table
     tabs      Tab-separated values
     tcl       TCL list elements

6. 直接讀取JSON檔做查詢

select json_extract(value, '$.country_ch') as '國家' 
  ,json_extract(value, '$.cases') as '案例數' 
from json_each(readfile('covid19.json'));

7. 常用命令

命令 說明
.databases 列出資料庫
.dbinfo 顯示資料庫內部資訊
.backup 備份資料庫
.restore 還原資料庫
.tables 列出所有資料表
.schema 顯示資料表資訊
.indexes 顯示索引資訊
.show 顯示系統設定
.mode 設定輸出格式
.separator 設定欄位分隔文字
.nullvalue 設定NULL值顯示文字
.timer 設定執行時間切換
.eqp 設定是否顯示執行計劃
.read 讀取並執行外部.sql檔
.output 將查詢結果輸出到指定檔案
.import 匯入CSV檔案
.shell 執行作業系統命令
.exit / .quit 退出SQLite 3

相關連結

教學影片

##

您可能也會有興趣的類似文章

簡睿

服務於軟體業的資訊老兵。興趣廣泛,學習力佳,樂於分享所知所學。

您可能也會喜歡…

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *