python的pymssql操作MSSQL数据库

在Python中，pymssql是一个用于与Microsoft SQL Server数据库进行交互的第三方库。pymssql提供了连接到数据库、执行SQL查询、插入、更新和删除数据等功能。下面我将详细介绍如何使用pymssql进行MSSQL数据库操作。

安装pymssql库 首先，确保你的Python环境已经安装了pymssql库。你可以使用pip工具进行安装

pip install pymssql

• 插入、更新和删除数据

使用pymssql库，你可以执行插入、更新和删除数据的操作。 

1.  
   import pymssql
2.  
    
3.  
   # 连接参数
4.  
   server = 'server_name'
5.  
   database = 'database_name'
6.  
   username = 'username'
7.  
   password = 'password'
8.  
    
9.  
   # 建立连接
10.  
    conn = pymssql.connect(server=server, database=database, user=username, password=password)
11.  
     
12.  
    # 创建游标对象
13.  
    cursor = conn.cursor()
14.  
     
15.  
    # 执行SQL查询
16.  
    cursor.execute("SELECT * FROM your_table")
17.  
     
18.  
    # 获取查询结果
19.  
    result = cursor.fetchall()
20.  
     
21.  
    # 遍历结果
22.  
    for row in result:
23.  
    print(row)
24.  
     
25.  
    # 插入数据
26.  
    insert_query = "INSERT INTO your_table (column1, column2) VALUES (%s, %s)"
27.  
    insert_data = ('value1', 'value2')
28.  
    cursor.execute(insert_query, insert_data)
29.  
     
30.  
    # 更新数据
31.  
    update_query = "UPDATE your_table SET column1 = %s WHERE id = %s"
32.  
    update_data = ('new_value', 1)
33.  
    cursor.execute(update_query, update_data) #参数化查询
34.  
     
35.  
    # 删除数据
36.  
    delete_query = "DELETE FROM your_table WHERE id = %s"
37.  
    delete_data = (1,)
38.  
    cursor.execute(delete_query, delete_data)
39.  
     
40.  
    # 提交事务
41.  
    conn.commit()
42.  
     
43.  
    # 关闭游标
44.  
    cursor.close()
45.  
     

•  管理事务

以确保一组数据库操作要么全部成功，要么全部回滚。 

1.  
   # 创建游标对象
2.  
   cursor = conn.cursor()
3.  
    
4.  
   try:
5.  
   # 开始事务
6.  
   conn.begin()
7.  
    
8.  
   # 执行数据库操作
9.  
   cursor.execute("INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2')")
10.  
    cursor.execute("UPDATE your_table SET column1 = 'new_value' WHERE id = 1")
11.  
     
12.  
    # 提交事务
13.  
    conn.commit()
14.  
     
15.  
    except Exception as e:
16.  
    # 回滚事务
17.  
    conn.rollback()
18.  
    print("Error:", e)
19.  
     
20.  
    # 关闭游标
21.  
    cursor.close()

• 查询结果处理

# pymssql返回的查询结果是一个元组列表，其中每个元组表示一行数据。你可以通过遍历查询结果来逐行处理数据。 

1.  
   # 查询结果处理
2.  
   # pymssql返回的查询结果是一个元组列表，其中每个元组表示一行数据。你可以通过遍历查询结果来逐行处理数据。
3.  
   # 创建游标对象
4.  
   cursor = conn.cursor()
5.  
    
6.  
   # 执行查询
7.  
   cursor.execute("SELECT column1, column2 FROM your_table")
8.  
    
9.  
   # 获取查询结果
10.  
    result = cursor.fetchall()
11.  
     
12.  
    # 遍历结果
13.  
    for row in result:
14.  
    column1_value = row[0]
15.  
    column2_value = row[1]
16.  
    # 处理数据
17.  
     
18.  
    # 关闭游标
19.  
    cursor.close()

•  处理大型结果集

