如何实现Excel→Python→Microsoft Access的数据迁移？求技术修复方案

The core issue here is that you're manually constructing your SQL query by concatenating tuples and strings, which leads to messy, invalid syntax. Plus, this approach is vulnerable to SQL injection and hard to maintain. Let's fix this properly using parameterized queries (the recommended way to execute SQL with pyodbc), and clean up your code along the way.

Step 1: Simplify Excel Data Reading #

Your current code has redundant variable assignments (like b2 = a2 = sheet['A2'] which doesn't make sense). Let's read the row values in a cleaner, more scalable way:

import pyodbc
import openpyxl

# Load Excel file and get active sheet
path = r'C:\Access_Test.xlsx'  # Use raw strings to avoid escape character issues
wb = openpyxl.load_workbook(path)
sheet = wb.active

# Read row 2 values (columns A to M) - adjust the column range if needed
row_values = [sheet.cell(row=2, column=col).value for col in range(1, 14)]
# row_values will be a list: [Case2, Last, First, Initial Intake, ..., Marital]

Step 2: Use Parameterized SQL Query #

Instead of manually building the VALUES clause, use ? as placeholders for your parameters. pyodbc will handle all formatting, type conversion, and escaping automatically:

# Access database connection setup
driver = '{Microsoft Access Driver (*.mdb, *.accdb)}'
filepath = r'C:\Users\Db_Mngr\Desktop\PythonTests\Microsoft Studio Projects\HUD Report-2001-Copy For Python.mdb'

cnxn = pyodbc.connect(driver=driver, dbq=filepath, autocommit=True)
crsr = cnxn.cursor()

# Define your INSERT statement with placeholders matching the number of columns
insert_query = """
INSERT INTO Python_Test(
    [Case2], [Last], [First], [Initial Intake], [Intake], 
    [Age], [Gender], [Ethnic], [Race], [DOB], 
    [SSN], [Educ Lvl], [Marital]
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
"""

# Execute the query with your row values
crsr.execute(insert_query, row_values)

# Optional: If you need to insert multiple rows later, use executemany()
# For example:
# rows_to_insert = []
# for row in sheet.iter_rows(min_row=2, values_only=True):
#     rows_to_insert.append(row[:13])  # Take first 13 columns
# crsr.executemany(insert_query, rows_to_insert)

# Cleanup connections
crsr.close()
cnxn.close()

Why This Works #

• No more formatting errors: pyodbc automatically converts Python types (dates, integers, strings) to Access-compatible formats, so you don't have to worry about quoting or syntax mismatches.
• Security: Parameterized queries eliminate the risk of SQL injection, which is critical if your Excel data might contain untrusted input.
• Maintainability: The code is cleaner and easier to adjust if you need to add/remove columns later.

Quick Checks #

• Make sure the order of values in row_values exactly matches the order of columns listed in your INSERT statement.
• If you're inserting multiple rows, use executemany() instead of looping execute()—it's far more efficient.
• Using raw strings (r'file/path') for file paths avoids headaches with backslash escape characters.

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