Python操作SQLite資料庫的方法詳解

Python操作SQLite資料庫的方法詳解

　　1.匯入Python SQLITE資料庫模組

　　Python2.5之後，內建了SQLite3，成為了內建模組，這給我們省了安裝的功夫，只需匯入即可~

　　import sqlite3

　　2. 建立/開啟資料庫

　　在調用connect函數的時候，指定庫名稱，如果指定的資料庫存在就直接開啟這個資料庫，如果不存在就新建立一個再開啟。

　　cx = sqlite3.connect("E:/test.db")

　　也可以建立資料庫在記憶體中。

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

　　3.資料庫連接對象

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

　　commit()--事務提交

　　rollback()--交易回復

　　close()--關閉一個資料庫連接

　　cursor()--建立一個遊標

　　關於commit()，如果isolation_level隔離等級預設，那麼每次對資料庫的操作，都需要使用該命令，你也可以設定isolation_level=None，這樣就變為自動認可模式。

　　Python操作SQLite資料庫的方法詳解

　　4.使用遊標查詢資料庫

　　我們需要使用遊標對象SQL語句查詢資料庫，獲得查詢對象。 通過以下方法來定義一個遊標。

　　cu=cx.cursor()

　　遊標對象有以下的操作：

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

　　1. 建表

　　cu.execute("create table catalog (id integer primary key,pid integer,name varchar(10) UNIQUE,nickname text NULL)")

　　上面語句建立了一個叫catalog的表，它有一個主鍵id，一個pid，和一個name，name是不可以重複的，以及一個nickname預設為NULL。

　　2. 插入資料

　　請注意避免以下寫法：

　　# Never do this -- insecure 會導致注入攻擊

　　pid=200

　　c.execute("... where pid = '%s'" % pid)

　　正確的做法如下，如果t只是單個數值，也要採用t=(n,)的形式，因為元組是不可變的。

　　for t in[(0,10,'abc','Yu'),(1,20,'cba','Xu')]:

　　cx.execute("insert into catalog values (?,?,?,?)", t)

　　簡單的插入兩行資料,不過需要提醒的是,只有提交了之後,才會生效.我們使用資料庫連接對象cx來進行提交commit和復原rollback操作.

　　cx.commit()

　　3.查詢

　　cu.execute("select * from catalog")

　　要提取查詢到的資料,使用遊標的fetch函數,如:

　　In [10]: cu.fetchall()

　　Out[10]: [(0, 10, u'abc', u'Yu'), (1, 20, u'cba', u'Xu')]

　　如果我們使用cu.fetchone(),則首先返回列表中的第一項,再次使用,則返回第二項,依次下去.

　　4.修改

　　In [12]: cu.execute("update catalog set name='Boy' where id = 0")

　　In [13]: cx.commit()

　　注意,修改資料以後提交

　　5.刪除

　　cu.execute("delete from catalog where id = 1")

　　cx.commit()

　　6.使用中文

　　請先確定你的IDE或者系統預設編碼是utf-8,並且在中文前加上u

　　x=u'魚'

　　cu.execute("update catalog set name=? where id = 0",x)

　　cu.execute("select * from catalog")

　　cu.fetchall()

　　[(0, 10, u'\u9c7c', u'Yu'), (1, 20, u'cba', u'Xu')]

　　如果要顯示出中文字型，那需要依次列印出每個字串

　　In [26]: for item in cu.fetchall():

　　....: for element in item:

　　....: print element,

　　....: print

　　....:

　　0 10 魚 Yu

　　1 20 cba Xu

　　7.Row類型

　　Row提供了基於索引和基於名字大小寫敏感的方式來訪問列而幾乎沒有記憶體開銷。 原文如下：

　　sqlite3.Row provides both index-based and case-insensitive name-based access to columns with almost no memory overhead. It will probably be better than your own custom dictionary-based approach or even a db_row based solution.

　　Row對象的詳細介紹

　　class sqlite3.Row

　　A Row instance serves as a highly optimized row_factory for Connection objects. It tries to mimic a tuple in most of its features.

　　It supports mapping access by column name and index, iteration, representation, equality testing and len().

　　If two Row objects have exactly the same columns and their members are equal, they compare equal.

　　Changed in version 2.6: Added iteration and equality (hashability).

　　keys()

　　This method returns a tuple of column names. Immediately after a query, it is the first member of each tuple in Cursor.description.

　　New in version 2.6.

　　下面舉例說明

　　In [30]: cx.row_factory = sqlite3.Row

　　In [31]: c = cx.cursor()

　　In [32]: c.execute('select * from catalog')

　　Out[32]:

　　In [33]: r = c.fetchone()

　　In [34]: type(r)

　　Out[34]:

　　In [35]: r

　　Out[35]:

　　In [36]: print r

　　(0, 10, u'\u9c7c', u'Yu')

　　In [37]: len(r)

　　Out[37]: 4

　　In [39]: r[2] #使用索引查詢

　　Out[39]: u'\u9c7c'

　　In [41]: r.keys()

　　Out[41]: ['id', 'pid', 'name', 'nickname']

　　In [42]: for e in r:

　　....: print e,

　　....:

　　0 10 魚 Yu

　　使用列的關鍵詞查詢

　　In [43]: r['id']

　　Out[43]: 0

　　In [44]: r['name']

　　Out[44]: u'\u9c7c'