如果查询结果集非常大，无法一次性全部加载到内存中，可以使用pymssql提供的fetchone()和fetchmany()方法来逐步获取结果集的数据。

1.  
   # 创建游标对象
2.  
   cursor = conn.cursor()
3.  
    
4.  
   # 执行查询
5.  
   cursor.execute("SELECT column1, column2 FROM your_table")
6.  
    
7.  
   # 获取一条记录
8.  
   row = cursor.fetchone()
9.  
   while row:
10.  
    # 处理数据
11.  
    print(row)
12.  
     
13.  
    # 获取下一条记录
14.  
    row = cursor.fetchone()
15.  
     
16.  
    # 关闭游标
17.  
    cursor.close()

•  批量插入数据

# 如果你需要插入大量数据到数据库，一次插入一行可能效率较低。pymssql允许你使用executemany()方法进行批量插入，一次插入多行数据。

1.  
   # 批量插入数据
2.  
   # 如果你需要插入大量数据到数据库，一次插入一行可能效率较低。pymssql允许你使用executemany()方法进行批量插入，一次插入多行数据。
3.  
   # 创建游标对象
4.  
   cursor = conn.cursor()
5.  
    
6.  
   # 准备插入数据
7.  
   data = [('value1', 'value2'),
8.  
   ('value3', 'value4'),
9.  
   ('value5', 'value6')]
10.  
     
11.  
    # 执行批量插入
12.  
    insert_query = "INSERT INTO your_table (column1, column2) VALUES (%s, %s)"
13.  
    cursor.executemany(insert_query, data)
14.  
     
15.  
    # 提交事务
16.  
    conn.commit()
17.  
     
18.  
    # 关闭游标
19.  
    cursor.close()

•  存储过程调用

# pymssql也支持调用MSSQL数据库中的存储过程。你可以使用execute_proc()方法来执行存储过程。

1.  
   # 存储过程调用
2.  
   # pymssql也支持调用MSSQL数据库中的存储过程。你可以使用execute_proc()方法来执行存储过程。
3.  
   # 创建游标对象
4.  
   cursor = conn.cursor()
5.  
    
6.  
   # 执行存储过程
7.  
   cursor.execute_proc('your_stored_procedure_name', ('param1', 'param2'))
8.  
    
9.  
   # 获取结果
10.  
    result = cursor.fetchall()
11.  
     
12.  
    # 关闭游标
13.  
    cursor.close()

•  分页查询

当处理大量数据时，分页查询是一种常见的需求。可以使用pymssql的OFFSET和FETCH语句来实现分页查询。通过调整page_size和page_number参数，可以获取指定页数的数据。

1.  
   # 定义分页参数
2.  
   page_size = 10
3.  
   page_number = 1
4.  
    
5.  
   # 执行分页查询
6.  
   query = f"SELECT column1, column2 FROM your_table ORDER BY column1 OFFSET {page_size * (page_number - 1)} ROWS FETCH NEXT {page_size} ROWS ONLY"
7.  
   cursor.execute(query)
8.  
    
9.  
   result = cursor.fetchall()
10.  
     
11.  
    for row in result:
12.  
    # 处理数据

1.  
   # 创建游标对象
2.  
   cursor = conn.cursor()
3.  
    
4.  
   # 定义分页查询语句
5.  
   page_size = 10 # 每页的记录数
6.  
   page_number = 1 # 页码
7.  
   offset = (page_number - 1) * page_size # 计算偏移量
8.  
   query = f"SELECT column1, column2 FROM your_table ORDER BY column1 OFFSET {offset} ROWS FETCH NEXT {page_size} ROWS ONLY"
9.  
    
10.  
    # 执行分页查询
11.  
    cursor.execute(query)
12.  
     
13.  
    # 处理查询结果
14.  
    result = cursor.fetchall()
15.  
    for row in result:
16.  
    # 处理数据
17.  
     
