pyodbc 操作SQL Server資料庫

pyodbc是Python包，使用ODBC驅動器來連接配接SQL Server資料庫，pyodbc的基本類型是Connection，Cursor和Row，其中，Connection表示用戶端和資料庫的連接配接，并用于送出事務；Cursor表示向資料庫發送的查詢請求，Row表示擷取的結果集。

從微軟官方文檔來看，更推薦使用pyodbc來操作SQL Server資料庫。

一，ODBC驅動程式

要使用pyodbc連接配接SQL Server資料庫，必須安裝SQL Server資料庫的ODBC 驅動程式：

Install the Microsoft ODBC Driver for SQL Server on Windows，

目前的版本是Microsoft ODBC Driver 17，支援從SQL Server 2008到目前最新的SQL Server 2019版本。

{ODBC Driver 17 for SQL Server} 
      

Microsoft ODBC Driver for SQL Server 是一個動态連結庫（DLL），包含run-time支援庫，使得程式可以使用native-code API連接配接到SQL Server資料庫。

使用pyodbc連接配接資料庫的基本操作，連接配接資料庫，執行查詢，并周遊查詢結果：

import pyodbc

# Specifying the ODBC driver, server name, database, etc. directly
cn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass')

# Create a cursor from the connection
cursor = cn.cursor()

#Selecting Some Data
cursor.execute("select user_id, user_name from users")
rows = cursor.fetchall()
for row in rows:
    print(row.user_id, row.user_name)

cn.close()      

二，連接配接對象

連接配接對象代表Python和資料庫的連接配接，通過connect()函數來建立連接配接對象，連接配接對象通過ODBC驅動程式來通路SQL Server資料庫：

cn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=mine;UID=user;PWD=pwd')      

1，連接配接對象的屬性

cn.autocommit 是連接配接對象的一個屬性，預設情況下，連接配接對象是不會自動送出的，該屬性的值是False。在執行完成查詢請求之後，需要顯示送出。

如果設定autocommit為True，那麼設定連接配接為自動送出模式：

cn.autocommit = True      

cn.timeout， 用于設定逾時時間，機關是秒，預設值是0，表示不限制時間。

2，連接配接對象可以建立Cursor對象

mycursor = cn.cursor()      

3，連接配接對象送出或復原事務

如果Cursor對象執行的是更新操作，比如執行UPDATE、DELETE或INSERT等更新操作，那麼必須通過連接配接對象來顯式送出事務；如果執行的更新操作失敗，連接配接對象必須顯式復原事務。如果Cursor對象執行的SELECT操作，不需要送出事務或者復原事務。

cn.commit()
cn.rollback()      

4，關閉連接配接對象

當關閉連接配接時，任何未送出的查詢語句都會復原，更新操作的結果将會丢失。當連接配接對象被删除，特别是超出作用域時，連接配接會自動關閉，但是，推薦顯式關閉連接配接。

cn.close()      

三，Cursor對象

Cursor對象用于管理每個fetch操作的上下文，Cursor對象通過Connection對象來建立。

1，執行SQL語句

Cursor調用execute()函數來執行SQL語句：

execute(sql, *parameters)
executemany(sql, *params)      

execute()函數隻執行一次SQL語句，可以向SQL語句傳遞參數，傳參的格式有以下兩種：

# standard
cursor.execute("select a from tbl where b=? and c=?", (x, y))
# pyodbc extension
cursor.execute("select a from tbl where b=? and c=?", x, y)      

executemany()可以對每組參數執行相同的SQL語句，

params = [ ('A', 1), ('B', 2) ]
cursor.executemany("insert into t(name, id) values (?, ?)", params)      

該語句等價于執行execute()函數兩次：

params = [ ('A', 1), ('B', 2) ]
for p in params:
    cursor.execute("insert into t(name, id) values (?, ?)", p)      

如果設定cursor.fast_executemany = True，可以提高executemany()函數的性能，該屬性預設是False。

