Python中sqlite學習教程

標籤：pysqlite

       Python SQLITE資料庫是一款非常小巧的嵌入式開來源資料庫軟體，沒有獨立的維護進程，所有的維護都來自於程式本身。它使用一個檔案儲存體整個資料庫，操作十分方便。

       以下是重要的 sqlite3 模組程式，可以滿足您在 Python 程式中使用 SQLite 資料庫的需求。如果您需要瞭解更多細節，請查看 Python sqlite3 模組的官方文檔。

序號	API & 描述
1	sqlite3.connect(database [,timeout ,other optional arguments]) 該 API 開啟一個到 SQLite 資料庫檔案 database 的連結。您可以使用 ":memory:" 來在 RAM 中開啟一個到 database 的資料庫連接，而不是在磁碟上開啟。如果資料庫成功開啟，則返回一個連線物件。 當一個資料庫被多個串連訪問，且其中一個修改了資料庫，此時 SQLite 資料庫被鎖定，直到事務提交。timeout 參數表示串連等待鎖定的期間，直到發生異常中斷連線。timeout 參數預設是 5.0（5 秒）。 如果給定的資料庫名稱 filename 不存在，則該調用將建立一個資料庫。如果您不想在目前的目錄中建立資料庫，那麼您可以指定帶有路徑的檔案名稱，這樣您就能在任意地方建立資料庫。
2	connection.cursor([cursorClass]) 該常式建立一個 cursor，將在 Python 資料庫編程中用到。該方法接受一個單一的可選的參數 cursorClass。如果提供了該參數，則它必須是一個擴充自 sqlite3.Cursor 的自訂的 cursor 類。
3	cursor.execute(sql [, optional parameters]) 該常式執行一個 SQL 陳述式。該 SQL 陳述式可以被參數化（即使用預留位置代替 SQL 文本）。sqlite3 模組支援兩種類型的預留位置：問好和命名預留位置（命名樣式）。 例如：cursor.execute("insert into people values (?, ?)", (who, age))
4	connection.execute(sql [, optional parameters]) 該常式是上面執行的由游標（cursor）對象提供的方法的捷徑，它通過調用游標（cursor）方法建立了一個中間的游標對象，然後通過給定的參數調用游標的 execute 方法。
5	cursor.executemany(sql, seq_of_parameters) 該常式對 seq_of_parameters 中的所有參數或映射執行一個 SQL 命令。
6	connection.executemany(sql[, parameters]) 該常式是一個由調用游標（cursor）方法建立的中間的游標對象的捷徑，然後通過給定的參數調用游標的 executemany 方法。
7	cursor.executescript(sql_script) 該常式一旦接收到指令碼，會執行多個 SQL 陳述式。它首先執行 COMMIT 語句，然後執行作為參數傳入的 SQL 指令碼。所有的 SQL 陳述式應該用分號（;）分隔。
8	connection.executescript(sql_script) 該常式是一個由調用游標（cursor）方法建立的中間的游標對象的捷徑，然後通過給定的參數調用游標的 executescript 方法。
9	connection.total_changes() 該常式返回自資料庫連接開啟以來被修改、插入或刪除的資料庫總行數。
10	connection.commit() 該方法提交當前的食物。如果您未調用該方法，那麼自您上一次調用 commit() 以來所做的任何動作對其他資料庫連接來說是不可見的。
11	connection.rollback() 該方法復原自上一次調用 commit() 以來對資料庫所做的更改。
12	connection.close() 該方法關閉資料庫連接。請注意，這不會自動調用 commit()。如果您之前未調用 commit() 方法，就直接關閉資料庫連接，您所做的所有更改將全部丟失！
13	cursor.fetchone() 該方法擷取查詢結果集中的下一行，返回一個單一的序列，當沒有更多可用的資料時，則返回 None。
14	cursor.fetchmany([size=cursor.arraysize]) 該方法擷取查詢結果集中的下一行組，返回一個列表。當沒有更多的可用的行時，則返回一個空的列表。該方法嘗試擷取由 size 參數指定的儘可能多的行。
15	cursor.fetchall() 該常式擷取查詢結果集中所有（剩餘）的行，返回一個列表。當沒有可用的行時，則返回一個空的列表。

        Python 的資料庫模組有統一的介面標準，所以資料庫操作都有統一的模式，基本上都是下面幾步（假設資料庫模組名為db）：

        1）用db.connect建立資料庫連接，假設連線物件為conn
        2）如果該資料庫操作不需要返回結果，就直接用conn.execute查詢，根據資料庫交易隔離等級的不同，可能修改資料庫需要conn.commit
        3）如果需要返回查詢結果則用conn.cursor建立遊標對象cur, 通過cur.execute查詢資料庫，用cur.fetchall/cur.fetchone/cur.fetchmany返回查詢結果。根據資料庫事 務隔離等級的不同，可能修改資料庫需要conn.commit
        4）關閉cur, conn

        下面我們以上面的介紹的步驟來介紹sqlite的使用。

        1）匯入sqlite3模組，如：

