Python連結Mssql之Python庫pymssql

標籤：comm   建立   tab   ane   com   imp   sql語句   資料庫服務   服務   

串連資料庫

pymssql串連資料庫的方式和使用sqlite的方式基本相同：

• 使用connect建立連線物件
• connect.cursor建立遊標對象，SQL語句的執行基本都在遊標上進行
• cursor.executeXXX方法執行SQL語句，cursor.fetchXXX擷取查詢結果等
• 調用close方法關閉遊標cursor和資料庫連接

import pymssql# server    資料庫伺服器名稱或IP# user      使用者名稱# password  密碼# database  資料庫名稱conn = pymssql.connect(server, user, password, database)cursor = conn.cursor()# 建立、插入操作

cursor.execute("""IF OBJECT_ID(‘persons‘, ‘U‘) IS NOT NULL    DROP TABLE personsCREATE TABLE persons (    id INT NOT NULL,    name VARCHAR(100),    salesrep VARCHAR(100),    PRIMARY KEY(id))""")cursor.executemany(    "INSERT INTO persons VALUES (%d, %s, %s)",    [(1, ‘John Smith‘, ‘John Doe‘),     (2, ‘Jane Doe‘, ‘Joe Dog‘),     (3, ‘Mike T.‘, ‘Sarah H.‘)])# 如果沒有指定autocommit屬性為True的話就需要調用commit()方法conn.commit()

 

# 查詢操作

cursor.execute(‘SELECT * FROM persons WHERE salesrep=%s‘, ‘John Doe‘)row = cursor.fetchone()while row:    print("ID=%d, Name=%s" % (row[0], row[1]))    row = cursor.fetchone()# 也可以使用for迴圈來迭代查詢結果# for row in cursor:#     print("ID=%d, Name=%s" % (row[0], row[1]))# 關閉串連conn.close()

 

注意: 例子中查詢操作的參數使用的%s而不是‘%s‘，若參數值是字串，在執行語句時會自動添加單引號

遊標使用注意事項

一個串連一次只能有一個遊標的查詢處於活躍狀態，如下：

c1 = conn.cursor()c1.execute(‘SELECT * FROM persons‘)c2 = conn.cursor()c2.execute(‘SELECT * FROM persons WHERE salesrep=%s‘, ‘John Doe‘)print( "all persons" )print( c1.fetchall() )  # 顯示出的是c2遊標查詢出來的結果print( "John Doe" )print( c2.fetchall() )  # 不會有任何結果

為了避免上述的問題可以使用以下兩種方式：

• 建立多個串連來保證多個查詢可以並存執行在不同串連的遊標上
• 使用fetchall方法擷取到遊標查詢結果之後再執行下一個查詢， 如下：

c1.execute(‘SELECT ...‘)c1_list = c1.fetchall()c2.execute(‘SELECT ...‘)c2_list = c2.fetchall()

 

遊標返回行為字典變數

上述例子中遊標擷取的查詢結果的每一行為元群組類型，
可以通過在建立遊標時指定as_dict參數來使遊標返回字典變數，
字典中的鍵為資料表的列名

conn = pymssql.connect(server, user, password, database)cursor = conn.cursor(as_dict=True)cursor.execute(‘SELECT * FROM persons WHERE salesrep=%s‘, ‘John Doe‘)for row in cursor:    print("ID=%d, Name=%s" % (row[‘id‘], row[‘name‘]))conn.close()

 

使用 with語句（上下文管理器）

可以通過使用with語句來省去顯示的調用close方法關閉串連和遊標

with pymssql.connect(server, user, password, database) as conn:    with conn.cursor(as_dict=True) as cursor:        cursor.execute(‘SELECT * FROM persons WHERE salesrep=%s‘, ‘John Doe‘)        for row in cursor:            print("ID=%d, Name=%s" % (row[‘id‘], row[‘name‘]))

調用預存程序

pymssql 2.0.0以上的版本可以通過cursor.callproc方法來調用預存程序

with pymssql.connect(server, user, password, database) as conn:    with conn.cursor(as_dict=True) as cursor:        # 建立預存程序        cursor.execute("""        CREATE PROCEDURE FindPerson            @name VARCHAR(100)        AS BEGIN            SELECT * FROM persons WHERE name = @name        END        """)        # 調用預存程序        cursor.callproc(‘FindPerson‘, (‘Jane Doe‘,))        for row in cursor:            print("ID=%d, Name=%s" % (row[‘id‘], row[‘name‘]))

 

參考：http://pymssql.org/en/stable/pymssql_examples.html

Python連結Mssql之Python庫pymssql