将谷歌表格数据导入SQLite时遇sqlite3.OperationalError语法错误求助

问题现象 #

尝试将Google Sheets中的字符串值（示例：SU 10074 31063）插入SQLite数据库，已定义字段为TEXT类型，但执行时抛出错误：

sqlite3.OperationalError: near 10074: syntax error

错误原因 #

核心问题是直接用字符串格式化拼接SQL语句，导致字符串值未被引号包裹。例如执行tbl1.add_info(2,arr[2])时，生成的SQL语句为：

INSERT INTO Converting VALUES(2,SU 10074 31063)

SQLite会将SU识别为未知命令，10074成为语法错误触发点。同时这种写法存在SQL注入风险。

解决方案 #

使用SQLite的参数化查询（占位符?）传递参数，让数据库自动处理字符串的引号与转义，同时修正代码中的其他潜在问题：

修复后的完整代码 #

import gspread
import sqlite3
from oauth2client.service_account import ServiceAccountCredentials 

scope = ['https://spreadsheets.google.com/feeds',
        'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name("secret key.json", scope)
gc = gspread.authorize(credentials)

wks = gc.open('File').sheet1
arr = wks.col_values(8)

class Tables():
    def __init__(self, table_name, column_name, id_number=0, national_grid_ref=""):
        self.id_number = id_number
        self.national_grid_ref = national_grid_ref
        self.table_name = table_name
        self.column_name = column_name
        # 单次建立数据库连接，避免重复创建
        self.connection = sqlite3.connect("Conversion.db")
        self.cursor = self.connection.cursor()
        # 处理含特殊字符的列名，用双引号包裹
        self.cursor.execute(f'''CREATE TABLE IF NOT EXISTS {self.table_name} (
            id INTEGER PRIMARY KEY,
            "{self.column_name}" TEXT
        );''')
    
    def load_info(self, id_number):
        # 参数化查询单条记录
        self.cursor.execute(f'''
            SELECT * FROM {self.table_name}
            WHERE id = ?
        ''', (id_number,)) 
        results = self.cursor.fetchone()
        if results:
            self.id_number = id_number
            self.national_grid_ref = results[1]
            print(f"From {self.table_name}, value {self.id_number} is {self.national_grid_ref}")
        else:
            print(f"No record found for id {id_number} in {self.table_name}")

    def add_info(self, id_number, value):
        # 参数化插入值，明确指定列名避免结构异常
        self.cursor.execute(f'''
            INSERT INTO {self.table_name} (id, "{self.column_name}")
            VALUES(?, ?)
        ''', (id_number, value))
        self.connection.commit()
        print(f"Added record: id={id_number}, value={value}")

# 定义表名和列名
table_name = "Converting"
column_name = arr[0]

# 实例化单个数据表对象
tbl1 = Tables(table_name, column_name)

print(column_name)
print(arr[2])

# 插入指定记录
tbl1.add_info(2, arr[2])

# 查询已插入的记录
tbl1.load_info(2)

# 关闭数据库连接
tbl1.connection.close()

关键修复点 #

1. 参数化查询：用?作为值的占位符，将参数以元组形式传递给execute方法，彻底解决字符串语法错误与SQL注入风险。
2. 列名处理：用双引号包裹列名（"{self.column_name}"），兼容列名含空格或特殊字符的场景。
3. 连接优化：单次建立数据库连接，避免重复实例化类时创建多个连接。
4. 查询逻辑优化：用fetchone()获取单条查询结果，符合按ID查询单条记录的业务场景。

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