Python如何将日期时间转换为时间戳 解决MQTT数据入库解析异常

现有代码的核心问题 #

• SQL参数使用错误：主流数据库驱动的参数化查询仅支持对字段值做占位替换，表名、字段名属于SQL语法标识符，不支持通过占位符传参。当前代码将表名、字段名也作为参数传入，驱动会自动给这类值包裹单引号，最终生成的SQL存在语法错误，根本无法执行到类型校验环节。
• 时间解析逻辑脆弱：当前靠空格拆分字符串硬拼接时间格式，一旦MQTT推送的字段顺序、格式出现微小变动，就会拼出非法的时间字符串，触发类型匹配失败。
• 异常处理无日志：当前except分支直接回滚，没有打印真实报错信息，根本无法区分是SQL语法错误、解析值非法还是类型不匹配。

时间字段适配入库的正确实现 #

绝大多数关系型数据库的timestamp类型字段，不需要手动将Python时间转换为数据库内部的整数时间戳，只要传入符合格式的时间字符串、或Python原生datetime对象，数据库驱动会自动完成类型映射，可靠性远高于手动拼接字符串。具体实现分两步：

1. 规范时间解析流程
   替换原有硬拼字符串的逻辑，用标准库的时间解析模块将原始字符串转成原生datetime对象：

   from datetime import datetime
   # 替换原有硬拼接time的代码段
   raw_time_str = a[1] + " " + a[2].replace('"','')
   # 根据实际拿到的时间字符串调整解析格式，常见格式示例：
   # 比如解析"2024-05-20 12:30:45" 对应的格式串为"%Y-%m-%d %H:%M:%S"
   parsed_time = datetime.strptime(raw_time_str, "%Y-%m-%d %H:%M:%S")
   

   若解析后的时间带时区、毫秒等特殊格式，仅需对应调整strptime的格式串即可，避免靠字符串拼接凑格式导致的格式不匹配。
   若数据库的timestamp字段要求存储Unix整数时间戳，仅需在解析完成后调用int(parsed_time.timestamp())转成整数传入即可，无需额外处理。
2. 修正SQL拼接逻辑
   表名、字段名这类标识符，在经过合法性校验（避免SQL注入）后直接拼入SQL语句，仅在值的位置使用参数占位符。修正后的入库代码示例：

   def to_database(time, value, table):
       cursor = connection.cursor()
       # 基础合法性校验：仅允许表名包含字母、数字、下划线，规避SQL注入风险
       if not table.replace("_", "").isalnum():
           print(f"拦截非法表名：{table}")
           return
       try:
           # 标识符直接拼接（按数据库语法调整引号，比如MySQL用反引号包标识符，PostgreSQL用双引号）
           sql = f"INSERT INTO `{table}` (`date`, `value`) VALUES (%s, %s)"
           # 直接传入解析好的datetime对象、转成int的采集值，无需手动转换时间格式
           cursor.execute(sql, (time, int(value)))
           connection.commit()
           print("数据写入成功")
       except Exception as e:
           connection.rollback()
           # 打印真实报错信息，便于定位具体问题
           print(f"写入失败，错误原因：{str(e)}")
   

额外注意事项 #

• 解析时间时要统一时区：如果MQTT推送的时间带时区标记，解析时要显式指定时区，避免数据库存储时间和实际采集时间存在时区偏差。
• 不要直接用裸字符串拼接无校验的表名、字段名，否则会存在SQL注入风险。

内容的提问来源于stack exchange，提问作者Tomáš Mejzr