Python串連資料庫學習之DB-API詳解

在沒有 Python DB-API 之前，各資料庫之間的應用介面非常混亂，實現各不相同。如果項目需要更換資料庫時，則需要做大量的修改，非常不便。Python DB-API 的出現就是為瞭解決這樣的問題。本文主要介紹了Python串連資料庫之DB-API的相關資料,需要的朋友可以參考。

前言

大家都知道在Python中如果要串連資料庫，不管是MySQL、SQL Server、PostgreSQL亦或是SQLite，使用時都是採用遊標的方式，所以就不得不學習Python DB-API。

Python所有的資料庫介面程式都在一定程度上遵守 Python DB-API 規範。DB-API定義了一系列必須的對象和資料庫存取方式，以便為各種底層資料庫系統和多種多樣的資料庫介面程式提供一致的提供者。由於DB-API 為不同的資料庫提供了一致的提供者， 在不同的資料庫之間移植代碼成為一件輕鬆的事情。

Python串連資料庫流程：

使用connect建立connection串連

connect 方法產生一個 connect 對象, 我們通過這個對象來訪問資料庫。符合標準的模組都會實現 connect 方法。

connect 函數的參數如下所示：

• user Username

• password Password

• host Hostname

• database Database name

• dsn Data source name
  

資料庫連接參數可以以一個 DSN 字串的形式提供，樣本：connect(dsn='host:MYDB',user='root',password=' ')
當然，不同的資料庫介面程式可能有些差異，並非都是嚴格按照規範實現，例如MySQLdb則使用 db 參數而不是規範推薦的 database 參數來表示要訪問的資料庫：

MySQLdb串連時可用參數

• host: 資料庫主機名稱.預設是用本地主機

• user: 資料庫登陸名.預設是目前使用者

• passwd: 資料庫登陸的秘密.預設為空白

• db: 要使用的資料庫名.沒有預設值

• port: MySQL服務使用的TCP連接埠.預設是3306

• charset: 資料庫編碼
  

psycopg2串連時可用參數：

• dbname – 資料庫名稱 (dsn串連模式)

• database – 資料庫名稱

• user – 使用者名稱

• password – 密碼

• host – 伺服器位址 (如果不提供預設串連Unix Socket)

• port – 串連連接埠 (預設5432)
  

其中connect對象又有如下方法:

• close():關閉此connect對象, 關閉後無法再進行操作，除非再次建立串連

• commit():提交當前事務，如果是支援事務的資料庫執行增刪改後沒有commit則資料庫預設復原

• rollback():取消當前事務

• cursor():建立遊標對象
  

使用cursor建立遊標對象

cursor遊標對象又有如下屬性和方法:

常用方法:

• close():關閉此遊標對象

• fetchone():得到結果集的下一行

• fetchmany([size = cursor.arraysize]):得到結果集的下幾行

• fetchall():得到結果集中剩下的所有行

• excute(sql[, args]):執行一個資料庫查詢或命令

• excutemany(sql, args):執行多個資料庫查詢或命令

常用屬性:

• connection:建立此遊標對象的資料庫連接

• arraysize:使用fetchmany()方法一次取出多少條記錄，預設為1

• lastrowid:相當於PHP的last_inset_id()
  

其他方法：

• __iter__()：建立一個可迭代對象（可選）

• next()：擷取結果集的下一行（如果支援迭代的話）

• nextset()：移到下一個結果集（如果支援的話）

• callproc(func[,args])：調用一個預存程序

• setinputsizes(sizes)：設定輸入最大值（必須有，但具體實現是可選的）

• setoutputsizes(sizes[,col])：設定大列 fetch 的最大緩衝區大小
  

其他屬性：

• description：返回遊標活動狀態（包含7個元素的元組）：（name, type_code, display_size, internal_size, precision, scale, null_ok）只有 name 和 type_cose 是必需的

• rowcount：最近一次 execute() 建立或影響的行數

• messages：遊標執行後資料庫返回的資訊元組（可選）

• rownumber：當前結果集中遊標所在行的索引（起始行號為 0）

DB-API只中的錯誤定義

錯誤類的層次關係：

StandardError|__Warning|__Error|__InterfaceError|__DatabaseError|__DataError|__OperationalError|__IntegrityError|__InternalError|__ProgrammingError|__NotSupportedError

資料庫操作樣本

