python之sqlite3使用詳解

Python SQLITE資料庫是一款非常小巧的嵌入式開來源資料庫軟體，也就是說沒有獨立的維護進程，所有的維護都來自於程式本身。它使用一個檔案儲存體整個資料庫，操作十分方便。它的最大優點是使用方便，功能相比於其它大型資料庫來說，確實有些差距。但是效能表現上，SQLITE並不遜色。麻雀雖小，五髒俱全， sqlite 實現了多數 sql-92 的標準，比如說 transaction 、 trigger 和複雜的查詢等。

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

下面讓我們一步步走進Python中的SQLite吧。

一，Python SQLITE資料庫匯入模組：

import sqlite3

二，建立資料庫/開啟資料庫：

cx = sqlite3.connect("E:/test.db") 其實我們不需要顯式的建立一個sqlite資料庫，在調用connect函數的時候，指定庫名稱，如果指定的資料庫存在就直接開啟這個資料庫，如果不存在就新建立一個再開啟。這一點應用很好理解。

三，資料庫連接對象：

開啟資料庫時返回的對象cx就是一個資料庫連接對象，它可以有以下操作：

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

四，Python SQLITE資料庫遊標的使用：

遊標提供了一種對從表中檢索出的資料進行操作的靈活手段，就本質而言，遊標實際上是一種能從包括多條資料記錄的結果集中每次提取一條記錄的機制。遊標總是與一條SQL  選擇語句相關聯。因為遊標由結果集（可以是零條、一條或由相關的選擇語句檢索出的多條記錄）和結果集中指向特定記錄的遊標位置組成。當決定對結果集進行處理時，必須聲明一個指向該結果集的遊標。如果曾經用 C 語言寫過對檔案進行處理的程式，那麼遊標就像您開啟檔案所得到的檔案控制代碼一樣，只要檔案開啟成功， 該檔案控制代碼就可代表該檔案。對於遊標而言，其道理是相同的。可見遊標能夠實現按與傳統程式讀取一般檔案類似的方式處理來自基礎資料表的結果集，從而把表中資料以一般檔案的形式呈現給程式。
        我們知道關聯式資料庫管理系統實質是面向集合的，在Sqlite中並沒有一種描述表中單一記錄的表達形式，除非使用where  子句來限制只有一條記錄被選中。因此我們必須藉助於遊標來進行面向單條記錄的資料處理。由此可見，遊標允許應用程式對查詢語句select  返回的行結果集中每一行進行相同或不同的操作，而不是一次對整個結果集進行同一種操作；它還提供對基於遊標位置而對錶中資料進行刪除或更新的能力；正是遊標把作為面向集合的資料庫管理系統和面向行的程式設計兩者聯絡起來，使兩個資料處理方式能夠進行溝通。

下面著重闡述cursor遊標的使用。其實，所有sql語句的執行都要在遊標對象下進行。

首先，定義一個遊標：

cu = cx.cursor() 這樣定義了一個遊標。遊標對象有以下的操作：

execute()--執行sql語句   executemany--執行多條sql語句   close()--關閉遊標   fetchone()--從結果中取一條記錄，並將遊標指向下一條記錄   fetchmany()--從結果中取多條記錄   fetchall()--從結果中取出所有記錄   scroll()--遊標滾動 下面就使用Python SQLITE資料庫中遊標對我們上面建立的資料庫作一些操作吧：

1,建表：
cu.execute('create table catalog (id integer primary key,pid integer,name varchar(10) UNIQUE）') 上面語句建立了一個叫catalog的表，它有一個主鍵id，一個pid，和一個name，name是不可以重複的。

2,插入資料:
cu.execute("insert into catalog values(0, 0, 'name1')")  cu.execute("insert into catalog values(1, 0, 'hello')") 簡單的插入兩行資料,不過需要提醒的是,只有提交了之後,才會生效.我們使用資料庫連接對象cx來進行提交commit和復原rollback操作.
cx.commit()

3,查詢:
cu.execute("select * from catalog") 要提取查詢到的資料,使用遊標的fetch***函數,如:
print cu.fetchall() 返回結果如下:
[(0, 0, u'name1'), (1, 0, u'hello')] 如果我們使用cu.fetchone(),則首先返回列表中的第一項,再次使用,則返回第二項,依次下去.

4,修改:
cu.execute("update catalog set name='name2' where id = 0") 

cx.commit() 注意,修改資料以後提交

5,刪除:
cu.execute("delete from catalog where id = 1")  cx.commit() 以上簡單的操作反應的Python SQLITE資料庫操作的基本要點,這裡點到為止.然後,SQLite的強大,並不僅限於此,其對SQL進階特性的支援及其小巧靈活的特點,使得SQLite在眾多領域受到開發人員的青睞.

舉例：

 Exemplary exemplar 1

import sqlite3

con = sqlite3.connect('d:/mydatabase.db3')
cur = con.cursor()
cur.execute('CREATE TABLE foo (o_id INTEGER PRIMARY KEY, fruit VARCHAR(20), veges VARCHAR(30))')
con.commit()
cur.execute('INSERT INTO foo (o_id, fruit, veges) VALUES(NULL, "apple", "broccoli")')
con.commit()
print cur.lastrowid

cur.execute('SELECT * FROM foo')
print cur.fetchall()

  Exemplary exemplar 2

#-*- encoding:gb2312 -*-
import sqlite3

conn = sqlite3.connect("D:/aaa.db")
conn.isolation_level = None #這個就是交易隔離等級，預設是需要自己commit才能修改資料庫，置為None則自動每次修改都提交,否則為""
# 下面就是建立一個表
conn.execute("create table if not exists t1(id integer primary key autoincrement, name varchar(128), info varchar(128))")
# 插入資料
conn.execute("insert into t1(name,info) values ('zhaowei', 'only a test')")
# 如果隔離等級不是自動認可就需要手動執行commit
conn.commit()
# 擷取到遊標對象
cur = conn.cursor()
# 用遊標來查詢就可以擷取到結果
cur.execute("select * from t1")
# 擷取所有結果
res = cur.fetchall()
print 'row:', cur.rowcount
# cur.description是對這個表結構的描述
print 'desc', cur.description
# 用fetchall返回的結果是一個二維的列表
for line in res:
    for f in line:
        print f,
    print
print '-'*60

cur.execute("select * from t1")
# 這次查詢後只取一個結果，就是一維列表
res = cur.fetchone()
print 'row:', cur.rowcount
for f in res:
    print f,
print
# 再取一行
res = cur.fetchone()
print 'row:', cur.rowcount
for f in res:
    print f,
print
print '-'*60

cur.close()
conn.close()