以一個簡單類別 (categories) 資料表為例，其 DDL 如下：

<span class="line"><span style="color: #81A1C1">CREATE</span><span style="color: #D8DEE9FF"> </span><span style="color: #81A1C1">TABLE</span><span style="color: #D8DEE9FF"> </span><span style="color: #88C0D0">categories</span><span style="color: #D8DEE9FF"> (</span></span>
<span class="line"><span style="color: #D8DEE9FF">    id </span><span style="color: #81A1C1">INTEGER</span><span style="color: #D8DEE9FF"> </span><span style="color: #81A1C1">PRIMARY KEY</span><span style="color: #D8DEE9FF"> AUTOINCREMENT,</span></span>
<span class="line"><span style="color: #D8DEE9FF">    </span><span style="color: #81A1C1">name</span><span style="color: #D8DEE9FF"> </span><span style="color: #81A1C1">TEXT</span><span style="color: #D8DEE9FF"> </span><span style="color: #81A1C1">NOT NULL</span><span style="color: #D8DEE9FF">,</span></span>
<span class="line"><span style="color: #D8DEE9FF">    </span><span style="color: #81A1C1">description</span><span style="color: #D8DEE9FF"> </span><span style="color: #81A1C1">TEXT</span></span>
<span class="line"><span style="color: #D8DEE9FF">)</span></span>
<span class="line"></span>

常用程式碼如下：

