python基礎教程總結12——資料庫

標籤：

1. Python 資料庫 API

　　很多支援SQL標準的資料庫在Python中都有對應的用戶端模組。為了在提供相同功能(基本相同)的不同模組之間進行切換(相容)，Python 規定了一個標準的 DB API。目前API最新版本時 2.0，

1.1 全域變數

　　任何支援2.0版本的DB API的資料庫模組都定義了3個描述模組特性的全域變數：

apilevel            所使用的Python DB API版本threadsafety        模組的安全執行緒層級       paramstyle          在SQL查詢中使用的參數風格

　1）apilevel 是個字串常量，提供正在使用API的版本號碼，對於DB API 2.0 版本來說

• 值可能是 ‘1.0’，
• 也可能是 ‘2.0’；

  2）threadsafety 這這隻有在使用多線程時才有參考價值，取值範圍為0~3的整數。

• 0 表示完全不共用模組
• 1 表示線程本身可以共用模組，但不對串連共用
• 3 表示模組完全是安全執行緒的

　3）paramstyle 表示在執行多次類似查詢的時候，參數是如何被拼接到SQL查詢中的：

• ‘format‘表示標準的字串格式化
• ‘pyformat‘表示擴充的格式代碼，使用字典拼接
• ‘qmark‘表示使用問號
• ‘numberic‘表示使用 :1 或 :2 格式的欄位
• ‘named’表示使用 :name 格式的欄位

1.2 異常

　　API中定義了(階層)一些異常類，可以通過 except 塊捕捉多種異常。如果想要知道更多DB API異常的想想可以參考這裡：http://www.python.org/dev/peps/pep-0249/#exceptions，下面是DB API中使用的異常：

===============================================================================異常                       超類               描述                             ===============================================================================StandarError                                所有異常的泛型基類Waring                  StandarError        在非致命錯誤發生時引發Error                   StandarError        所有錯誤條件的泛型超類InterfaceError          Error               關於介面而非資料庫的錯誤DatabaseError           Error               與資料庫相關的錯誤的基類 DataError               DatabaseError       與資料庫相關的問題，比如值超出範圍OperationalError        DatabaseError       資料庫內部操作錯誤IntegrityError          DatabaseError       關係完整性受到影響，比如鍵檢查失敗InternalError           DatabaseError       資料庫內部錯誤，比如非法遊標ProgramningError        DatabaseError       使用者編程錯誤，比如未找到表NotSupportedError       DatabaseError       請求不支援的特性(比如復原)

1.3 串連和遊標

　　使用 connect 函數串連到資料庫，該函數有多個參數，具體使用方法取決於對應的資料庫。下面是具體的參數含義(使用時推薦使用關鍵字參數的方式)：

==================================================參數名         描述                                 ==================================================dsn         資料庫源名稱，給出參數表示資料庫依賴(必選)  user        使用者名稱(可選)                           passowrd    密碼(可選)                                     host        主機名稱(可選)                                      database    資料庫名(可選)                                    

　　函數返回連線物件，該對象表示目前和資料庫的會話，對象支援的方法如下：

=============================================================方法名                 描述                                 =============================================================close()             關閉串連之後，連線物件和它的遊標均不可用commit()            如果支援的話就提交掛起事務，否則不可用rollback()          復原掛起的事務cursor()            返回串連的遊標對象

　　rollback 方法如果使用的資料庫支援的話，就會撤銷所有未提交的事務。而 cursor 方法則返回一個遊標對象，可以通過遊標執行SQL查詢並檢查結果。遊標比串連支援更多方法，也更加易用，下面是遊標方法的概述：

====================================================================================名稱                                     描述                                 ====================================================================================callproc(name[, params])            使用給定的名稱和參數(可選)調用以命名的資料庫程式close()                             關閉遊標後，遊標不可用execute(oper[, params])             執行SQL操作，可選的參數executemany(oper, pseq)             對序列中的每個參數執行SQL操作fetchone()                          把查詢結果集中的下一行儲存為序列或者Nonefetchmany([size])                   擷取查詢的結果集中的多行，預設尺寸為arraysizefetchall()                          將所有(剩餘)的行作為序列的序列nextset()                           跳至下一個可用的結果集(可選)setinputsizes(sizes)                為參數預先定義的記憶體地區setoutputsize(size[, col])          為擷取的大資料庫值設定緩衝區尺寸

