使用df.to_sql()保存微笑检测数据至SQLite3数据库为空问题求助

1. 核心问题排查方向 #

SQLite 常见写入失败原因 #

• 未执行提交操作：SQLite默认开启事务，插入数据后如果不调用conn.commit()，所有操作都会回滚，数据库不会保存数据。CSV是直接写入文件，不需要提交，所以正常。
• 表未正确创建：如果初始化时没创建目标表，插入数据时会静默失败（默认SQLite不会主动抛出错误，除非开启异常捕获）。
• 数据库连接提前关闭：如果在插入数据前就关闭了连接，或者连接对象被提前销毁，数据无法写入。
• 字段不匹配：插入的字段数量、类型和表结构不对应，导致插入失败但未被捕获。

MySQL 常见写入失败原因 #

• 连接认证失败：用户名、密码、主机地址配置错误，导致无法连接数据库，自然无法写入。
• 权限不足：当前数据库用户没有INSERT权限，或者没有创建表的权限。
• 字符集不兼容：插入的中文位置信息字符集和数据库/表的字符集不匹配，导致插入失败。
• 未提交事务：MySQL默认自动提交，但如果手动开启了事务，同样需要执行commit()。

2. 修复代码的关键步骤 #

针对 SQLite 的修复示例 #

import sqlite3
from datetime import datetime

# 初始化数据库连接（保持持久化，不要频繁创建销毁）
conn = sqlite3.connect('smile_details.db')
cursor = conn.cursor()

# 1. 先创建表（如果不存在），确保表结构和CSV字段对应
cursor.execute('''
CREATE TABLE IF NOT EXISTS smile_records (
    timestamp TEXT PRIMARY KEY,
    happiness_score REAL,
    capture_location TEXT
)
''')
conn.commit()  # 创建表后需提交

# 写入数据库的核心函数
def write_to_db(timestamp, score, location):
    try:
        cursor.execute('''
        INSERT INTO smile_records (timestamp, happiness_score, capture_location)
        VALUES (?, ?, ?)
        ''', (timestamp, score, location))
        conn.commit()  # 关键：插入后必须提交
    except sqlite3.Error as e:
        print(f"数据库写入错误: {e}")
        conn.rollback()  # 出错时回滚事务

# 示例调用（替换成你的采集逻辑）
current_time = datetime.now().isoformat()
write_to_db(current_time, 0.85, "笔记本摄像头")

# 程序结束前再关闭连接（不要提前关闭）
# conn.close()

针对 MySQL 的修复示例 #

import mysql.connector
from mysql.connector import Error
from datetime import datetime

def connect_mysql():
    try:
        conn = mysql.connector.connect(
            host='localhost',
            database='smile_db',
            user='your_username',
            password='your_password'
        )
        if conn.is_connected():
            cursor = conn.cursor()
            # 创建表，指定兼容中文的字符集
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS smile_records (
                timestamp VARCHAR(50) PRIMARY KEY,
                happiness_score FLOAT,
                capture_location VARCHAR(100)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            ''')
            return conn, cursor
    except Error as e:
        print(f"MySQL连接错误: {e}")
    return None, None

def write_to_mysql(timestamp, score, location):
    conn, cursor = connect_mysql()
    if conn and cursor:
        try:
            query = '''
            INSERT INTO smile_records (timestamp, happiness_score, capture_location)
            VALUES (%s, %s, %s)
            '''
            cursor.execute(query, (timestamp, score, location))
            conn.commit()
        except Error as e:
            print(f"MySQL写入错误: {e}")
            conn.rollback()
        finally:
            cursor.close()
            conn.close()

# 示例调用
current_time = datetime.now().isoformat()
write_to_mysql(current_time, 0.85, "笔记本摄像头")

3. 额外调试技巧 #

• 强制异常捕获：在所有数据库操作代码块中添加try-except，捕获SQLite/MySQL的错误信息，这是定位问题的核心（很多失败是静默的，看不到错误）。
• 手动验证数据库：用SQLite Studio（SQLite）或Navicat（MySQL）打开数据库，查看表是否存在，手动执行插入语句测试。
• 检查数据类型：确保CSV中的数据类型（比如幸福指数是浮点数）和数据库表的字段类型一致，避免类型转换失败。
• 避免重复主键：如果用时间戳作为主键，确保时间戳精度足够（比如到毫秒），避免重复插入导致失败。

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