如何将代码中的SQLite原生语句迁移至独立SQL文件并调用？

Great question! This approach is totally feasible and actually a fantastic practice for separating SQL logic from your application code—making your SQL easier to edit, version control, and maintain, especially as queries grow more complex. Here's a step-by-step breakdown of how to implement it:

1. Set up your SQL file structure #

First, create a dedicated directory (like sqlstatements) to store all your standalone SQL files. For your example:

• Create sqlstatements/quantity.sql
• Inside the file, add your raw SQL query:

  INSERT OR IGNORE INTO quantity VALUES (?,?,?)
  

  Note: Avoid trailing semicolons unless you're using executescript() for multi-statement queries—execute() expects a single statement without a final semicolon.

2. Modify your Python code to load SQL from files #

The key thing to remember: cursor.execute() requires a SQL string as its first argument, not a file path directly. So you'll need to add a helper function to read the contents of your SQL files.

Basic implementation #

def load_sql_query(file_path):
    """Load and return the contents of a SQL file."""
    with open(file_path, 'r', encoding='utf-8') as sql_file:
        # Strip leading/trailing whitespace to avoid unexpected syntax issues
        return sql_file.read().strip()

# Usage in your existing code
sql_query = load_sql_query('sqlstatements/quantity.sql')
read_cursor.execute(sql_query, row)

Improved path handling (avoid relative path bugs) #

To prevent issues with relative paths (e.g., when running your script from a different directory), use pathlib to reference the SQL directory relative to your script:

from pathlib import Path

# Define the SQL directory relative to this script's location
SQL_DIRECTORY = Path(__file__).parent / 'sqlstatements'

def load_sql_query(file_name):
    file_path = SQL_DIRECTORY / file_name
    with open(file_path, 'r', encoding='utf-8') as sql_file:
        return sql_file.read().strip()

# Usage stays clean
sql_query = load_sql_query('quantity.sql')
read_cursor.execute(sql_query, row)

3. Optional optimizations #

• Cache loaded queries: If you're reusing the same SQL query multiple times, cache it to avoid re-reading the file every time:

  _query_cache = {}
  
  def load_sql_query(file_name):
      if file_name not in _query_cache:
          file_path = SQL_DIRECTORY / file_name
          with open(file_path, 'r', encoding='utf-8') as sql_file:
              _query_cache[file_name] = sql_file.read().strip()
      return _query_cache[file_name]
  

• Clean up comments: If you add comments to your SQL files (e.g., -- This inserts a quantity record), you can add logic to strip them before executing the query.
• Batch load queries: For larger projects, load all SQL files in the directory into a dictionary on startup for easy access.

4. Key notes to avoid issues #

• Ensure the number of placeholders (?) in your SQL file matches the number of values in your row parameter—mismatches will throw errors.
• Use consistent encoding (UTF-8 is recommended) for all SQL files to avoid garbled text.
• For multi-statement queries (e.g., creating tables and inserting data in one go), use cursor.executescript() instead of execute(), and keep the semicolons in your SQL file.

内容的提问来源于stack exchange，提问作者Sam