　　遊標對象還包含一些特性：

==================================================名稱                   描述                                 ==================================================description         結果列描述的序列，唯讀                       rowcount            結果中的行數，唯讀arraysize           fetchmany中返回的行數，預設為1

1.4 類型

　　DB API 定義了Python中的類型和資料庫類型的對應關係。通過特殊的類型和值的建構函式以及常量(單例模式)，如下(一些模組可能並不是完全按照這些定義)：

==================================================名稱                                  描述                                 ==================================================Date(year, month, day)              建立儲存日期值的對象Time(hour, minute, second)          建立儲存時間值的對象Timestamp(y, mon, d, h, min, s)     建立儲存時間戳記的對象DateFromTicks(ticks)                建立儲存自新紀元以來的秒數的對象TimeFromTicks(ticks)                建立儲存來自秒數的時間值的對象TimestampFromTicks(ticks)           建立儲存來自秒數的時間戳記值的對象Binary(string)                      建立儲存二進位字串值的對象STRING                              描述二進位列(比如LONG或RAW)BINARY                              描述數字列MUMBER                              描述數字列DATETIME                            描述日期/時間列ROWID                               描述行ID列

 

2. SQLite 和PySQLite

　　SQLite是一個小型的資料庫引擎，它不基於集中式資料庫儲存機制，而是直接作用於本地檔案。在Python 2.5 之後，SQLite 的封裝(PySQLite)已經被包含在標準庫中了(在最新的Python版本中已經包含了SQLite資料庫了，並不需要單獨安裝)。

2.1 簡單樣本

　　將SQLite作為名為sqlite3的模組匯入，即可建立一個資料庫檔案(不存在則建立，可指定路徑)，並串連到資料庫，下面是一個簡單的樣本：

1 # -- coding:utf-8 --2 import sqlite33 4 conn = sqlite3.connect(‘myDatabase.db‘)5 curs = conn.cursor() # 獲得遊標6 conn.commit() # 提交更改7 conn.close()  # 關閉串連

2.2 建立和填充表

　　通過下面的樣本來學習如果建立和向表中插入資料，在這裡使用的資料庫是基於USDA營養資料庫實驗室提供的文字格式設定的資料庫，在這裡使用的檔案地址是：http://sr23-nutrient-calculator.googlecode.com/svn-history/r9/trunk/ABBREV.txt，樣本如下：

 1 import sqlite3 2  3 def convert(value): 4     if value.startswith(‘~‘): 5         return value.strip(‘~‘) 6     if not value: 7         value = ‘0‘ 8     return float(value) 9 10 conn = sqlite3.connect(‘food.db‘)11 curs = conn.cursor()12 13 curs.execute(‘‘‘14     CREATE TABLE food(15         id      TEXT  PRIMARY KEY,16         desc    TEXT,17         water   FLOAT,18         kcal    FLOAT,19         protein FLOAT,20         fat     FLOAT,21         ash     FLOAT,22         carbs   FLOAT,23         fiber   FLOAT,24         sugar   FLOAT25     )26     ‘‘‘)27 28 query = r‘INSERT INTO food VALUES(?,?,?,?,?,?,?,?,?,?)‘29 30 for line in open(‘ABBREV.txt‘):31     fields = line.split(‘^‘)32     vals = [convert(f) for f in fields[0:10]]33     curs.execute(query,vals)34 35 conn.commit()36 conn.close()

2.3 搜尋和處理結果

　　查詢資料庫內容只需要建立串連，並獲得該串連的遊標即可，通過 execute 方法執行SQL查詢，使用 fetchall 等方法提取結果，如下所示：

 1 # food_query.py 2 import sqlite3,sys 3  4 conn = sqlite3.connect(‘food.db‘) 5 curs = conn.cursor() 6  7 query = ‘select * from food WHERE %s‘ % sys.argv[1] 8 print query 9 curs.execute(query)10 names = [f[0] for f in curs.description]11 for row in curs.fetchall():12     for pair in zip(names,row):13         print ‘%s: %s‘% pair14     print

　　運行程式，通過命令列傳遞查詢參數：

　　　　python food_query.py "kcal <=100 AND fiber >= 10 ORDER BY sugar"

python基礎教程總結12——資料庫