<span class="line"><span style="color: #81A1C1">import</span><span style="color: #D8DEE9FF"> sqlite3</span></span>
<span class="line"></span>
<span class="line"><span style="color: #616E88"># 如果找不到資料庫檔，就會新建一個</span></span>
<span class="line"><span style="color: #D8DEE9FF">con </span><span style="color: #81A1C1">=</span><span style="color: #D8DEE9FF"> sqlite3</span><span style="color: #ECEFF4">.</span><span style="color: #88C0D0">connect</span><span style="color: #ECEFF4">(</span><span style="color: #ECEFF4">"</span><span style="color: #A3BE8C">sample.db</span><span style="color: #ECEFF4">"</span><span style="color: #ECEFF4">)</span></span>
<span class="line"><span style="color: #D8DEE9FF">cur </span><span style="color: #81A1C1">=</span><span style="color: #D8DEE9FF"> con</span><span style="color: #ECEFF4">.</span><span style="color: #88C0D0">cursor</span><span style="color: #ECEFF4">()</span></span>
<span class="line"></span>
<span class="line"><span style="color: #88C0D0">print</span><span style="color: #ECEFF4">(</span><span style="color: #ECEFF4">'</span><span style="color: #A3BE8C">檢查 categories 資料表是否存在於 sambple.db 資料庫中:</span><span style="color: #ECEFF4">'</span><span style="color: #ECEFF4">)</span></span>
<span class="line"><span style="color: #D8DEE9FF">listOfTables </span><span style="color: #81A1C1">=</span><span style="color: #D8DEE9FF"> cur</span><span style="color: #ECEFF4">.</span><span style="color: #88C0D0">execute</span><span style="color: #ECEFF4">(</span></span>
<span class="line"><span style="color: #D8DEE9FF">    </span><span style="color: #ECEFF4">"""</span><span style="color: #A3BE8C">SELECT tbl_name FROM sqlite_master WHERE type='table'</span></span>
<span class="line"><span style="color: #A3BE8C">    AND tbl_name ='categories'; </span><span style="color: #ECEFF4">"""</span><span style="color: #ECEFF4">).</span><span style="color: #88C0D0">fetchall</span><span style="color: #ECEFF4">()</span></span>
<span class="line"></span>
<span class="line"><span style="color: #81A1C1">if</span><span style="color: #D8DEE9FF"> listOfTables </span><span style="color: #81A1C1">==</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">[]:</span></span>
<span class="line"><span style="color: #D8DEE9FF">    </span><span style="color: #88C0D0">print</span><span style="color: #ECEFF4">(</span><span style="color: #ECEFF4">'</span><span style="color: #A3BE8C">categories 資料表不存在!</span><span style="color: #ECEFF4">'</span><span style="color: #ECEFF4">)</span></span>
<span class="line"><span style="color: #81A1C1">else</span><span style="color: #ECEFF4">:</span></span>
<span class="line"><span style="color: #D8DEE9FF">    </span><span style="color: #88C0D0">print</span><span style="color: #ECEFF4">(</span><span style="color: #ECEFF4">'</span><span style="color: #A3BE8C">找到 categories 資料表!</span><span style="color: #ECEFF4">'</span><span style="color: #ECEFF4">)</span></span>
<span class="line"><span style="color: #D8DEE9FF">    </span><span style="color: #88C0D0">print</span><span style="color: #ECEFF4">(</span><span style="color: #ECEFF4">"</span><span style="color: #A3BE8C">刪除舊有 categories 資料表</span><span style="color: #ECEFF4">"</span><span style="color: #ECEFF4">)</span></span>
<span class="line"><span style="color: #D8DEE9FF">    cur</span><span style="color: #ECEFF4">.</span><span style="color: #88C0D0">execute</span><span style="color: #ECEFF4">(</span><span style="color: #ECEFF4">"</span><span style="color: #A3BE8C">DROP TABLE categories</span><span style="color: #ECEFF4">"</span><span style="color: #ECEFF4">)</span></span>
<span class="line"></span>
<span class="line"><span style="color: #D8DEE9FF">create_sql </span><span style="color: #81A1C1">=</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">'''</span></span>
<span class="line"><span style="color: #A3BE8C">    CREATE TABLE categories (</span></span>
<span class="line"><span style="color: #A3BE8C">        id INTEGER PRIMARY KEY AUTOINCREMENT,</span></span>
<span class="line"><span style="color: #A3BE8C">        name TEXT NOT NULL,</span></span>
<span class="line"><span style="color: #A3BE8C">        description TEXT</span></span>
<span class="line"><span style="color: #A3BE8C">    )</span></span>
<span class="line"></span>
<span class="line"><span style="color: #ECEFF4">'''</span></span>
<span class="line"><span style="color: #88C0D0">print</span><span style="color: #ECEFF4">(</span><span style="color: #ECEFF4">"</span><span style="color: #A3BE8C">建立 categories 資料表</span><span style="color: #ECEFF4">"</span><span style="color: #ECEFF4">)</span></span>
<span class="line"><span style="color: #D8DEE9FF">cur</span><span style="color: #ECEFF4">.</span><span style="color: #88C0D0">execute</span><span style="color: #ECEFF4">(</span><span style="color: #D8DEE9FF">create_sql</span><span style="color: #ECEFF4">)</span></span>
<span class="line"></span>
<span class="line"><span style="color: #88C0D0">print</span><span style="color: #ECEFF4">(</span><span style="color: #ECEFF4">"</span><span style="color: #A3BE8C">新增資料</span><span style="color: #ECEFF4">"</span><span style="color: #ECEFF4">)</span></span>
<span class="line"><span style="color: #D8DEE9FF">cur</span><span style="color: #ECEFF4">.</span><span style="color: #88C0D0">execute</span><span style="color: #ECEFF4">(</span></span>
<span class="line"><span style="color: #D8DEE9FF">    </span><span style="color: #ECEFF4">"""</span><span style="color: #A3BE8C">INSERT INTO categories (name, description) VALUES ("Python","python")</span><span style="color: #ECEFF4">"""</span><span style="color: #ECEFF4">)</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color: #D8DEE9FF">name </span><span style="color: #81A1C1">=</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">"</span><span style="color: #A3BE8C">PHP</span><span style="color: #ECEFF4">"</span></span>
<span class="line"><span style="color: #D8DEE9FF">description </span><span style="color: #81A1C1">=</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">"</span><span style="color: #A3BE8C">php</span><span style="color: #ECEFF4">"</span></span>
<span class="line"><span style="color: #D8DEE9FF">cur</span><span style="color: #ECEFF4">.</span><span style="color: #88C0D0">execute</span><span style="color: #ECEFF4">(</span><span style="color: #ECEFF4">"""</span><span style="color: #A3BE8C">INSERT INTO categories (name, description)</span></span>
<span class="line"><span style="color: #A3BE8C">              VALUES (?, ?)</span></span>
<span class="line"><span style="color: #A3BE8C">              </span><span style="color: #ECEFF4">"""</span><span style="color: #ECEFF4">,</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">(</span><span style="color: #D8DEE9FF">name</span><span style="color: #ECEFF4">,</span><span style="color: #D8DEE9FF"> description</span><span style="color: #ECEFF4">))</span></span>
<span class="line"></span>
<span class="line"><span style="color: #88C0D0">print</span><span style="color: #ECEFF4">(</span><span style="color: #ECEFF4">"</span><span style="color: #A3BE8C">列出所有資料</span><span style="color: #ECEFF4">"</span><span style="color: #ECEFF4">)</span></span>
<span class="line"><span style="color: #D8DEE9FF">statement </span><span style="color: #81A1C1">=</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">"</span><span style="color: #A3BE8C">SELECT * FROM categories</span><span style="color: #ECEFF4">"</span></span>
<span class="line"><span style="color: #D8DEE9FF">cur</span><span style="color: #ECEFF4">.</span><span style="color: #88C0D0">execute</span><span style="color: #ECEFF4">(</span><span style="color: #D8DEE9FF">statement</span><span style="color: #ECEFF4">)</span></span>
<span class="line"><span style="color: #D8DEE9FF">output </span><span style="color: #81A1C1">=</span><span style="color: #D8DEE9FF"> cur</span><span style="color: #ECEFF4">.</span><span style="color: #88C0D0">fetchall</span><span style="color: #ECEFF4">()</span></span>
<span class="line"><span style="color: #81A1C1">for</span><span style="color: #D8DEE9FF"> row </span><span style="color: #81A1C1">in</span><span style="color: #D8DEE9FF"> output</span><span style="color: #ECEFF4">:</span></span>
<span class="line"><span style="color: #D8DEE9FF">    </span><span style="color: #88C0D0">print</span><span style="color: #ECEFF4">(</span><span style="color: #D8DEE9FF">row</span><span style="color: #ECEFF4">)</span></span>
<span class="line"><span style="color: #D8DEE9FF">    </span></span>
<span class="line"><span style="color: #88C0D0">print</span><span style="color: #ECEFF4">(</span><span style="color: #ECEFF4">"</span><span style="color: #A3BE8C">列出 id 為 1 的資料</span><span style="color: #ECEFF4">"</span><span style="color: #ECEFF4">)</span></span>
<span class="line"><span style="color: #D8DEE9FF">statement </span><span style="color: #81A1C1">=</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">"</span><span style="color: #A3BE8C">SELECT * FROM categories WHERE id = ?</span><span style="color: #ECEFF4">"</span></span>
<span class="line"><span style="color: #616E88"># 參數只有一個時，記得加上逗號，否則會有錯誤</span></span>
<span class="line"><span style="color: #D8DEE9FF">cur</span><span style="color: #ECEFF4">.</span><span style="color: #88C0D0">execute</span><span style="color: #ECEFF4">(</span><span style="color: #D8DEE9FF">statement</span><span style="color: #ECEFF4">,</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">(</span><span style="color: #88C0D0">id</span><span style="color: #ECEFF4">,))</span></span>
<span class="line"><span style="color: #D8DEE9FF">result </span><span style="color: #81A1C1">=</span><span style="color: #D8DEE9FF"> cur</span><span style="color: #ECEFF4">.</span><span style="color: #88C0D0">fetchone</span><span style="color: #ECEFF4">()</span></span>
<span class="line"><span style="color: #81A1C1">if</span><span style="color: #D8DEE9FF"> result</span><span style="color: #ECEFF4">:</span></span>
<span class="line"><span style="color: #D8DEE9FF">    </span><span style="color: #88C0D0">print</span><span style="color: #ECEFF4">(</span><span style="color: #D8DEE9FF">result</span><span style="color: #ECEFF4">)</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color: #D8DEE9FF">con</span><span style="color: #ECEFF4">.</span><span style="color: #88C0D0">commit</span><span style="color: #ECEFF4">()</span></span>
<span class="line"><span style="color: #D8DEE9FF">con</span><span style="color: #ECEFF4">.</span><span style="color: #88C0D0">close</span><span style="color: #ECEFF4">()</span></span>
<span class="line"></span>

參考資料

sqlite3 常用指令及 SQL 語法
 SQLite Python: Querying Data

【Python】Sqlite3 常用程式碼範例

參考資料

Author

Comments

Write a Reply or Comment 取消回覆