pyodbc通路資料庫（python ODBC通路資料庫）

入門

連接配接到資料庫

調用connect方法并傳入ODBC連接配接字元串，其會傳回一個connect對象。通過connect對象，調用cursor()方法,可以擷取一個遊标cursor。如下代碼示例：

import pyodbc

#連接配接示例: Windows系統, 非DSN方式, 使用微軟 SQL Server 資料庫驅動

cnxn =pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;PORT=1433;DATABASE=testdb;UID=me;PWD=pass')

#連接配接示例: Linux系統, 非DSN方式, 使用FreeTDS驅動

cnxn =pyodbc.connect('DRIVER={FreeTDS};SERVER=localhost;PORT=1433;DATABASE=testdb;UID=me;PWD=pass;TDS_Version=7.0')

#連接配接示例:使用DSN方式

cnxn = pyodbc.connect('DSN=test;PWD=password')

# 打開遊标

cursor =cnxn.cursor()

以上示例隻是标準示例，具體的ODBC連接配接字元串以你自己使用的驅動為準。

查詢一些資料

所有SQL語句都使用Cursor.execute()方法執行。比如select語句會傳回一些結果行，你可以使用遊标（Cursor）相關的函數功能（fetchone，fetchall，fetchmany）對結果進行檢索。

Cursor.fetchone 用于傳回一個單行（ Row）對象:

cursor.execute("selectuser_id, user_name from users")

row =cursor.fetchone()

if row:

    print(row)

Row 對象是類似一個python元組（tuples），不過也可以通過列名稱來通路，例如:

cursor.execute("selectuser_id, user_name from users")

row =cursor.fetchone()

print('name:',row[1])         # 使用列索引号來通路資料

print('name:',row.user_name)  # 或者直接使用列名來通路資料

當所有行都已被檢索，則fetchone傳回None.

while 1:

    row = cursor.fetchone()

    if not row:

        break

    print('id:', row.user_id)

Cursor.fetchall方法傳回所有剩餘行并存儲于一個清單中。如果沒有行，則傳回一個空清單。（注意：如果有很多行，會造成大量記憶體占用。Fetchall會一次性将所有資料查詢到本地，然後再周遊）

cursor.execute("selectuser_id, user_name from users")

rows = cursor.fetchall()

for row in rows:

    print(row.user_id, row.user_name)

如果并不在意資料處理時間，可以使用光标本身作為一個疊代器，逐行疊代。這樣可以節省大量的記憶體開銷，但是由于和資料來回進行通信，速度會相對較慢：

cursor.execute("selectuser_id, user_name from users"):

for row in cursor:

    print(row.user_id, row.user_name)

由于Cursor.execute總是傳回遊标（cursor）, 是以也可以簡寫成:

for row incursor.execute("select user_id, user_name from users"):

    print(row.user_id, row.user_name)

我們可以在execut中使用”””三重引号，來應用多行SQL字元串。這樣sql的可讀性大大增強。這是python特有的特性：

cursor.execute(

    """

    select user_id, user_name

      from users

     where last_logon < '2001-01-01'

           and bill_overdue = 1

    """)

SQL參數

ODBC支援使用問号作為SQL的查詢參數占位符。可以在execute方法的SQL參數之後，提供SQL參數占位符的值:

cursor.execute(

    """

    select user_id, user_name

      from users

     where last_logon < ?

           and bill_overdue = ?

    """, '2001-01-01', 1)

這樣做可以防止SQL注入攻擊，提高安全性。如果使用不同的參數反複執行相同的SQL它效率會更高，這種情況下該SQL将隻預裝（prepared ）一次。（pyodbc隻保留最後一條編寫的語句，是以如果程式在語句之間進行切換，每次都會預裝，造成多次預裝。）

Python的DB API指定參數應以序列（sequence）對象傳遞，是以pyodbc也支援這種方式：

cursor.execute(

    """

    select user_id, user_name

      from users

     where last_logon < ?

           and bill_overdue = ?

    """, ['2001-01-01', 1])

插入資料

插入資料使用相同的函數 - 通過傳入insert SQL和相關占位參數執行插入資料：

cursor.execute("insertinto products(id, name) values ('pyodbc', 'awesome library')")

cnxn.commit()

cursor.execute("insertinto products(id, name) values (?, ?)", 'pyodbc', 'awesome library')

cnxn.commit()

注意：調用cnxn.commit()。發成錯誤可以復原。具體需要看資料庫特性支援情況。如果資料發生改變，最好進行commit。如果不送出，則在連接配接中斷時，所有資料會發生復原。

更新和删除資料

更新和删除工作以同樣的方式：通過特定的SQL來執行。通常我們都想知道更新和删除的時候有多少條記錄受到影響，可以使用Cursor.rowcount來擷取值:

cursor.execute("deletefrom products where id <> ?", 'pyodbc')

print('Deleted {}inferior products'.format(cursor.rowcount))

cnxn.commit()

由于execute 總是傳回遊标（允許你調用鍊或疊代器使用），有時我們直接這樣簡寫：

deleted =cursor.execute("delete from products where id <> 'pyodbc'").rowcount

cnxn.commit()

注意一定要調用commit。否則連接配接中斷時會造成改動復原。

技巧和竅門

引号

于單引号SQL是有效的，當值需要使用單引号時，使用用雙引号包圍的SQL:

cursor.execute("deletefrom products where id <> 'pyodbc'")

如果使用三重引号, 我們可以這樣使用單引号:

cursor.execute(

    """

    delete

      from products

     where id <> 'pyodbc'

    """)

列名稱

Microsoft SQLServer之類的一些資料庫不會産生計算列的列名，在這種情況下，需要通過索引來通路列。我們也可以使用sql列别名的方式，為計算列指定引用名稱：

row =cursor.execute("select count(*) as user_count fromusers").fetchone()

print('{}users'.format(row.user_count)

當然也可以直接使用列索引來通路列值:

count =cursor.execute("select count(*) from users").fetchone()[0]

print('{}users'.format(count)

注意，上例的首列不能是Null。否則fetchone方法将傳回None并且會報NoneType不支援索引的錯誤。如果有一個預設值，經常可以是ISNULL或合并：

maxid =cursor.execute("select coalesce(max(id), 0) fromusers").fetchone()[0]

自動清理

連接配接（預設）在一個事務中。如果一個連接配接關閉前沒有送出，則會進行目前事務復原。很少需要finally或except 語句來執行人為的清理操作，程式會自動清理。

例如，如果下列執行過程中任何一條SQL語句出現異常，都将引發導緻這兩個遊标執行失效。進而保證原子性，要麼所有資料都插入發生，要麼所有資料都不插入。不需要人為編寫清理代碼。

cnxn =pyodbc.connect(...)

cursor = cnxn.cursor()

cursor.execute("insertinto t(col) values (1)")

cursor.execute("insertinto t(col) values (2)")

cnxn.commit()