pip install pymssql

import pymssql

# 連接參數(shù)
server = 'server_name'
database = 'database_name'
username = 'username'
password = 'password'

# 建立連接
conn = pymssql.connect(server=server, database=database, user=username, password=password)

# 創(chuàng)建游標(biāo)對(duì)象
cursor = conn.cursor()

# 執(zhí)行SQL查詢(xún)
cursor.execute("SELECT * FROM your_table")

# 獲取查詢(xún)結(jié)果
result = cursor.fetchall()

# 遍歷結(jié)果
for row in result:
    print(row)

# 插入數(shù)據(jù)
insert_query = "INSERT INTO your_table (column1, column2) VALUES (%s, %s)"
insert_data = ('value1', 'value2')
cursor.execute(insert_query, insert_data)

# 更新數(shù)據(jù)
update_query = "UPDATE your_table SET column1 = %s WHERE id = %s"
update_data = ('new_value', 1)
cursor.execute(update_query, update_data) #參數(shù)化查詢(xún)

# 刪除數(shù)據(jù)
delete_query = "DELETE FROM your_table WHERE id = %s"
delete_data = (1,)
cursor.execute(delete_query, delete_data)

# 提交事務(wù)
conn.commit()

# 關(guān)閉游標(biāo)
cursor.close()

# 創(chuàng)建游標(biāo)對(duì)象
cursor = conn.cursor()
try:
    # 開(kāi)始事務(wù)
    conn.begin()
    # 執(zhí)行數(shù)據(jù)庫(kù)操作
    cursor.execute("INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2')")
    cursor.execute("UPDATE your_table SET column1 = 'new_value' WHERE id = 1")
    # 提交事務(wù)
    conn.commit()
except Exception as e:
    # 回滾事務(wù)
    conn.rollback()
    print("Error:", e)
# 關(guān)閉游標(biāo)
cursor.close()

# 查詢(xún)結(jié)果處理
# pymssql返回的查詢(xún)結(jié)果是一個(gè)元組列表，其中每個(gè)元組表示一行數(shù)據(jù)。你可以通過(guò)遍歷查詢(xún)結(jié)果來(lái)逐行處理數(shù)據(jù)。
# 創(chuàng)建游標(biāo)對(duì)象
cursor = conn.cursor()
# 執(zhí)行查詢(xún)
cursor.execute("SELECT column1, column2 FROM your_table")
# 獲取查詢(xún)結(jié)果
result = cursor.fetchall()
# 遍歷結(jié)果
for row in result:
    column1_value = row[0]
    column2_value = row[1]
    # 處理數(shù)據(jù)
# 關(guān)閉游標(biāo)
cursor.close()

# 創(chuàng)建游標(biāo)對(duì)象
cursor = conn.cursor()
# 執(zhí)行查詢(xún)
cursor.execute("SELECT column1, column2 FROM your_table")
# 獲取一條記錄
row = cursor.fetchone()
while row:
    # 處理數(shù)據(jù)
    print(row)
    # 獲取下一條記錄
    row = cursor.fetchone()
# 關(guān)閉游標(biāo)
cursor.close()

# 批量插入數(shù)據(jù)
# 如果你需要插入大量數(shù)據(jù)到數(shù)據(jù)庫(kù)，一次插入一行可能效率較低。pymssql允許你使用executemany()方法進(jìn)行批量插入，一次插入多行數(shù)據(jù)。
# 創(chuàng)建游標(biāo)對(duì)象
cursor = conn.cursor()
# 準(zhǔn)備插入數(shù)據(jù)
data = [('value1', 'value2'),
        ('value3', 'value4'),
        ('value5', 'value6')]
# 執(zhí)行批量插入
insert_query = "INSERT INTO your_table (column1, column2) VALUES (%s, %s)"
cursor.executemany(insert_query, data)
# 提交事務(wù)
conn.commit()
# 關(guān)閉游標(biāo)
cursor.close()

# 存儲(chǔ)過(guò)程調(diào)用
# pymssql也支持調(diào)用MSSQL數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程。你可以使用execute_proc()方法來(lái)執(zhí)行存儲(chǔ)過(guò)程。
# 創(chuàng)建游標(biāo)對(duì)象
cursor = conn.cursor()
# 執(zhí)行存儲(chǔ)過(guò)程
cursor.execute_proc('your_stored_procedure_name', ('param1', 'param2'))
# 獲取結(jié)果
result = cursor.fetchall()
# 關(guān)閉游標(biāo)
cursor.close()

# 定義分頁(yè)參數(shù)
page_size = 10
page_number = 1
# 執(zhí)行分頁(yè)查詢(xún)
query = f"SELECT column1, column2 FROM your_table ORDER BY column1 OFFSET {page_size * (page_number - 1)} ROWS FETCH NEXT {page_size} ROWS ONLY"
cursor.execute(query)
result = cursor.fetchall()
for row in result:
    # 處理數(shù)據(jù)

# 創(chuàng)建游標(biāo)對(duì)象
cursor = conn.cursor()
# 定義分頁(yè)查詢(xún)語(yǔ)句
page_size = 10  # 每頁(yè)的記錄數(shù)
page_number = 1  # 頁(yè)碼
offset = (page_number - 1) * page_size  # 計(jì)算偏移量
query = f"SELECT column1, column2 FROM your_table ORDER BY column1 OFFSET {offset} ROWS FETCH NEXT {page_size} ROWS ONLY"
# 執(zhí)行分頁(yè)查詢(xún)
cursor.execute(query)
# 處理查詢(xún)結(jié)果
result = cursor.fetchall()
for row in result:
    # 處理數(shù)據(jù)
