pymssql 介紹
pymssql包是Python語言用於連接SQL Server資料庫的驅動程式（或者稱作DB API），它是最終和資料庫進行交互的工具。SQLAlchemy包就是利用pymssql包實現和SQL Server資料庫交互的功能的。
一，pymssql包的基本組成
pymssql包由兩個模組構成：pymssql 和 _mssql，pymssql 是建立在_mssql模組之上的模組，相對來說，_mssql性能更高。
pymssql模組由Connection和Cursor 兩個大類構成：
- Connection類代表MS SQL Sever資料庫的一個連接，
- Cursor類用於向資料庫發送查詢請求，並獲取查詢的的結果。
二，連接
連接對象用於連接SQL Server引擎，並設置連接的屬性，比如連接超時，字符集等。
1，創建連接對象
pymssql通過類函數來構建連接對，在創建連接對象的同時，打開連接：
class pymssql.Connection(user, password, host, database, timeout, login_timeout, charset, as_dict)
2，構建Cursor對象
在創建連接對象之後，創建Cursor對象，使用Cursor對象向資料庫引擎發送查詢請求，並獲取查詢的結果：
Connection.cursor(as_dict=False)
as_dict是布爾類型，默認值是False，表示返回的數據是元組（tuple）類型；如果設置為True，返回的數據集是字典(dict)類型。
3，提交查詢和自動提交模式
在執行查詢之後，必須提交當前的事務，以真正執行Cursor對象的查詢請求：
Connection.commit()
默認情況下，自動提交模式是關閉的，用戶可以設置自動提交，pymssql自動執行Cursor發送的查詢請求：
Connection.autocommit(status)
status是bool值，True表示打開自動提交模式，False表示關閉自動提交模式，默認值是False。
4，關閉連接
在執行完查詢之後，關閉連接：
Connection.close()
三，Cursor
通過打開的連接對象來創建Cursor對象，通過Cursor對象向資料庫引擎發送查詢請求，並獲取查詢的結果。
1，執行查詢
Cursor對象調用execute**()函數來執行查詢請求，
Cursor.execute(operation)
Cursor.execute(operation, params)
Cursor.executemany(operation, params_seq)
參數注釋：
- operation：表示執行的sql語句，
- params ：表示sql語句的參數，
- params_seq：參數序列，用於sql語句包含多個參數的情況。
注意，除設置自動提交模式之外，必須在執行查詢之後，通過連接對象來提交查詢。
Connection.commit()
如果sql語句只包含一個參數，那麼必須在sql語句中顯式使用%s或%d作為佔位符，分別用於引用字元型的參數和數值型的參數。
cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
如果sql語句包含多個參數，那麼使用list來傳遞參數：
cursor.executemany( "INSERT INTO persons VALUES (%d, %s, %s)", [(1, 'John Smith', 'John Doe'), (2, 'Jane Doe', 'Joe Dog'), (3, 'Mike T.', 'Sarah H.')])
2，獲取查詢結果
Cursor對象調用fetch**()函數來獲取查詢的結果：
Cursor.fetchone() Cursor.fetchmany(size=None) Cursor.fetchall()
fetch**()函數是迭代的：
- fetchone()：表示從查詢結果中獲取下一行（next row）
- fetchmany()：表示從查詢結果中獲取下面的多行（next batch）
- fetchall()：表示從查詢結果中獲取剩餘的所有數據行(all remaining）
3，跳過結果集
當查詢的結果包含多個結果集時，可以跳過當前的結果集，跳到下一個結果集：
Cursor.nextset()
如果當前結果集還有數據行未被讀取，那麼這些剩餘的數據行會被丟棄。
四，執行存儲過程
Cursor對象有函數callproc()，用於執行存儲過程：
Cursor.callproc(sp_name, paras)
第一個參數是存儲過程的名稱，第二個參數是一個元組類型，
五，pymssql模組的基本操作
1，pymssql的基本操作
from os import getenv import pymssql server = getenv("PYMSSQL_TEST_SERVER") user = getenv("PYMSSQL_TEST_USERNAME") password = getenv("PYMSSQL_TEST_PASSWORD") conn = pymssql.connect(server, user, password, "tempdb") cursor = conn.cursor(as_dict=False) cursor.execute("TSQL query") cursor.executemany("INSERT INTO persons VALUES (%d, %s, %s)", [(1, 'John Smith', 'John Doe'), (2, 'Jane Doe', 'Joe Dog'), (3, 'Mike T.', 'Sarah H.')]) # you must call commit() to persist your data if you don't set autocommit to True 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() conn.close()
2，以字典集返回數據行
conn = pymssql.connect(server, user, password, "tempdb") 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()
3，使用with語句
with是上下文管理器，可以自動關閉上下文。
如果使用with語句來創建連接對象和Cursor對象，那麼就不需要顯式的關閉連接和Cursor對象，在語句執行完成之後，Python會自動檢測連接對象和Cursor對象的作用域，一旦連接對象或Cursor對象不再有效，Python就會關閉連接或Cursor對象。
with pymssql.connect(server, user, password, "tempdb") 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']))
六，附上程式碼庫
附上程式碼，以饗讀者。
import pymssql from sqlalchemy import create_engine import pandas as pd from sqlalchemy.sql import text as sql_text class DBHelper: def __init__(self): self.name='DB Helper' self.db_host = r'sql server' self.db_name = 'db name' self.db_user = r'sa' self.db_password = r'pwd' ###################################################### ## data connection ## ###################################################### def get_engine(self): str_format = 'mssql+pymssql://{0}:{1}@{2}/{3}?charset=utf8' connection_str = str_format.format(self.db_user,self.db_password,self.db_host,self.db_name) engine = create_engine(connection_str,echo=False) return engine def get_pymssql_conn(self): conn = pymssql.connect(self.db_host, self.db_user, self.db_password, self.db_name) return conn ###################################################### ## common SQL APIs ## ###################################################### def write_data(self,df,destination,if_exists='append',schema='dbo'): engine = self.get_engine() df.to_sql(destination, con=engine, if_exists=if_exists,index = False, schema=schema
, method='multi', chunksize=1000) def read_data(self,sql): engine = self.get_engine() df = pd.read_sql(sql, con=engine) return df def exec_sql(self,sql): engine = self.get_engine() con = engine.connect() with con.begin() as tran: con.execute(sql_text(sql).execution_options(autocommit=True)) def exec_sp(self,sp_name,*paras): with pymssql.connect(self.db_host, self.db_user, self.db_password, database=self.db_name) as conn: with conn.cursor(as_dict=False) as cursor: try: cursor.callproc(sp_name, paras) conn.commit() except Exception as e: print(e) def exec_sp_result(self,sp_name,*paras): with pymssql.connect(self.db_host, self.db_user, self.db_password, database=self.db_name) as conn: with conn.cursor(as_dict=True) as cursor: try: cursor.callproc(sp_name, paras) cursor.nextset() result=cursor.fetchall() conn.commit() df=pd.DataFrame.from_records(result) return df except Exception as e: print(e)
參考文檔：
