最後更新日期：2025 年 02 月 21 日
這需要用到 sqlite3 中的一個特殊資料表 sqlite_master。
sqlite_master 的結構如下：
<span class="line"><span style="color: #D8DEE9FF">CREATE TABLE </span><span style="color: #88C0D0">sqlite_master</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">(</span></span>
<span class="line"><span style="color: #D8DEE9FF"> </span><span style="color: #88C0D0">type</span><span style="color: #D8DEE9FF"> TEXT</span><span style="color: #ECEFF4">,</span></span>
<span class="line"><span style="color: #D8DEE9FF"> name TEXT</span><span style="color: #ECEFF4">,</span></span>
<span class="line"><span style="color: #D8DEE9FF"> tbl_name TEXT</span><span style="color: #ECEFF4">,</span></span>
<span class="line"><span style="color: #D8DEE9FF"> rootpage INTEGER</span><span style="color: #ECEFF4">,</span></span>
<span class="line"><span style="color: #D8DEE9FF"> sql TEXT</span></span>
<span class="line"><span style="color: #ECEFF4">)</span><span style="color: #D8DEE9">;</span></span>type 的值恆為 table，所以如果我們要查詢是否有 categories 這個資料表，就可以用以下 SQL 指令完成。
<span class="line"><span style="color: #D8DEE9FF">SELECT </span><span style="color: #81A1C1">*</span><span style="color: #D8DEE9FF"> FROM sqlite_master WHERE </span><span style="color: #88C0D0">type</span><span style="color: #D8DEE9FF"> </span><span style="color: #81A1C1">=</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">'</span><span style="color: #A3BE8C">table</span><span style="color: #ECEFF4">'</span><span style="color: #D8DEE9FF"> AND name </span><span style="color: #81A1C1">=</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">"</span><span style="color: #A3BE8C">categories</span><span style="color: #ECEFF4">"</span></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: #D8DEE9FF">conn </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">test.db</span><span style="color: #ECEFF4">"</span><span style="color: #ECEFF4">)</span></span>
<span class="line"><span style="color: #D8DEE9FF">cursor </span><span style="color: #81A1C1">=</span><span style="color: #D8DEE9FF"> conn</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: #D8DEE9FF">table_name </span><span style="color: #81A1C1">=</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">'</span><span style="color: #A3BE8C">categories</span><span style="color: #ECEFF4">'</span></span>
<span class="line"></span>
<span class="line"><span style="color: #616E88"># 資料表尚未建立時的檢查</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">== Check before create table ==</span><span style="color: #ECEFF4">"</span><span style="color: #ECEFF4">)</span></span>
<span class="line"><span style="color: #D8DEE9FF">query_sql </span><span style="color: #81A1C1">=</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">"</span><span style="color: #A3BE8C">SELECT * FROM sqlite_master WHERE type = 'table' AND name = ?</span><span style="color: #ECEFF4">"</span></span>
<span class="line"><span style="color: #D8DEE9FF">listOfTable </span><span style="color: #81A1C1">=</span><span style="color: #D8DEE9FF"> cursor</span><span style="color: #ECEFF4">.</span><span style="color: #88C0D0">execute</span><span style="color: #ECEFF4">(</span><span style="color: #D8DEE9FF">query_sql</span><span style="color: #ECEFF4">,</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">(</span><span style="color: #D8DEE9FF">table_name</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">if</span><span style="color: #D8DEE9FF"> listOfTable </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">Table </span><span style="color: #EBCB8B">%s</span><span style="color: #A3BE8C"> Not exists</span><span style="color: #EBCB8B">\n</span><span style="color: #ECEFF4">"</span><span style="color: #D8DEE9FF"> </span><span style="color: #81A1C1">%</span><span style="color: #D8DEE9FF"> table_name</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">Table </span><span style="color: #EBCB8B">%s</span><span style="color: #A3BE8C"> exists</span><span style="color: #EBCB8B">\n</span><span style="color: #ECEFF4">"</span><span style="color: #D8DEE9FF"> </span><span style="color: #81A1C1">%</span><span style="color: #D8DEE9FF"> table_name</span><span style="color: #ECEFF4">)</span></span>
<span class="line"></span>
<span class="line"><span style="color: #616E88"># 如果資料表不存在，就建立資料表</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">== Create table ==</span><span style="color: #ECEFF4">"</span><span style="color: #ECEFF4">)</span></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 style="color: #A3BE8C">CREATE TABLE IF NOT EXISTS categories (</span></span>
<span class="line"><span style="color: #A3BE8C"> id integer</span></span>
<span class="line"><span style="color: #A3BE8C"> name text)</span><span style="color: #ECEFF4">'''</span></span>
<span class="line"><span style="color: #D8DEE9FF">cursor</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: #616E88"># 資料表建立後的檢查</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">== Check after create table ==</span><span style="color: #ECEFF4">"</span><span style="color: #ECEFF4">)</span></span>
<span class="line"><span style="color: #D8DEE9FF">query_sql </span><span style="color: #81A1C1">=</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">"</span><span style="color: #A3BE8C">SELECT * FROM sqlite_master WHERE type = 'table' AND name = ?</span><span style="color: #ECEFF4">"</span></span>
<span class="line"><span style="color: #D8DEE9FF">listOfTable </span><span style="color: #81A1C1">=</span><span style="color: #D8DEE9FF"> cursor</span><span style="color: #ECEFF4">.</span><span style="color: #88C0D0">execute</span><span style="color: #ECEFF4">(</span><span style="color: #D8DEE9FF">query_sql</span><span style="color: #ECEFF4">,</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">(</span><span style="color: #D8DEE9FF">table_name</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">if</span><span style="color: #D8DEE9FF"> listOfTable </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">Table </span><span style="color: #EBCB8B">%s</span><span style="color: #A3BE8C"> Not exists</span><span style="color: #ECEFF4">"</span><span style="color: #D8DEE9FF"> </span><span style="color: #81A1C1">%</span><span style="color: #D8DEE9FF"> table_name</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">Table </span><span style="color: #EBCB8B">%s</span><span style="color: #A3BE8C"> exists</span><span style="color: #ECEFF4">"</span><span style="color: #D8DEE9FF"> </span><span style="color: #81A1C1">%</span><span style="color: #D8DEE9FF"> table_name</span><span style="color: #ECEFF4">)</span></span>
<span class="line"></span>
<span class="line"><span style="color: #D8DEE9FF">conn</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">cursor</span><span style="color: #ECEFF4">.</span><span style="color: #88C0D0">close</span><span style="color: #ECEFF4">()</span></span>
<span class="line"><span style="color: #D8DEE9FF">conn</span><span style="color: #ECEFF4">.</span><span style="color: #88C0D0">close</span><span style="color: #ECEFF4">()</span></span>
Comments