如何让Python脚本仅为SQL插入语句的字符串值添加单引号？

现有一段从Excel读取数据生成SQL Server插入语句的Python脚本，脚本可正常运行，但生成的INSERT语句中字符串值未被单引号包裹，导致SQL语法错误。

原Python脚本 #

excel_file = 'prod_data.xlsx'
df = pd.read_excel(excel_file)

systemDesign_columns = ['dateCreated', 'systemSecure', 'isUsingFirewall','systemStartYear', 'firstName', 'lastName', 'systemDescription'] 

insert_statements = []
for _, row in df.iterrows():
    systemDesign_values = [str(row[column]) for column in systemDesign_columns]
    systemDesign_values_str = ', '.join(systemDesign_values)

    commands = f"""
    
    INSERT INTO dbo.systemDesign (dateCreated, systemSecure, isUsingFirewall, systemStartYear, firstName, lastName, systemDescription) VALUES ({systemDesign_values_str});
    
    """
    insert_statements.append(commands)
    
for statement in insert_statements:
    print(statement)

生成的错误INSERT语句示例 #

INSERT INTO dbo.systemDesign (dateCreated, systemSecure, isUsingFirewall,systemStartYear, firstName, lastName, systemDescription) VALUES (2023-06-01 00:00:00, 1, 0, 2022, Jim, Williams, A user network);

解决方案 #

方法1：明确指定字符串字段，手动添加单引号 #

先定义需要加单引号的字段列表，遍历字段时判断是否属于该列表，是则用单引号包裹值，同时转义值中的单引号避免语法错误。

excel_file = 'prod_data.xlsx'
df = pd.read_excel(excel_file)

systemDesign_columns = ['dateCreated', 'systemSecure', 'isUsingFirewall','systemStartYear', 'firstName', 'lastName', 'systemDescription']
# 指定需要加单引号的字段（字符串、日期类型都需要）
string_date_columns = ['firstName', 'lastName', 'systemDescription', 'dateCreated']

insert_statements = []

for _, row in df.iterrows():
    systemDesign_values = []
    for column in systemDesign_columns:
        value_str = str(row[column])
        if column in string_date_columns:
            # 转义单引号：将'替换为''，避免SQL语法错误
            escaped_value = value_str.replace("'", "''")
            systemDesign_values.append(f"'{escaped_value}'")
        else:
            systemDesign_values.append(value_str)
    systemDesign_values_str = ', '.join(systemDesign_values)

    commands = f"""
    INSERT INTO dbo.systemDesign (dateCreated, systemSecure, isUsingFirewall, systemStartYear, firstName, lastName, systemDescription) VALUES ({systemDesign_values_str});
    """
    insert_statements.append(commands)

for statement in insert_statements:
    print(statement)

方法2：根据值的类型自动判断 #

通过判断值的类型（字符串、日期）来决定是否添加单引号，适合字段类型明确的场景：

from datetime import datetime

excel_file = 'prod_data.xlsx'
df = pd.read_excel(excel_file)

systemDesign_columns = ['dateCreated', 'systemSecure', 'isUsingFirewall','systemStartYear', 'firstName', 'lastName', 'systemDescription']
insert_statements = []

for _, row in df.iterrows():
    systemDesign_values = []
    for column in systemDesign_columns:
        value = row[column]
        # 字符串或日期类型的值需要加单引号
        if isinstance(value, str) or isinstance(value, datetime):
            value_str = str(value).replace("'", "''")
            systemDesign_values.append(f"'{value_str}'")
        else:
            systemDesign_values.append(str(value))
    systemDesign_values_str = ', '.join(systemDesign_values)

    commands = f"""
    INSERT INTO dbo.systemDesign (dateCreated, systemSecure, isUsingFirewall, systemStartYear, firstName, lastName, systemDescription) VALUES ({systemDesign_values_str});
    """
    insert_statements.append(commands)

for statement in insert_statements:
    print(statement)

更安全的方案：使用参数化查询 #

直接拼接SQL语句存在SQL注入风险，推荐使用参数化查询，数据库驱动会自动处理类型转换和引号问题：

import pyodbc
import pandas as pd

excel_file = 'prod_data.xlsx'
df = pd.read_excel(excel_file)

# 连接SQL Server，替换为你的数据库信息
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=你的服务器地址;DATABASE=你的数据库名;UID=用户名;PWD=密码')
cursor = conn.cursor()

# 参数化插入模板，用?作为占位符
insert_sql = """
INSERT INTO dbo.systemDesign (dateCreated, systemSecure, isUsingFirewall, systemStartYear, firstName, lastName, systemDescription)
VALUES (?, ?, ?, ?, ?, ?, ?)
"""

# 从DataFrame提取对应列的数据
data = df[systemDesign_columns].values.tolist()

# 批量插入数据
cursor.executemany(insert_sql, data)
conn.commit()

# 关闭连接
cursor.close()
conn.close()

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