SQLite : Python開發環境下，使用SQLite的參數化查詢，可以避免因包含 ' 單引號(apostrophe)的字串，所引起的錯誤

1. 使用Python的 % 串接字串，很好用，很方便，但如果字串中包含單引號 (apostrophe, ASCII : 39)，又沒有預先處理好，就會造成SQL執行錯誤，例如：
   zSQL="""INSERT INTO logs
                   (logA,logB,logC,logD,logE,logF) values (
                   '%s','%s',%d,%d,%d,'%s');
           """
   SQL=zSQL%(strA,strB,strC,strD,strE,strF)
   
2. 如果直接改成參數化的語法，這樣就可以自動排除單引號 (apostrophe, ASCII : 39)所引起的錯誤。
oConn.execute("INSERT INTO logs (logA,logB,logC,logD,logE,logF) values (?,?,?,?,?,?), (strA,strB,strC,strD,strE,strF))"

1. 參考資料：https://zh.wikipedia.org/wiki/參數化查詢

Python 3 : SQLite3的存取基本步驟，及異常錯誤處理 (try ... except ... )

1. 參考資料：
• https://docs.python.org/3/library/sqlite3.html  
• https://docs.python.org/zh-cn/3/library/sqlite3.html 
• http://doc.codingdict.com/python_352/library/sqlite3.html  
• 詳解Python 資料庫的Connection、Cursor兩大物件  
2. 在Python 3 環境下，使用SQLilte3，對資料庫操作的基本步驟(一般原則)：
1. 用sqlite3.connect("資料庫檔名.副檔名") 建立資料庫連線，並將這個連線物件指定給一個連線物件變數，例如：oConn=sqlite3.connect("資料庫檔名.副檔名")
2. 建立連線物件的cursor物件，例如：cTest=oConn.cursor()  
3. 執行SQL命令，將結果以tuple資料組存放在cursor物件內。例如：cTest.execute("SQL命令")  
4. 取得目標資料集，例如：oTest=cTest.fetchall()  
5. 處理取得的資料集，例如：for aTest in oTest :
6. 關閉資料庫連線，例如：oConn.close()  
3. 對於不需要return回傳執行結果的資料庫操作，可以簡化操作步驟，如下：
1. 建立資料庫連線，例如：oConn=sqlite3.connect("資料庫檔名.副檔名")
2. 執行SQL命令，例如：oConn.execute("SQL命令")  
3. 更新資料庫，例如：oConn.commite()  
4. cursor物件是一個指標物件，cursor物件執行SQL命令後，會將結果存放在cursor物件內，並可透過fetchone, fetchmany, fetchall ... 等方法或函數來操作cursor物件內的資料。
   一個資料庫連線的使用過程，可以根據目的需求的不同，建立多個cursor物件來使用，不同名稱的cursor物件，如果不再使用可給予關閉close()。同一個cursor物件，可以透過不同的命令執行，賦予不同的資料內容。
   使用VS code 可以快速瀏覽sqlite3 cursor可以使用的方法、屬性...
   
5. 可以使用 VS Code，查看Python 有哪些處理 SQLite 錯誤例外異常的類別，例如；Error, DatabaseErrot, DataError, ProgrammingError ...，在不分開測試錯誤類別來源的情況下，可以使用預設的異常錯誤例外處理 Exception ...
   import sqlite3
   oConn=sqlite3.connect("test.db")
   zSQL="UPDATE T SET T2='5',T3='8' WHERE T1='1' "
   try:
       oConn.execute(zSQL)
   except sqlite3.DataError as d:
       print("d=",d)
   except sqlite3.DatabaseError as de:
       print("de=",de)
   except sqlite3.Error as e:
       print("e=",e)
   except Exception as ex:
       print("ex=",ex)
   oConn.commit()
   
   #de= database is locked
   
   
6. 適度地在SQLite的CRUD操作上使用try ... except ...，可以確保程式持續執行，或在執行過程排除異常、錯誤...。

SQLite3 : 欄位串接的運算子是 || (double pipe)

我一直有一個作法，如果資料內容在SQL查詢的過程中，能夠直接準備好所需要的資料項目內容，那我就會儘量在下SQL語法時，想辦法一併取得資料：
透過資料的串接，達到合併資料欄位的內容的需求，產生的新欄位，下接下來的程式取用資料，會相對較簡單。
SQLite : 欄位串接的運算子是 || (double pipe)
SQL語法範例：
SELECT (stockType||'_'||stockID||'.tw') as stockParm , *  FROM StdToNote

使用Python 處理 SQLite3 跨資料庫檔案間的資料表資料複製

1. 作法：
   建立兩個connection，分別對應來源及目標資料庫檔案
   一次讀取來源的所有資料，再逐筆寫入目標資料表內
2. 程式碼：
   import sqlite3
   def dict_factory(cursor, row):
       d = {}
       for idx, col in enumerate(cursor.description):
           d[col[0]] = row[idx]
       return d
   ##來源資料庫
   dbName="LineNotifierStock-20190731.db3"
   oConnSource=sqlite3.connect(dbName)
   ##目標資料庫
   dbName="StockNotifier.db3"
   oConnTarget=sqlite3.connect(dbName)
   ##作法：一次讀取來源的所有資料，再逐筆寫入目標資料表內
   zSQL="Select * from StdToNote
   oConnSource.row_factory = dict_factory
   cSource=oConnSource.cursor()
   cSource.execute(zSQL)
   oSource=cSource.fetchall()
   print(oSource)
   for aSource in oSource :
       zSQL="""INSERT INTO StdToNote
               (stockID,stockName,doNoteOpen,doNoteRate,
               doNotePrice,doNoteAccVol,doNoteUpDown,noteType,
               refPrice,rateHigh,rateLow,priceHigh,priceLow,memo)
               values('%s','%s','%s','%s','%s','%s','%s','%s',%f,%f,%f,%f,%f,'%s')
       """
       zSQL=zSQL%(aSource['stockID'],aSource['stockName'],aSource['isNoteOpen'], \
               aSource['isNoteRate'],aSource['isNotePrice'],aSource['isNoteAccVol'],
               aSource['isNoteUpDown'],aSource['noteType'],aSource['refPrice'], \
               aSource['rateHigh'],aSource['rateLow'],aSource['priceHigh'], \
               aSource['priceLow'],aSource['memo'])
       oConnTarget.execute(zSQL)
       oConnTarget.commit()
   oConnSource.close()
   oConnTarget.close()
   print("Data Copied Ready!!!")
   
3. 查看執行結果：
   

從政府資料開放平台(open data)，取得上市上櫃公司基本資料(CSV)，使用Python將資料寫入SQLite3資料庫

1. 政府資料開放平台 ( https://data.gov.tw/ )
• 上市公司基本資料(18419) -  https://data.gov.tw/dataset/18419  
• 上櫃股票基本資料(25036) -  https://data.gov.tw/dataset/25036  
• 實際資料提供者：證交所的公開資訊觀測站 https://mopsfin.twse.com.tw/  
• 資料格式只提供 CSV ，更新頻率→每日 
• 取得資料的連結點：
• http://mopsfin.twse.com.tw/opendata/t187ap03_L.csv  
• http://mopsfin.twse.com.tw/opendata/t187ap03_O.csv  
2. 程式碼：
   ## Table : Stocks (stockType,stockID,stockAbbr,industryType,releaseDate)
   ## stockType : tse->上市，otc->上櫃
   ## 上市公司基本資料(18419) - 政府資料開放平台 https://data.gov.tw/dataset/18419
   ## 上櫃股票基本資料(25036) - 政府資料開放平台 https://data.gov.tw/dataset/25036
   ## 主要欄位說明:
   ## 出表日期[0]、公司代號[1]、公司名稱[2]、公司簡稱[3]、外國企業註冊地國[4]、產業別[5]、住址、
   ## 營利事業統一編號、董事長、總經理、發言人、發言人職稱、代理發言人、總機電話、成立日期、上市日期、
   ## 普通股每股面額、實收資本額、私募股數、特別股、編制財務報表類型、股票過戶機構、過戶電話、過戶地址、
   ## 簽證會計師事務所、簽證會計師1、簽證會計師2、英文簡稱、英文通訊地址、傳真機號碼、電子郵件信箱、網址
   zSQL="""CREATE TABLE IF NOT EXISTS `Stocks` (
           `stockType`     VARCHAR(4)  NOT NULL,
           `stockID`       VARCHAR(8)  NOT NULL,
           `stockAbbr`     VARCHAR(30) NOT NULL,
           `industryType`  VARCHAR(30),
           `releaseDate`   VARCHAR(12),
           PRIMARY KEY (`stockID`)
   );
   """
   oConn.execute(zSQL)
   oConn.commit()
   zSQL="SELECT * FROM Stocks "
   cStocks=oConn.execute(zSQL)
   rStock=cStocks.fetchone()
   if rStock==None:
       ##寫入上市公司資料
       zCsvUrl="http://mopsfin.twse.com.tw/opendata/t187ap03_L.csv"
       oHTML=requests.get(zCsvUrl)
       oHTML.encoding='utf-8'
       oList=oHTML.text.split('\r\n')
       for i in range(1,len(oList)-1):
           aList=oList[i].replace('"','').split(',')
           zSQL="INSERT INTO Stocks (stockType,stockID,stockAbbr,industryType,releaseDate) values ('tse','%s','%s','%s','%s'); "
           zSQL=zSQL%(aList[1],aList[3],aList[5],aList[0])
           oConn.execute(zSQL)
           oConn.commit()
       ##寫入上櫃公司資料
       zCsvUrl="http://mopsfin.twse.com.tw/opendata/t187ap03_O.csv"
       oHTML=requests.get(zCsvUrl)
       oHTML.encoding='utf-8'
       oList=oHTML.text.split('\r\n')
       for i in range(1,len(oList)-1):
           aList=oList[i].replace('"','').split(',')
           zSQL="INSERT INTO Stocks (stockType,stockID,stockAbbr,industryType,releaseDate) values ('otc','%s','%s','%s','%s'); "
           zSQL=zSQL%(aList[1],aList[3],aList[5],aList[0])
           oConn.execute(zSQL)
           oConn.commit()
   
3. 透過Python寫入的資料：
   

SQL : INSERT INTO ... SELECT ... WHERE NOT EXISTS ( SELECT ...) 。如果資料不存在就插入一筆資料表。

目標：如果查詢的資料不存在，就插入一筆資料。
想法：如果按照CREATE TABLE IF NOT EXISTS的思考方式，會想要使用 IF NOT EXISTS的作法，以SELECT查詢資料作為條件判斷，可以用 WHERE NOT EXISTS的作法。當然還有其他的方法可以達到需求。

SQL參考語法：

INSERT INTO Config (cfgID, cfgDesc, srhTLD, srhNUM, srhSTOP, srhPAUSE, memo)
SELECT '0000','Inserted by program','com',5,5,120,''
WHERE NOT EXIST ( SELECT 1 FROM Config WHERE cfgID='0000' )

用SQLiteStudio查詢執行結果：

SQL / SQLite3 : CREATE TABLE IF NOT EXISTS 。如果資料表不存在就新增建立這個資料表。

目標：如果資料表不存在就新增建立這個資料表。
SQL參考語法：

在SQLite，也可以使用VARCHAR

CREATE TABLE IF NOT EXISTS `Config` (

    `cfgID`        VARCHAR(04) DEFAULT('0000') NOT NULL,

    `cfgDesc`    VARCHAR(50) NOT NULL,

    `...` ....,

    PRIMARY KEY(`cfgID`)

);

用SQLiteStudio查詢執行結果：

SQLite3的管理工具程式：sqlite_tools, SQLiteStudio, SQLiteBrowser

SQLite可以經由程式(Python, Java, C#, C, C++ ...)操作執行來產生檔案資料庫，操作使用資料庫內的資料表、資料...等，但一定會遇到，需要直接先建立、修改、刪除、修改資料庫、資料表資料欄位、資料內容...的狀況，這時候有個工具程式可以用的話，可以避免很多麻煩。

1. SQLite官網的command-line 管理工具，不需安裝
   https://www.sqlite.org/download.html
   sqlite-tools-win32-x86-3290000.zip (目前的版本 3.29.0)，包含sqlite3.exe, sqldiff.exe, sqlite3_analyzer.exe，其中sqlite3.exe，使用.help指令，可以查看相關操作指令
   
2. SQLiteStudio圖形介面、功能強大，portable免安裝
   網址：https://sqlitestudio.pl/index.rvt
   https://sqlitestudio.pl/index.rvt?act=download
   https://sqlitestudio.pl/files/sqlitestudio3/complete/win32/SQLiteStudio-3.2.1.zip
   
   可以透過圖形介面的程式管理(新增、修改、刪除、查詢)Structure / Data / Constraints / Indexes / Triggers / DDL
3. DB Browser for SQLite
   官網：https://sqlitebrowser.org/
   下載：https://sqlitebrowser.org/dl/
   

在樹莓派 RaspBerry Pi 下，用Python當開發工具，用SQLite3儲存資料，用SQLiteBrowser協助資料管理

工作環境：

• RaspBerry Pi 3 Mode B+
• 作業系統：版本代號為 Buster (Version:June 2019 / Release date:2019-06-20 / Kernel version:4.19)

在Python測試連接SQLite3的使用：

• Buster版本的Python環境，SQLite3已經是Ready的狀態
• 用以下的程式測試，在Python使用SQLite是OK的
  使用Python sqlite3模組建立資料庫連線，如果所指定的資料庫不存在，Python便會自動建立產生該資料庫檔案
  
  

在樹莓派安裝sqlitebrowser

• sudo apt-get update
• sudo apt-get upgrade
• sudo apt-get install sqlite3
• sudo apt-get install sqlitebrowser
  
  

參考資料：

• Powerful open source SQLite manager for Raspberry Pi  

使用SQL語法將資料轉換為cross table交叉資料表的形式

本範例要使用一個SQLite檔案，在SQLite Database Browser下，來進行資料的轉換示範。
SQLite Database Browser 1.3 Portable免安裝版下載的參考網址：http://files.bod.idv.tw/development/SQLiteDatabaseBrowser
在SQLite Database Browser下，可以建立SQLite資料庫檔檔、建立編輯刪除資料表、新增編輯刪除查詢資料、執行SQL指令...，一個學習SQL指令方常方便的免費小程式！
下載 MakingCrossTable.zip 範例檔案：MakingCrossTable.zip (解壓縮後檔案名稱為MakingCrossTable.db3，可以使用SQLite Database Browser直接開啟這個檔案)

在MakingCrossTable.db3中，包含一個資料表Sign，資料表中的資料欄位，請參閱圖例。
SheetNo：表單號碼，SlotNo：關卡，SignName：簽核人，ReceiveTime：收件時間，SignTime：簽核時間。

在Sign資料表中，包含下列資料：有兩個表單號碼102030301及102030302，每個表單都有三個關卡100/200/300，每筆資料中紀錄了，該表單在某一關卡的簽核人、收件時間、簽核時間。

接下來要透過SQL statements將Sign資料表中的這兩個表單單號的六個關卡簽核紀錄，彙整每個表單所有簽核關卡簽核紀錄，使成為一筆資料。

可以將以下的SQL指令，複製到Execute SQL分頁的SQL string方塊中，然後按下Execute query按鈕，即可檢視彙整後的結果。

select SheetNo,
max(case SlotNo when '100' then SignName else '' end) as U1,
max(case SlotNo when '100' then ReceiveTime else '' end) as R1,
max(case SlotNo when '100' then SignTime else '' end) as S1,
max(case SlotNo when '200' then SignName else '' end) as U2,
max(case SlotNo when '200' then ReceiveTime else '' end) as R2,
max(case SlotNo when '200' then SignTime else '' end) as S2,
max(case SlotNo when '300' then SignName else '' end) as U3,
max(case SlotNo when '300' then ReceiveTime else '' end) as R3,
max(case SlotNo when '300' then SignTime else '' end) as S3
from Sign
group by SheetNo
order by SheetNo


簡單幾行的SQL指令，就可以達到資料彙整的效果，太方便了！

使用VB.net列出SQLite資料的範例(select)

********************************************

    Private Sub ListData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListData.Click
        Try
            Dim zSQLFile As String = "Data Source=C:\SQLite檔案存放資料夾\NewDB3.db3"
            '連接資料庫
            Dim oConn As New SQLiteConnection(zSQLFile)
            oConn.Open()
            '執行SQL指令
            Dim zSQL As String = "SELECT * from test "
            Dim oCmd As SQLiteCommand = New SQLiteCommand(zSQL, oConn)
            '將執行結果放到DataReader
            Dim oDR As SQLiteDataReader = oCmd.ExecuteReader()
            '將Dataview的資料顯示在ListBox1上
            ListBox1.Items.Clear()
            While oDR.Read()
                'ListBox1.Items.Add(String.Format("{0},{1},{2}", oDR(0), oDR(1), oDR(2)))
                ListBox1.Items.Add(String.Format("{0},{1},{2}", oDR("oid"), oDR("word"), oDR("denotation")))
            End While
            lblMsg.Text = "Data Added to ListBox ready!!!"
            oCmd.Dispose()
            oConn.Close()
        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try
    End Sub

使用VB.net將資料插入SQLite3資料表的範例(insert into)

************************************************

    Private Sub InsertData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles InsertData.Click
        Dim aWord(,) = {{"boy", "男孩"}, {"girl", "女孩"}, {"man", "男人"}, {"woman", "女人"}, {"student", "學生"}}
        Try
            Dim zSQLFile As String = "Data Source=C:\SQLite檔案存放資料夾\NewDB3.db3"
            '連接資料庫
            Dim oConn As New SQLiteConnection(zSQLFile)
            oConn.Open()
            '執行SQL指令
            Dim zSQL As String = vbNullString
            Dim zWord As String = vbNullString
            Dim zDeno As String = vbNullString
            Dim oCmd As SQLiteCommand = Nothing
            For i = 0 To 4
                zWord = aWord(i, 0)
                zDeno = aWord(i, 1)
                zSQL = "INSERT INTO test (word,denotation) VALUES ('" & zWord & "','" & zDeno & "')"
                oCmd = New SQLiteCommand(zSQL, oConn)
                oCmd.ExecuteNonQuery()
            Next
            lblMsg.Text = "Data inserted ready!!!"
            oCmd.Dispose()
            oConn.Close()
        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try
    End Sub

使用VB.net建立SQLite3資料表的範例(create table)

1. NewDB.db3是一個已存在的SQLite資料庫檔案
2. 要在NewDB.db3上新增一個test資料表，包含三個欄位oid, word, denotation，資料規格如程式內容。

************************************************

Imports System.Data.SQLite

Public Class Form2
    Private Sub CreateTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateTable.Click
        Try
            Dim zSQLFile As String = "Data Source=C:\SQLite檔案存放資料夾\NewDB3.db3"
            '連接資料庫
            Dim oConn As New SQLiteConnection(zSQLFile)
            oConn.Open()
            '執行SQL指令
            Dim zSQL As String = "CREATE TABLE test(oid INTEGER PRIMARY KEY AUTOINCREMENT, word VARCHAR(50), denotation VARCHAR(255));"
            Dim oCmd As SQLiteCommand = New SQLiteCommand(zSQL, oConn)
            oCmd.ExecuteNonQuery()
            oCmd.Dispose()
            oConn.Close()
            lblMsg.Text = "Table created ready!!!"
        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try
    End Sub
End Class

在Visual Studio 2010 express中加入System.Data.SQLite

System.Data.SQLite : An open source ADO.NET provider for the SQLite database engine
參考網址：

• http://sqlite.phxsoftware.com/
• http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki
• http://www.kirupa.com/net/sqllite_vb_pg1.htm
• http://www.kirupa.com/net/sqllite_vb_pg2.htm

安裝檔下載網址：http://sourceforge.net/project/showfiles.php?group_id=132486

安裝步驟：

在以上的安裝過程畫面中，可以清楚的看到，這個安裝適用於ADO.net 2.0/3.5，如果是使用.net framework 4.0的環境，會出現以下錯誤訊。可以在專案的web.confing或app.config檔案中，增加或修改<startup>區段，就可以避開這個問題決了！

以新增項目app.config為例：
點選專案名稱按一下滑鼠右鍵，選擇『加入(D)』→『新增項目(W)』，在『加入新項目』對話方塊中，選擇『應用程式組態檔』，預設的檔名稱為“app.config”，按一下“確定”。
開啟app.config在<configuration>節點下，加入以下內容：
<startup useLegacyV2RuntimeActivationPolicy="true">
  <supportedRuntime version="v4.0"/>
</startup>
以上修正的參考資料：

• [C#] 解決SQLite不能在.Net Framework 4下執行的問題
• [C#] 解決SQLite不能在.Net Framework 4下執行的問題(2)

建立專案時，必須加入參考：
C:\Program Files\SQLite.NET\bin\System.Data.SQLite.dll

程式碼中要加入： Imports System.Data.SQLite

加入您所需要的程式碼...

使用php PDO的存取方式，選取SQLite3資料庫的資料內容

以下是一個使用PDO取用SQLite資料庫內容的範例：


<?php
//指定SQLite的檔名
$zDsn ="sqlite:e1000.db3";
//使用PDO建立資料庫連線
//pdo(資料來源字串,帳號,密碼);
$oConn = new pdo($zDsn,"","");
//選取資料的SQL statement
$zSQL ="select oid,word,denotation from elementary where oid<=10";
//執行查詢作業
$oRS = $oConn->query($zSQL);
//取得查詢結果，PDO::FETCH_ASSOC 傳回下一筆資料的欄位名及值
$oRows = $oRS->fetchAll(PDO::FETCH_ASSOC);
//依序 列出從SQLite取得的資料
for ($i=0;$i<10;$i++) {
    $zOid = $oRows[$i]['oid'];
    $zWord = $oRows[$i]['word'];
    $zDennotation = $oRows[$i]['denotation'];
    echo "$zOid --  $zWord --  $zDennotation <br>";
}
?>

使用php PDO的存取方式，清空SQLite3資料庫的資料內容

使用phpMyAdmin操作MySQL資料庫，有一個很好用但要小心用的『清空』功能(Truncate)。

但在SQLite Database Browser上操作SQLite資料庫檔案，要一次清空資料表中的資料，似乎不是這麼方便？

在沒有找到更好的方法前，先用php的PDO存取方式，寫個迴圈，用delete的方式，逐筆清空資料表的紀錄。雖然不是個好方法，但是個可以達到目的的方法，紀錄下來當個範例用。

<?php
//指定SQLite的來源，資料庫檔案empty.db3和程式碼放在同一目錄下
$zDsn = 'sqlite:empty.db3';
//共有1000筆資料
$nEnd = 1001;
//根據elementary資料表的oid欄位，逐筆讀取資料庫
  for ($i=1;$i<$nEnd;$i++) {
    //使用PDO連接資料庫    
    $oConn = new pdo($zDsn,"","");
    $zSQL = "delete from elementary where oid=".$i;
    $oResult = $oConn->exec($zSQL);
    if (!$oResult){
      echo "資料刪除發生錯誤！<br>";
      echo $zSQL;
      exit();
    }
    $oConn = null;
  }
?>

SQLite3 Database Browser v1.3 免安裝版(Portable)

使用SQLite不需要額外再安裝資料庫伺服器(引擎)，又可以方便的使用SQL語法的特性來存取資料，是個非常方便的檔案型資料庫。
SQLite Database Browser v1.3適用於SQLite v3，具有下列的特性：(節錄自SQLite Database Browser官方網站)

• Create and compact database files
• Create, define, modify and delete tables
• Create, define and delete indexes
• Browse, edit, add and delete records
• Search records
• Import and export records as text
• Import and export tables from/to CSV files
• Import and export databases from/to SQL dump files
• Issue SQL queries and inspect the results
• Examine a log of all SQL commands issued by the application

SQLite官方網站：http://sqlite.sourceforge.net/
SQLite Database Browser官方網站：http://sqlitebrowser.sourceforge.net/
國網中心下載連結：SQLite Database Browser v1.3 Portable 免安裝版