硬编码的 SQL 查询以及高度耦合的数据访问代码会导致难以扩展或切换数据库，并且常常会产生重复的逻辑。
Copilot Chat 可以建议实现仓储模式或数据访问层，以抽象数据库交互，使代码更具模块化并减少冗余。它还能帮助将 SQL 查询重构为优化后的版本，从而提升性能和可维护性。
注意
本文中描述的响应仅为示例。Copilot Chat 的响应具有非确定性，因此您可能会得到与此处显示的不同答案。
示例情景
这段 Python 代码连接到 SQLite 数据库，检索用户记录并返回用户数据。但它没有抽象数据库连接逻辑，并且使用了硬编码的查询，容易受到 SQL 注入攻击。
import sqlite3
def get_user_by_id(user_id):
conn = sqlite3.connect('database.db')
cursor = conn.cursor()
cursor.execute(f"SELECT display_name FROM users WHERE id = {user_id}")
user = cursor.fetchone()
conn.close()
return user
示例提示 1
您可以先向 Copilot 提一个关于如何改进代码的通用问题。
How can I improve this code to make it safe and easier to update and expand? List possible improvements but don't show revised code.
import sqlite3
def get_user_by_id(user_id):
conn = sqlite3.connect('database.db')
cursor = conn.cursor()
cursor.execute(f"SELECT display_name FROM users WHERE id = {user_id}")
user = cursor.fetchone()
conn.close()
return user
How can I improve this code to make it safe and easier to update and expand? List possible improvements but don't show revised code.
How can I improve this code to make it safe and easier to update and expand? List possible improvements but don't show revised code.
示例响应 1
Copilot 提出了多项改进代码的建议
- 抽象数据库连接细节。
- 使用参数化查询以防止 SQL 注入。
- 使用上下文管理器，确保在出现错误时数据库连接能够正确关闭。
- 添加错误处理。
示例提示 2
您可以使用对第一个提示的响应来编写更具体的提示。
Rewrite this code to make it more scalable and easier to maintain. Use a context manager. Avoid hardcoded SQL queries and tightly coupled data access code. Instead, use a repository pattern to abstract database interactions and make the code more modular and reusable. Where possible optimize the code to improve performance. Include error trapping, and make sure the code is not vulnerable to SQL injection.
import sqlite3
def get_user_by_id(user_id):
conn = sqlite3.connect('database.db')
cursor = conn.cursor()
cursor.execute(f"SELECT display_name FROM users WHERE id = {user_id}")
user = cursor.fetchone()
conn.close()
return user
Rewrite this code to make it more scalable and easier to maintain. Use a context manager. Avoid hardcoded SQL queries and tightly coupled data access code. Instead, use a repository pattern to abstract database interactions and make the code more modular and reusable. Where possible optimize the code to improve performance. Include error trapping, and make sure the code is not vulnerable to SQL injection.
Rewrite this code to make it more scalable and easier to maintain. Use a context manager. Avoid hardcoded SQL queries and tightly coupled data access code. Instead, use a repository pattern to abstract database interactions and make the code more modular and reusable. Where possible optimize the code to improve performance. Include error trapping, and make sure the code is not vulnerable to SQL injection.
示例响应 2
Copilot 建议将代码拆分为多个各司其职的文件，并提供了返回用户详细信息的示例代码。
所建议的更改实现了仓储模式，并使用参数化查询以避免 SQL 注入。
# database.py
import sqlite3
class Database:
def __init__(self, db_path):
self.db_path = db_path
def __enter__(self):
self.conn = sqlite3.connect(self.db_path)
self.cursor = self.conn.cursor()
return self
def __exit__(self, exc_type, exc_val, exc_tb):
if exc_type or exc_val or exc_tb:
self.conn.rollback()
else:
self.conn.commit()
self.conn.close()
# user_repository.py
class UserRepository:
def __init__(self, db):
self.db = db
def get_user_by_id(self, user_id):
try:
self.db.cursor.execute("SELECT display_name FROM users WHERE id = ?", (user_id,))
user = self.db.cursor.fetchone()
return user
except sqlite3.Error as e:
print(f"An error occurred: {e}")
return None
# main.py
from database import Database
from user_repository import UserRepository
def get_user_by_id(user_id):
with Database('database.db') as db:
user_repo = UserRepository(db)
return user_repo.get_user_by_id(user_id)
# Example usage
if __name__ == "__main__":
user = get_user_by_id(1)
if user:
print(user)
else:
print("User not found or an error occurred.")
重构后的代码在多个方面优于原始代码
- 代码现在实现了一个基础的数据访问层。
Database类负责与 SQLite 数据库的连接，并通过实现__enter__与__exit__方法遵循上下文管理器协议。这确保了对数据库连接的正确管理，包括事务提交和连接关闭。UserRepository类封装了访问用户数据的逻辑。- 查询的参数已使用占位符进行参数化，以防止 SQL 注入。
- 错误会被捕获，并将详细信息打印到控制台。