2，fetch結果

從cursor對象中擷取單行：

cursor.fetchone()      

從cursor對象中擷取多行：

cursor.fetchone(size=1)      

從cursor對象中擷取所有行：

cursor.fetchall()      

3，跳到下一個結果集

此方法将使Cursor對象跳至下一個可用結果集，并丢棄目前結果集中的所有剩餘行。 如果沒有更多結果集，則該方法傳回False。 否則，它将傳回True，随後對fetch方法的調用将傳回下一個結果集中的行。

如果執行傳回多個結果的存儲過程，那麼主要使用此方法。

cursor.nextset()      

4，關閉連接配接

在不再需要cursor時，請使用關閉Cursor對象。

cursor.close()      

如果試圖通路已經被關閉的cursor，程式将會出錯。Cursor對象會在被删除或者超出作用域時自動關閉。

四，Row對象

Row對象調用fetch函數傳回的一行資料，row對象的值可以被替換，從同一個select語句中傳回的row對象共享記憶體。

row對象可以通過索引來通路資料，也可以通過列名來通路資料：

cursor.execute("select album_id, photo_id from photos where user_id=1")
row = cursor.fetchone()
print(row.album_id, row.photo_id)
print(row[0], row[1])  # same as above, but less readable      

fetchone()函數傳回的是單行Row對象，fetchmany()函數 或這fetchall()函數傳回的rows對象的清單：

cursor.execute("select album_id, photo_id from photos where user_id=1")
rows = cursor.fetchall()
for row in rows:
    row.album_id=1
    row.photo_id=1001
      

五，上下文管理器

Connection對象和Cursor對象都支援Python的上下文管理（Context manager），使用with語句來自動關閉Connection對象和Cursor對象，并調用Connection對象的commit()函數來送出查詢。

1，Connection對象的上下文管理器

Connection對象的上下文管理器，不僅自動關閉連接配接，還會送出查詢：

with pyodbc.connect('mydsn') as cnxn:
    do_stuff

#等價于
cnxn = pyodbc.connect('mydsn')
do_stuff
if not cnxn.autocommit:
    cnxn.commit()        

2，Cursor對象的上下文管理器

Cursor對象的上下文管理器，會自動關閉Cursor，還會送出查詢：

with cnxn.cursor() as crsr:
    do_stuff

#等價于
crsr = cnxn.cursor()
do_stuff
if not cnxn.autocommit:
    cnxn.commit()        

3，使用上下文管理器來查詢結果

import pyodbc

# Specifying the ODBC driver, server name, database, etc. directly
with pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass') as cn:

    # Create a cursor from the connection
    with cn.cursor() as cursor:
        
        #Selecting Some Data
        cursor.execute("select user_id, user_name from users")
        rows = cursor.fetchall()
        for row in rows:
            print(row.user_id, row.user_name)      

4，使用上下文管理器來執行更新操作

在執行更新操作，需要顯式送出事務

import pyodbc

with pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass') as cn:
    try:
        cn.autocommit = False
        with cn.cursor() as cursor:
            params = [ ('A', 1), ('B', 2) ]
            cursor.executemany("insert into t(name, id) values (?, ?)", params)
    except pyodbc.DatabaseError as err:
        cn.rollback()
    else:
        cn.commit()
    finally:
        cn.autocommit = True      

六，調用存儲過程

pyodbc 使用{call ...}來調用存儲過程，調用存儲過程的格式如下：

cursor.execute("{CALL usp_NoParameters}")

params = (14, "Dinsdale")
cursor.execute("{CALL usp_HaveParammeters (?,?)}", params)      

參考文檔：

pyodbc wiki

作者

：悅光陰

出處

：http://www.cnblogs.com/ljhdo/

本文版權歸作者和部落格園所有，歡迎轉載，但未經作者同意，必須保留此段聲明，且在文章頁面醒目位置顯示原文連接配接，否則保留追究法律責任的權利。