18.  
    # 关闭游标
19.  
    cursor.close()

• 处理数据库连接错误

在连接数据库时，可能会遇到连接错误。可以通过捕获pymssql库引发的pymssql.OperationalError异常来处理连接错误。 

1.  
   import pymssql
2.  
    
3.  
   try:
4.  
   conn = pymssql.connect(server='server_name', database='database_name', user='username', password='password')
5.  
   # 连接成功，执行数据库操作
6.  
   cursor = conn.cursor()
7.  
   # 执行查询、插入、更新等操作
8.  
   # ...
9.  
   conn.commit()
10.  
    cursor.close()
11.  
    conn.close()
12.  
     
13.  
    except pymssql.OperationalError as e:
14.  
    # 处理连接错误
15.  
    print("Connection Error:", e)

1.  
   import pymssql
2.  
    
3.  
   try:
4.  
   # 连接数据库
5.  
   conn = pymssql.connect(server='server_name', database='database_name', user='username', password='password')
6.  
    
7.  
   # 执行数据库操作
8.  
   cursor = conn.cursor()
9.  
   cursor.execute("SELECT column1, column2 FROM your_table")
10.  
    result = cursor.fetchall()
11.  
     
12.  
    # 处理查询结果
13.  
    for row in result:
14.  
    # 处理数据
15.  
     
16.  
    except pymssql.Error as e:
17.  
    print("Database Error:", e)
18.  
     
19.  
    finally:
20.  
    # 关闭连接
21.  
    if conn:
22.  
    conn.close()

• 获取查询结果的列信息

如果你需要获取查询结果的列信息，如列名、数据类型等，可以使用cursor.description属性。

1.  
   # 创建游标对象
2.  
   cursor = conn.cursor()
3.  
    
4.  
   # 执行查询
5.  
   cursor.execute("SELECT column1, column2 FROM your_table")
6.  
    
7.  
   # 获取列名
8.  
   column_names = [column[0] for column in cursor.description]
9.  
    
10.  
    # 获取列类型
11.  
    column_types = [column[1] for column in cursor.description]
12.  
     
13.  
    # 处理查询结果
14.  
    result = cursor.fetchall()
15.  
    for row in result:
16.  
    for name, value in zip(column_names, row):
17.  
    print(f"{name}: {value}")
18.  
     
19.  
    # 关闭游标
20.  
    cursor.close()

•  处理查询结果中的NULL值

在查询结果中，某些列的值可能为NULL。pymssql将NULL值表示为Python中的None。你可以使用条件语句来处理查询结果中的NULL值。

1.  
   cursor.execute("SELECT column1, column2 FROM your_table")
2.  
    
3.  
   result = cursor.fetchall()
4.  
    
5.  
   for row in result:
6.  
   column1_value = row[0] if row[0] is not None else 'N/A'
7.  
   column2_value = row[1] if row[1] is not None else 'N/A'
8.  
   # 处理数据

•  执行存储过程并获取输出参数

如果你需要执行MSSQL数据库中的存储过程，并获取输出参数的值，可以使用pymssql提供的callproc()方法。使用callproc()方法执行名为your_stored_procedure_name的存储过程，并传递参数param1和param2。然后，可以使用getoutputparams()方法获取输出参数的值。

1.  
   # 创建游标对象
2.  
   cursor = conn.cursor()
3.  
    
4.  
   # 执行存储过程
5.  
   cursor.callproc('your_stored_procedure_name', (param1, param2))
6.  
    
7.  
   # 获取输出参数的值
8.  
   output_param1 = cursor.getoutputparams()[0]
9.  
   output_param2 = cursor.getoutputparams()[1]
10.  
     
11.  
    # 关闭游标
12.  
    cursor.close()

•  批量更新数据

如果你需要批量更新数据库中的数据，可以使用pymssql的executemany()方法。

1.  
   # 创建游标对象
2.  
   cursor = conn.cursor()