代碼如下：

#! /usr/bin/env python# -*- coding: utf-8 -*-# *************************************************************#  Filename @ operatemysql.py#  Author @ Huoty# Create date @ 2015-08-16 10:44:34# Description @ # *************************************************************import MySQLdb# Script starts from here# 串連資料庫db_conn = MySQLdb.connect(host = 'localhost', user= 'root', passwd = '123456')# 如果已經建立了資料庫，可以直接用如下方式串連資料庫#db_conn = MySQLdb.connect(host = "localhost", user = "root",passwd = "123456", db = "testdb")"""connect方法常用參數: host: 資料庫主機名稱.預設是用本地主機 user: 資料庫登陸名.預設是目前使用者 passwd: 資料庫登陸的秘密.預設為空白 db: 要使用的資料庫名.沒有預設值 port: MySQL服務使用的TCP連接埠.預設是3306 charset: 資料庫編碼"""# 擷取操作遊標 cursor = db_conn.cursor()# 使用 execute 方法執行SQL語句cursor.execute("SELECT VERSION()")# 使用 fetchone 方法擷取一條資料庫。dbversion = cursor.fetchone()print "Database version : %s " % dbversion# 建立資料庫cursor.execute("create database if not exists dbtest")# 選擇要操作的資料庫db_conn.select_db('dbtest');# 建立資料表SQL語句sql = """CREATE TABLE if not exists employee(   first_name CHAR(20) NOT NULL,   last_name CHAR(20),   age INT,    sex CHAR(1),   income FLOAT )"""try: cursor.execute(sql)except Exception, e: # Exception 是所有異常的基類，這裡表示捕獲所有的異常 print "Error to create table:", e# 插入資料sql = """INSERT INTO employee(first_name,   last_name, age, sex, income)   VALUES ('%s', '%s', %d, '%s', %d)"""# Sex: Male男, Female女employees = (   {"first_name": "Mac", "last_name": "Mohan", "age": 20, "sex": "M", "income": 2000},  {"first_name": "Wei", "last_name": "Zhu", "age": 24, "sex": "M", "income": 7500},  {"first_name": "Huoty", "last_name": "Kong", "age": 24, "sex": "M", "income": 8000},  {"first_name": "Esenich", "last_name": "Lu", "age": 22, "sex": "F", "income": 3500},  {"first_name": "Xmin", "last_name": "Yun", "age": 31, "sex": "F", "income": 9500},  {"first_name": "Yxia", "last_name": "Fun", "age": 23, "sex": "M", "income": 3500}  )try: # 清空表中資料 cursor.execute("delete from employee") # 執行 sql 插入語句 for employee in employees:  cursor.execute(sql % (employee["first_name"], \   employee["last_name"], \   employee["age"], \   employee["sex"], \   employee["income"])) # 提交到資料庫執行 db_conn.commit() # 對於支援事務的資料庫， 在Python資料庫編程中， # 當遊標建立之時，就自動開始了一個隱形的資料庫事務。 # 用 commit 方法能夠提交事物except Exception, e: # Rollback in case there is any error print "Error to insert data:", e #b_conn.rollback()print "Insert rowcount:", cursor.rowcount# rowcount 是一個唯讀屬性，並返回執行execute(方法後影響的行數。)# 資料庫查詢操作:# fetchone()  得到結果集的下一行 # fetchmany([size=cursor.arraysize]) 得到結果集的下幾行 # fetchall()  返回結果集中剩下的所有行 try: # 執行 SQL cursor.execute("select * from employee") # 擷取一行記錄 rs = cursor.fetchone() print rs # 擷取餘下記錄中的 2 行記錄 rs = cursor.fetchmany(2) print rs # 擷取剩下的所有記錄 ars = cursor.fetchall() for rs in ars:  print rs # 可以用 fetchall 獲得所有記錄，然後再遍曆except Exception, e: print "Error to select:", e# 資料庫更新操作sql = "UPDATE employee SET age = age + 1 WHERE sex = '%c'" % ('M')try: # 執行SQL語句 cursor.execute(sql) # 提交到資料庫執行 db_conn.commit() cursor.execute("select * from employee") ars = cursor.fetchall() print "After update: ------" for rs in ars:  print rsexcept Exception, e: # 發生錯誤時復原 print "Error to update:", e db.rollback()# 關閉資料庫連接db_conn.close()