Python提取Outlook日历空列表及pandas时间类型报错问题排查

我编写了如下Python代码，用于提取Outlook日历、展示已安排会议的所有参会人员列表，运行时遇到数据类型相关报错，且报错前打印appointments列表显示为空。

原始代码 #

import datetime as dt
import pandas as pd
import win32com.client

def get_calendar(begin,end):
    outlook = win32com.client.Dispatch('Outlook.Application').GetNamespace('MAPI')
    calendar = outlook.getDefaultFolder(9).Items
    calendar.IncludeRecurrences = True
    calendar.Sort('[Start]')
    restriction = "[Start] >= '" + begin.strftime('%m/%d/%Y') + "' AND [END] <= '" + end.strftime('%m/%d/%Y') + "'"
    calendar = calendar.Restrict(restriction)
    return calendar

def get_appointments(calendar,subject_kw = None,exclude_subject_kw = None, body_kw = None):
    if subject_kw == None:
        appointments = [app for app in calendar]    
    else:
        appointments = [app for app in calendar if subject_kw in app.subject]
    if exclude_subject_kw != None:
        appointments = [app for app in appointments if exclude_subject_kw not in app.subject]
    cal_subject = [app.subject for app in appointments]
    cal_start = [app.start for app in appointments]
    cal_end = [app.end for app in appointments]
    cal_body = [app.body for app in appointments]

    df = pd.DataFrame({'subject': cal_subject,
                       'start': cal_start,
                       'end': cal_end,
                       'body': cal_body})
    return df

def make_cpd(appointments):
    appointments['Date'] = appointments['start']
    appointments['Hours'] = (appointments['end'] - appointments['start']).dt.seconds/3600
    appointments.rename(columns={'subject':'Meeting Description'}, inplace = True)
    appointments.drop(['start','end'], axis = 1, inplace = True)
    summary = appointments.groupby('Meeting Description')['Hours'].sum()
    return summary

final = r"C:\Users\rcarmody\Desktop\Python\Accelerators\Outlook Output.xlsx" 

begin = dt.datetime(2021,1,1)
end = dt.datetime(2021,5,12)

print(begin)
print(end)

cal = get_calendar(begin, end)
appointments = get_appointments(cal, subject_kw = 'weekly', exclude_subject_kw = 'Webcast')
result = make_cpd(appointments)

result.to_excel(final)

首次报错信息 #

Traceback (most recent call last):
  File "C:\Users\Desktop\Python\Accelerators\outlook_meetings.py", line 50, in <module>
    result = make_cpd(appointments)
  File "C:\Users\Desktop\Python\Accelerators\outlook_meetings.py", line 34, in make_cpd
    appointments['Hours'] = (appointments['end'] - appointments['start']).dt.seconds/3600
  File "C:\Users\AppData\Roaming\Python\Python39\site-packages\pandas\core\generic.py", line 5461, in __getattr__
    return object.__getattribute__(self, name)
  File "C:\Users\rcarmody\AppData\Roaming\Python\Python39\site-packages\pandas\core\accessor.py", line 180, in __get__
    accessor_obj = self._accessor(obj)
  File "C:\Users\AppData\Roaming\Python\Python39\site-packages\pandas\core\indexes\accessors.py", line 494, in __new__
    raise AttributeError("Can only use .dt accessor with datetimelike values")
AttributeError: Can only use .dt accessor with datetimelike values
[Finished in 1.2s]

修改后新报错信息 #

Traceback (most recent call last):
  File "C:\Users\Desktop\Python\Accelerators\outlook_meetings.py", line 50, in <module>
    result = make_cpd(appointments)
  File "C:\Users\Desktop\Python\Accelerators\outlook_meetings.py", line 34, in make_cpd
    appointments['Hours'] = (appointments['end'] - appointments['start']) / pd.Timedelta(hours=1)
  File "C:\Users\\AppData\Roaming\Python\Python39\site-packages\pandas\core\ops\common.py", line 65, in new_method
    return method(self, other)
  File "C:\Users\AppData\Roaming\Python\Python39\site-packages\pandas\core\arraylike.py", line 113, in __truediv__
    return self._arith_method(other, operator.truediv)
  File "C:\Users\\AppData\Roaming\Python\Python39\site-packages\pandas\core\series.py", line 4998, in _arith_method
    result = ops.arithmetic_op(lvalues, rvalues, op)
  File "C:\Users\\AppData\Roaming\Python\Python39\site-packages\pandas\core\ops\array_ops.py", line 185, in arithmetic_op
    res_values = op(lvalues, rvalues)
  File "pandas\_libs\tslibs\timedeltas.pyx", line 1342, in pandas._libs.tslibs.timedeltas.Timedelta.__rtruediv__
numpy.core._exceptions.UFuncTypeError: ufunc 'true_divide' cannot use operands with types dtype('float64') and dtype('<m8[ns]')

两个报错的核心原因有两点：

1. 从Outlook COM接口拿到的start/end字段是COM时间对象，不是pandas可识别的datetime类型，无法直接使用.dt访问器或直接与Timedelta做运算
2. 日历过滤规则可能不生效，导致返回的appointments为空，进一步触发类型运算异常

第一步：排查日历为空问题 #

• 先移除过滤条件测试：调用get_appointments时先去掉subject_kw和exclude_subject_kw参数，打印返回的DataFrame的行数，确认是否可以正常拿到日历数据
• 修正过滤时间格式：不同地区的Outlook对Restrict语句的时间格式要求不同，如果你所在地区的日期格式是日/月/年，需要把strftime('%m/%d/%Y')改成strftime('%d/%m/%Y')
• 确认Outlook配置：确保本地Outlook客户端已正常登录，默认日历（getDefaultFolder(9)获取的是默认日历）是你存储会议的日历，也可以手动指定日历文件夹路径避免获取错误
• 确认查询时间范围：原始代码的查询范围是2021年1月1日到2021年5月12日，确认该时间段内你确实有对应的会议数据

第二步：修复数据类型报错 #

修改get_appointments函数中构造DataFrame的代码，主动将COM时间对象转换为pandas支持的datetime类型：

df = pd.DataFrame({
    'subject': cal_subject,
    'start': pd.to_datetime(cal_start),
    'end': pd.to_datetime(cal_end),
    'body': cal_body
})

修改make_cpd函数中计算会议时长的代码，使用total_seconds()计算总秒数，避免超过24小时的会议计算错误：

appointments['Hours'] = (appointments['end'] - appointments['start']).dt.total_seconds() / 3600

第三步：增加空数据兼容逻辑 #

在调用make_cpd之前增加空判断，避免无数据时触发后续报错：

cal = get_calendar(begin, end)
appointments = get_appointments(cal, subject_kw = 'weekly', exclude_subject_kw = 'Webcast')
if appointments.empty:
    print("未查询到符合条件的会议数据")
else:
    result = make_cpd(appointments)
    result.to_excel(final)

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