3.  
    
4.  
   # 定义更新语句和数据
5.  
   update_query = "UPDATE your_table SET column1 = %s WHERE id = %s"
6.  
   data = [('new_value1', 1), ('new_value2', 2), ('new_value3', 3)]
7.  
    
8.  
   # 执行批量更新
9.  
   cursor.executemany(update_query, data)
10.  
     
11.  
    # 提交事务
12.  
    conn.commit()
13.  
     
14.  
    # 关闭游标
15.  
    cursor.close()

•  使用with语句自动管理连接和事务

使用with语句可以更方便地管理数据库连接和事务，确保资源的正确释放和事务的提交或回滚。

1.  
   # 使用with语句管理连接和事务
2.  
   with pymssql.connect(server='server_name', database='database_name', user='username', password='password') as conn:
3.  
   # 创建游标对象
4.  
   cursor = conn.cursor()
5.  
    
6.  
   try:
7.  
   # 执行数据库操作
8.  
   cursor.execute("INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2')")
9.  
   cursor.execute("UPDATE your_table SET column1 = 'new_value' WHERE id = 1")
10.  
     
11.  
    # 提交事务
12.  
    conn.commit()
13.  
     
14.  
    except Exception as e:
15.  
    # 回滚事务
16.  
    conn.rollback()
17.  
    print("Error:", e)
18.  
     
19.  
    # 关闭游标
20.  
    cursor.close()

•  异步操作

如果你需要执行异步的MSSQL数据库操作，pymssql提供了对异步IO的支持。可以使用pymssql.connect()的asynchronous=True参数来创建异步连接，以及cursor.execute()的as_dict=True参数来执行异步查询并返回字典格式的结果。使用asyncio模块创建了一个异步的主函数main()，在其中创建了异步连接和游标，并执行了异步查询。最后，我们使用事件循环运行异步任务。

1.  
   import asyncio
2.  
   import pymssql
3.  
    
4.  
   async def main():
5.  
   # 创建异步连接
6.  
   conn = await pymssql.connect(server='server_name', database='database_name', user='username', password='password', asynchronous=True)
7.  
    
8.  
   # 创建异步游标
9.  
   cursor = conn.cursor(as_dict=True)
10.  
     
11.  
    # 执行异步查询
12.  
    await cursor.execute("SELECT * FROM your_table")
13.  
     
14.  
    # 获取结果
15.  
    result = await cursor.fetchall()
16.  
     
17.  
    # 处理查询结果
18.  
    for row in result:
19.  
    # 处理数据
20.  
     
21.  
    # 关闭游标和连接
22.  
    await cursor.close()
23.  
    await conn.close()
24.  
     
25.  
    # 创建事件循环并运行异步任务
26.  
    loop = asyncio.get_event_loop()
27.  
    loop.run_until_complete(main())

• 使用连接池

连接池是一种用于管理数据库连接的技术，它可以提高应用程序的性能和可扩展性。pymssql支持使用连接池来管理数据库连接。使用连接池可以减少连接的创建和销毁开销，并提供连接的复用，从而提高应用程序的性能和可扩展性。

1.  
   from pymssql import pool
2.  
    
3.  
   # 创建连接池
4.  
   pool = pool.ConnectionPool(server='server_name', database='database_name', user='username', password='password', max_connections=5)
5.  
    
6.  
   # 从连接池获取连接
7.  
   conn = pool.get_connection()
8.  
    
9.  
   # 执行数据库操作
10.  
    cursor = conn.cursor()
11.  
    cursor.execute("SELECT * FROM your_table")
12.  
    result = cursor.fetchall()
13.  
     
14.  
    # 处理查询结果
15.  
    for row in result:
16.  
    # 处理数据
17.  
     
18.  
    # 关闭游标和连接
19.  
    cursor.close()
20.  
    conn.close()

这篇好文章是转载于：学新通技术网