python中的pymssql操作MSSQL数据库

大家好，欢迎来到IT知识分享网。

pymssql是 Python 中用于连接和操作 Microsoft SQL Server 数据库的库之一。它是一个轻量级的库，为 Python 提供了与 SQL Server 进行通信的简单而有效的方法。以下是关于 `pymssql` 库的一些详细介绍：

特点：
1. Pythonic 接口：`pymssql` 提供了一种 Pythonic 的接口，使得连接和操作 SQL Server 数据库变得简单直观。
2. 轻量级：该库的实现相对较轻量，因此加载和使用它不会增加太多额外的开销。
3. 跨平台：`pymssql` 在多个平台上都能够运行，包括 Windows、Linux 和 macOS 等。

主要功能：
1. 连接数据库：`pymssql` 允许你连接到 Microsoft SQL Server 数据库实例。
2. 执行 SQL 语句：通过 `pymssql`，你可以执行 SQL 查询、插入、更新和删除等操作。
3. 处理事务：支持事务处理，可以使用 `commit()` 和 `rollback()` 方法来提交或回滚事务。
4. 支持存储过程：可以执行存储过程，并获取输出参数。
5. 处理结果集：能够获取 SQL 查询的结果集，并对其进行处理。
6. 连接池支持：支持使用连接池，提高连接的复用性和效率。

注意事项：
1. 参数化查询：尽可能使用参数化查询，以防止 SQL 注入攻击。
2. 错误处理：在执行 SQL 查询时，应该适当地处理异常情况。
3. 资源释放：执行完 SQL 查询后，应该及时关闭游标和连接，以释放资源。

安装pymssql库   pip install pymssql

• 插入、更新和删除数据

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

import pymssql # 连接参数 server = 'server_name' database = 'database_name' username = 'username' password = 'password' # 建立连接 conn = pymssql.connect(server=server, database=database, user=username, password=password) # 创建游标对象 cursor = conn.cursor() # 执行SQL查询 cursor.execute("SELECT * FROM your_table") # 获取查询结果 result = cursor.fetchall() # 遍历结果 for row in result: print(row) # 插入数据 insert_query = "INSERT INTO your_table (column1, column2) VALUES (%s, %s)" insert_data = ('value1', 'value2') cursor.execute(insert_query, insert_data) # 更新数据 update_query = "UPDATE your_table SET column1 = %s WHERE id = %s" update_data = ('new_value', 1) cursor.execute(update_query, update_data) #参数化查询 # 删除数据 delete_query = "DELETE FROM your_table WHERE id = %s" delete_data = (1,) cursor.execute(delete_query, delete_data) # 提交事务 conn.commit() # 关闭游标 cursor.close() 

•  管理事务

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

# 创建游标对象 cursor = conn.cursor() try: # 开始事务 conn.begin() # 执行数据库操作 cursor.execute("INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2')") cursor.execute("UPDATE your_table SET column1 = 'new_value' WHERE id = 1") # 提交事务 conn.commit() except Exception as e: # 回滚事务 conn.rollback() print("Error:", e) # 关闭游标 cursor.close() 

• 查询结果处理

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

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

•  处理大型结果集

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

# 创建游标对象 cursor = conn.cursor() # 执行查询 cursor.execute("SELECT column1, column2 FROM your_table") # 获取一条记录 row = cursor.fetchone() while row: # 处理数据 print(row) # 获取下一条记录 row = cursor.fetchone() # 关闭游标 cursor.close() 

•  批量插入数据

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

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

•  存储过程调用

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

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

•  分页查询

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

# 定义分页参数 page_size = 10 page_number = 1 # 执行分页查询 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: # 处理数据 

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

• 处理数据库连接错误

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

import pymssql try: conn = pymssql.connect(server='server_name', database='database_name', user='username', password='password') # 连接成功，执行数据库操作 cursor = conn.cursor() # 执行查询、插入、更新等操作 # ... conn.commit() cursor.close() conn.close() except pymssql.OperationalError as e: # 处理连接错误 print("Connection Error:", e) 

import pymssql try: # 连接数据库 conn = pymssql.connect(server='server_name', database='database_name', user='username', password='password') # 执行数据库操作 cursor = conn.cursor() cursor.execute("SELECT column1, column2 FROM your_table") result = cursor.fetchall() # 处理查询结果 for row in result: # 处理数据 except pymssql.Error as e: print("Database Error:", e) finally: # 关闭连接 if conn: conn.close() 

• 获取查询结果的列信息

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

# 创建游标对象 cursor = conn.cursor() # 执行查询 cursor.execute("SELECT column1, column2 FROM your_table") # 获取列名 column_names = [column[0] for column in cursor.description] # 获取列类型 column_types = [column[1] for column in cursor.description] # 处理查询结果 result = cursor.fetchall() for row in result: for name, value in zip(column_names, row): print(f"{name}: {value}") # 关闭游标 cursor.close() 

•  处理查询结果中的NULL值

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

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' # 处理数据 

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

假设我们有一个名为 CalculateSum 的存储过程，该过程接受两个整数作为输入，并将它们相加，然后将结果返回。我们将使用 Python 脚本执行该存储过程，并获取输出参数（即求和结果）。
首先执行存储过程 CalculateSum，并将参数传递给它。然后，我们执行一个额外的 SELECT 查询来获取输出参数的值，并将其打印出来。

CREATE PROCEDURE CalculateSum @param1 INT, @param2 INT, @sum INT OUTPUT AS BEGIN SET @sum = @param1 + @param2 END 

import pymssql # 连接数据库 conn = pymssql.connect(server='your_server_name', user='your_username', password='your_password', database='your_database_name') # 创建游标对象 cursor = conn.cursor() # 执行存储过程 param1 = 5 param2 = 10 output_param = None # 初始化输出参数 cursor.execute("EXEC CalculateSum @param1=%s, @param2=%s, @sum OUTPUT", (param1, param2, output_param)) # 获取输出参数的值 cursor.execute("SELECT @sum") output_param = cursor.fetchone()[0] # 输出结果 print("Sum:", output_param) # 关闭游标和连接 cursor.close() conn.close() 

•  批量更新数据

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

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

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

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

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

•  异步操作

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

import asyncio import pymssql async def main(): # 创建异步连接 conn = await pymssql.connect(server='server_name', database='database_name', user='username', password='password', asynchronous=True) # 创建异步游标 cursor = conn.cursor(as_dict=True) # 执行异步查询 await cursor.execute("SELECT * FROM your_table") # 获取结果 result = await cursor.fetchall() # 处理查询结果 for row in result: # 处理数据 # 关闭游标和连接 await cursor.close() await conn.close() # 创建事件循环并运行异步任务 loop = asyncio.get_event_loop() loop.run_until_complete(main()) 

• 使用连接池

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

from pymssql import pool # 创建连接池 pool = pool.ConnectionPool(server='server_name', database='database_name', user='username', password='password', max_connections=5) # 从连接池获取连接 conn = pool.get_connection() # 执行数据库操作 cursor = conn.cursor() cursor.execute("SELECT * FROM your_table") result = cursor.fetchall() # 处理查询结果 for row in result: # 处理数据 # 关闭游标和连接 cursor.close() conn.close()