# 關(guān)閉游標(biāo)
cursor.close()

import pymssql
try:
    conn = pymssql.connect(server='server_name', database='database_name', user='username', password='password')
    # 連接成功，執(zhí)行數(shù)據(jù)庫(kù)操作
    cursor = conn.cursor()
    # 執(zhí)行查詢(xún)、插入、更新等操作
    # ...
    conn.commit()
    cursor.close()
    conn.close()
except pymssql.OperationalError as e:
    # 處理連接錯(cuò)誤
    print("Connection Error:", e)

import pymssql
try:
    # 連接數(shù)據(jù)庫(kù)
    conn = pymssql.connect(server='server_name', database='database_name', user='username', password='password')
    # 執(zhí)行數(shù)據(jù)庫(kù)操作
    cursor = conn.cursor()
    cursor.execute("SELECT column1, column2 FROM your_table")
    result = cursor.fetchall()
    # 處理查詢(xún)結(jié)果
    for row in result:
        # 處理數(shù)據(jù)
except pymssql.Error as e:
    print("Database Error:", e)
finally:
    # 關(guān)閉連接
    if conn:
        conn.close()

# 創(chuàng)建游標(biāo)對(duì)象
cursor = conn.cursor()
# 執(zhí)行查詢(xún)
cursor.execute("SELECT column1, column2 FROM your_table")
# 獲取列名
column_names = [column[0] for column in cursor.description]
# 獲取列類(lèi)型
column_types = [column[1] for column in cursor.description]
# 處理查詢(xún)結(jié)果
result = cursor.fetchall()
for row in result:
    for name, value in zip(column_names, row):
        print(f"{name}: {value}")
# 關(guān)閉游標(biāo)
cursor.close()

cursor.execute("SELECT column1, column2 FROM your_table")
result = cursor.fetchall()
for row in result:
    column1_value = row[0] if row[0] is not None else 'N/A'
    column2_value = row[1] if row[1] is not None else 'N/A'
    # 處理數(shù)據(jù)

# 創(chuàng)建游標(biāo)對(duì)象
cursor = conn.cursor()
# 執(zhí)行存儲(chǔ)過(guò)程
cursor.callproc('your_stored_procedure_name', (param1, param2))
# 獲取輸出參數(shù)的值
output_param1 = cursor.getoutputparams()[0]
output_param2 = cursor.getoutputparams()[1]
# 關(guān)閉游標(biāo)
cursor.close()

# 創(chuàng)建游標(biāo)對(duì)象
cursor = conn.cursor()
# 定義更新語(yǔ)句和數(shù)據(jù)
update_query = "UPDATE your_table SET column1 = %s WHERE id = %s"
data = [('new_value1', 1), ('new_value2', 2), ('new_value3', 3)]
# 執(zhí)行批量更新
cursor.executemany(update_query, data)
# 提交事務(wù)
conn.commit()
# 關(guān)閉游標(biāo)
cursor.close()

# 使用with語(yǔ)句管理連接和事務(wù)
with pymssql.connect(server='server_name', database='database_name', user='username', password='password') as conn:
    # 創(chuàng)建游標(biāo)對(duì)象
    cursor = conn.cursor()
    try:
        # 執(zhí)行數(shù)據(jù)庫(kù)操作
        cursor.execute("INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2')")
        cursor.execute("UPDATE your_table SET column1 = 'new_value' WHERE id = 1")
        # 提交事務(wù)
        conn.commit()
    except Exception as e:
        # 回滾事務(wù)
        conn.rollback()
        print("Error:", e)
    # 關(guān)閉游標(biāo)
    cursor.close()

import asyncio
import pymssql
async def main():
    # 創(chuàng)建異步連接
    conn = await pymssql.connect(server='server_name', database='database_name', user='username', password='password', asynchronous=True)
    # 創(chuàng)建異步游標(biāo)
    cursor = conn.cursor(as_dict=True)
    # 執(zhí)行異步查詢(xún)
    await cursor.execute("SELECT * FROM your_table")
    # 獲取結(jié)果
    result = await cursor.fetchall()
    # 處理查詢(xún)結(jié)果
    for row in result:
        # 處理數(shù)據(jù)
    # 關(guān)閉游標(biāo)和連接
    await cursor.close()
    await conn.close()
# 創(chuàng)建事件循環(huán)并運(yùn)行異步任務(wù)
loop = asyncio.get_event_loop()
loop.run_until_complete(main())

from pymssql import pool
# 創(chuàng)建連接池
pool = pool.ConnectionPool(server='server_name', database='database_name', user='username', password='password', max_connections=5)
# 從連接池獲取連接
conn = pool.get_connection()
# 執(zhí)行數(shù)據(jù)庫(kù)操作
cursor = conn.cursor()
cursor.execute("SELECT * FROM your_table")
result = cursor.fetchall()
# 處理查詢(xún)結(jié)果
for row in result:
    # 處理數(shù)據(jù)
# 關(guān)閉游標(biāo)和連接
cursor.close()
conn.close()