Flask API开发中SQLite无法保存数据，查询返回空列表求助

问题根源及修复方案 #

• 查询逻辑缺失：get_links函数直接调用c.fetchall()，但未先执行查询数据的SQL语句，导致返回空列表。必须先执行SELECT语句再获取结果。
• SQL注入风险：数据插入使用字符串拼接构造SQL语句，存在安全漏洞，应改用参数化查询。
• 异常处理语法错误：except sqlite3.DatabaseError or sqlite3.DataError as e写法无效，需用元组包含多个异常类型。

修正后的代码 #

数据操作代码

import sqlite3
# 初始化数据库连接与游标（需确保在模块加载时执行）
conn = sqlite3.connect('your_db.db', check_same_thread=False)  # Flask多线程环境需添加check_same_thread=False
c = conn.cursor()
# 确保表结构存在
c.execute('''CREATE TABLE IF NOT EXISTS ping (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    links TEXT NOT NULL
)''')
conn.commit()

def data_insert(link):
    try:
        # 参数化查询避免SQL注入
        c.execute("INSERT INTO ping(links) VALUES (?);", (link,))
        conn.commit()
        return "success"
    except sqlite3.DatabaseError as e:
        print("AN ERROR HAS OCCURED")
        print(chalk.red(e))
        conn.rollback()  # 出错时回滚事务
        return "an internal error has occured."

def get_links():
    try:
        # 先执行查询语句
        c.execute("SELECT * FROM ping;")
        links = c.fetchall()
        print(f"links:{links}")
        return links
    except (sqlite3.DatabaseError, sqlite3.DataError) as e:
        print(chalk.red(e))
        return "an internal error has occured."

Flask路由代码（可保留原逻辑）

@app.route("/submit-url")
def main():
    url = request.args.get("url")
    if url is None:
        return "please enter url",404
    else:
        output = db.data_insert(url)
        links = db.get_links()
        print(links)
        return output

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