>>> import sqlite3

        2）建立資料庫/開啟資料庫

        建立或開啟資料，如下：

>>> import sqlite3 as sqlite>>> conn = sqlite.connect("D:/test.db")

上面的命令開啟D:/test.db的資料庫檔案，如果指定的資料庫存在就直接開啟這個資料庫，如果不存在就新建立一個再開啟，並返回資料庫連接對象conn，該對象主要有以下操作：

• commit(): 事務提交 
• rollback(): 交易回復 
  
• close(): 關閉一個資料庫連接 
  
• cursor(): 建立一個遊標 
  

connect的完整形式如：

connect(database[, timeout, isolation_level, detect_types, factory])

       其中，commit()方法用於提交事務，rollback()方法用於復原至上次調用commit()方法的地方。可以通過Connection.isolation_level定義交易隔離等級，當該屬性設定成None時，它會自動認可事務，不需要顯式地調用commit()方法。

        除了直接指定資料庫檔案之外，還有一種方法是在記憶體中建立資料庫。方法是將":memory:"作為參數傳遞給sqlite.connect()函數：

conn = sqlite3.connect(":memory:")

      如果不需要返回結果，則可以直接使用conn.execute( )進行資料操作，如下，建立一個資料庫表user：

conn.execute("create table user(uid int primary key not null, name text not null, password text not null, age int)")

      如果需要返回結果，則繼續看下面的遊標對象。

      3）建立遊標對象

>>> cur = conn.cursor()

       遊標對象有以下的操作：

• execute()--執行sql語句
• executemany--執行多條sql語句   
• close()--關閉遊標  
• fetchone()--從結果中取一條記錄，並將遊標指向下一條記錄   
• fetchmany()--從結果中取多條記錄   
• fetchall()--從結果中取出所有記錄   

       如進行插入操作：

>>> cur.execute("insert into user(uid,name, password,age)values(1,'John','123',12)")<sqlite3.Cursor object at 0x02A8A0A0>>>> cur.execute("insert into user(uid,name, password,age)values(2,'Kate','123',12)")

   現在查詢看下剛才插入的資料：

>>> cur.execute("select* from user")<sqlite3.Cursor object at 0x02A8A0A0>>>> print(cur.fetchone())(1, u'John', u'123', 12)>>> print(cur.fetchall())[(2, u'Kate', u'123', 12)]>>> print(cur.fetchall())[]>>> cur.execute("select* from user")<sqlite3.Cursor object at 0x02A8A0A0>>>> print(cur.fetchall())[(1, u'John', u'123', 12), (2, u'Kate', u'123', 12)]>>> 

      從上面的代碼可以看到，採用fetchone( )每次取出一條資料，並且指向下一個資料，而fetchall( )取出所有的資料，並指向末尾。當然，還有修改操作：

>>> cur.execute("update user set age =15 where uid =1")<sqlite3.Cursor object at 0x02A8A0A0>>>> cur.execute("select* from user where uid =1")<sqlite3.Cursor object at 0x02A8A0A0>>>> print(cur.fetchall())[(1, u'John', u'123', 15)]>>> 

刪除操作：

>>> cur.execute("delete from user where uid =1")<sqlite3.Cursor object at 0x02A8A0A0>>>> 

      除了上面顯示的cur=conn.curson()建立右邊對象對象外，其實可以直接conn.ececute( )返回的遊標對象，如:

>>> cur = conn.execute("select* from user")>>> print(cur.fetchall())[(1, u'test', u'124', 16)]>>> 

       遊標對象取多多條記錄fetchmany( )：

>>> cur = conn.execute("select* from user")>>> print(cur.fetchmany(3))[(1, u'test', u'124', 16), (2, u'test2', u'124', 18), (3, u'test3', u'124', 20)]>>> 

       下面，我們將Python中的sqlite操作封裝成一個類，如下：

class sqlitehelper:    def __init__(self, db):        self.db = db        try:            self.conn = sqlite.connect(db)            self.cur = self.conn.cursor()        except sqlite.Error,e:            print("connect "+db+" failed:"+e.reason)            return              def __del__(self):        self.conn.close()    def execute(self,sql):        self.cur.execute(sql)        return self.cur    def select(self, sql):        self.cur.execute(sql)        return self.cur    def insert(self, sql):        self.cur.execute(sql)        return self.cur    def delete(self, sql):        self.cur.execute(sql)        return self.cur    def update(self, sql):        self.cur.execute(sql)        return self.cur

參考資料：

1、python之sqlite3使用詳解  

2、sqlite3 — DB-API 2.0 interface for SQLite databases

3、Python sqlite3 模組 API

Python中sqlite學習教程