datetime.strptime解析SQLite时间字符串报错：存在未转换微秒数据

问题现象 #

从SQLite数据库读取存储为文本类型的车辆入场时间EnterTime，使用datetime.strptime转换时出现如下报错：

2023-02-06 16:07:46.640395
Traceback (most recent call last):
  File "/media/pi/6134-E775/payment.py", line 32, in <module>
    entrance_time = datetime.strptime(entrance_time_str, "%Y-%m-%d %H:%M:%S")
  File "/usr/lib/python3.7/_strptime.py", line 577, in _strptime_datetime
    tt, fraction, gmtoff_fraction = _strptime(data_string, format)
  File "/usr/lib/python3.7/_strptime.py", line 362, in _strptime
    data_string[found.end():])
ValueError: unconverted data remains: .640395

问题原因 #

你的时间字符串包含微秒部分（.640395），但使用的格式串"%Y-%m-%d %H:%M:%S"仅匹配到秒级，未处理微秒，导致无法完全转换整个字符串。

解决方案 #

修改datetime.strptime的格式参数，添加%f（用于匹配6位微秒数字）：

修改后的关键代码片段 #

# 获取入场时间字符串后，用包含微秒的格式转换
entrance_time_str = cursor.fetchone()[0]
print(entrance_time_str)
# 新增%f匹配微秒部分
entrance_time = datetime.strptime(entrance_time_str, "%Y-%m-%d %H:%M:%S.%f")

完整修改后的代码 #

import sqlite3
import smtplib
from datetime import datetime, timedelta

# Connect to the database
conn = sqlite3.connect("py.db")

# Get current time
current_time = datetime.now()

# Define the carplate number
carplate = "SJJ4649G"

# Check if the carplate already exists in the database
cursor = conn.cursor()
query = "SELECT * FROM entrance WHERE carplate = ?"
cursor.execute(query, (carplate,))
result = cursor.fetchall()

# If the carplate already exists, send an email
if len(result) > 0:
    # Get the email address from the gov_info table
    query = "SELECT email FROM gov_info WHERE carplate = ?"
    cursor.execute(query, (carplate,))
    email_result = cursor.fetchall()

    # Get the entrance time from the entrance table
    query = "SELECT EnterTime FROM entrance WHERE carplate = ?"
    cursor.execute(query, (carplate,))
    entrance_time_str = cursor.fetchone()[0]
    print(entrance_time_str)
    # 修复格式串，添加%f匹配微秒
    entrance_time = datetime.strptime(entrance_time_str, "%Y-%m-%d %H:%M:%S.%f")

    # Calculate the cost
    delta = current_time - entrance_time
    cost = delta.total_seconds() / 3600 * 10  # 10 is the hourly rate

    # Email details
    email = "testcsad69@gmail.com"
    password = "ufwdiqcfepqlepsn"
    send_to = email_result[0][0]
    subject = "Parking Fees"
    message = f"The cost for parking the car with plate number {carplate} is ${cost:.2f}. The entrance time was {entrance_time} and the current time is {current_time}."

    # Send the email
    smtp = smtplib.SMTP('smtp.gmail.com', 587)
    smtp.ehlo()
    smtp.starttls()
    smtp.login(email, password)
    smtp.sendmail(email, send_to, f"Subject: {subject}\n\n{message}")
    smtp.quit()

# If the carplate does not exist, insert it into the database
else:
    query = "INSERT INTO entrance (carplate, EnterTime) VALUES (?, ?)"
    cursor.execute(query, (carplate, current_time))
    conn.commit()

# Close the connection
cursor.close()
conn.close()

额外优化建议 #

如果后续希望避免这类格式问题，可以将SQLite中EnterTime字段的类型改为TIMESTAMP，插入时直接存入datetime对象，读取时SQLite会自动转换为datetime类型，无需手动调用strptime。

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