python - 如何修复 Python 的 Object of type datetime is not JSON serializable 错误

Question

我通过 Twitter 使用数据挖掘。所以我从 twitter 获得值 create_at 以保存在文件 excel 之后将文件 excel 发送到谷歌表但是无法发送。
它有这样的错误:

response = service.spreadsheets().values().append(
File "C:\Users\What Name\AppData\Local\Programs\Python\Python38-32\lib\site- 
packages\googleapiclient\discovery.py", line 830, in method
headers, params, query, body = model.request(
File "C:\Users\What Name\AppData\Local\Programs\Python\Python38-32\lib\site- 
packages\googleapiclient\model.py", line 161, in request
body_value = self.serialize(body_value)
File "C:\Users\What Name\AppData\Local\Programs\Python\Python38-32\lib\site- 
packages\googleapiclient\model.py", line 274, in serialize
return json.dumps(body_value)
File "C:\Users\What Name\AppData\Local\Programs\Python\Python38-32\lib\json\__init__.py", line 231, 
in dumps
return _default_encoder.encode(obj)
File "C:\Users\What Name\AppData\Local\Programs\Python\Python38-32\lib\json\encoder.py", line 199, in 
encode
chunks = self.iterencode(o, _one_shot=True)
File "C:\Users\What Name\AppData\Local\Programs\Python\Python38-32\lib\json\encoder.py", line 257, in 
iterencode
return _iterencode(o, 0)
File "C:\Users\What Name\AppData\Local\Programs\Python\Python38-32\lib\json\encoder.py", line 179, in 
default
raise TypeError(f'Object of type {o.__class__.__name__} '
TypeError: Object of type datetime is not JSON serializable

或者这段代码可能有问题？

xlApp = win32.Dispatch('Excel.Application')
wb = xlApp.Workbooks.Open(r"F:\work\feen\WU\twitter.xlsx")
ws = wb.WorkSheets('Sheet1')
rngData = ws.Range('A1').CurrentRegion()

gsheet_id = 'sheet_id'
CLIENT_SECRET_FILE = 'credentials2.json'
API_SERVICE_NAME = 'sheets'
API_VERSION = 'v4'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

service = Create_Service(CLIENT_SECRET_FILE,API_SERVICE_NAME,API_VERSION,SCOPES)
response = service.spreadsheets().values().append(
  spreadsheetId=gsheet_id,
  valueInputOption='RAW',
  range='data1!A1',
   body=dict(
      majorDimension='ROWS',
      values=rngData
  )
).execute()

wb.Close(r"F:\work\feen\WU\twitter.xlsx")

Answer 1

回答:

为了修复 Object of type datetime is not JSON serializable 错误，您需要将对象中 datetime 对象的所有实例转换为 字符串。

然而，您的代码中还有其他错误，这意味着仅此一项无法使您的程序运行。

将 datetime 对象转换为 string 对象:

在 python 中，您可以使用 json.dumps() 将 JSON 数据本地转换为字符串，默认转换为字符串。

您可以通过在 service.spreadsheets().values().append() 调用之前添加此行来执行此操作:

//rngData at this point has already been assigned
rngData = json.dumps(rngData, indent = 4, sort_keys = True, default = str)

注意:这本身并不能修复您的代码!

其他事项:

调用 Google Sheets API 时，以服务器期望接收这些请求的方式发出请求非常重要。也就是说，按照文档提出请求很重要。

我在 linux 机器上，所以我无法测试 win32.Dispatch().Workbooks.Open().Worksheets().Range().CurrentRegion() 的输出格式, 但如果 Microsoft 文档关于 Worksheet.Range property of Excel有什么可以引用的，我可以放心地假设它的输出不是 spreadsheets.values.append method 要求的格式:

array (ListValue format):

The data that was read or to be written. This is an array of arrays, the outer array representing all the data and each inner array representing a major dimension. Each item in the inner array corresponds with one cell.

For output, empty trailing rows and columns will not be included.

For input, supported value types are: bool, string, and double. Null values will be skipped. To set a cell to an empty value, set the string value to an empty string.

我不能 100% 确定输出是否相同，但为了尝试模拟您正在尝试的操作，我使用了 python 包 xlrd 从您提供的 Excel 文件中获取值像这样:

workbook = xlrd.open_workbook("twitter.xlsx")
sheet = workbook.sheet_by_index(0)
data = [sheet.row_values(rowx) for rowx in range(sheet.nrows)]

并且，正如您在评论中提供的屏幕截图(见下文):

我也有同样的 react 。向上滚动，错误是由于请求错误:

googleapiclient.errors.HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/XXXXX/values/Sheet1%21A1:append?alt=json&valueInputOption=RAW returned "Invalid value at 'data.values' (type.googleapis.com/google.protobuf.ListValue)..."

具体来说，“data.values”处的值无效。您需要遵守 Google Sheets API request specification for this method .

引用资料:

• Method: spreadsheets.values.append | Sheets API - Response body
• Worksheet.Range property (Excel) | Microsoft Docs
• REST Resource: spreadsheets.values | Sheets API - Resource: ValueRange
• xlrd documentation - xlrd 1.1.0 documentation