时间戳

首页 > 解决方案 > bson.errors.InvalidBSON 超出范围

python - bson.errors.InvalidBSON 超出范围

问题描述

我有一个通过 pymongo 从 python 到 mongodb 的查询。有些记录对我有用，但在一条记录上它停止工作，看起来有不同的日期格式，但是它是如何进入 find() 的：

from bson import ObjectId
import config_auth
from operator import itemgetter
from datetime import datetime
import pyodbc
import pymongo

mydb = config_auth.mydb

def load():
    temp_arr = []
    for item in mydb.questionaries.find({ 'created_at' : {"$gt": datetime(2019,10,30), "$lt": datetime(2019,10,31)}}):
        temp=[]
        temp.append(str(item['_id']))
        print(item['created_at'])
        temp_arr.append(tuple(temp))

在此之后我有这个错误：

2019-10-30 15:36:09.920000 2019-10-30 15:36:02.344000 2019-10-30 15:36:02.344000 2019-10-30 15:33:47.360000 2019-10-30 15:33:47.360000 Traceback (most recent call last): File "c:/Users/d.konoplya/Desktop/python/etl_finservice/questionaries.py", line 115, in <module> print(load()) File "c:/Users/d.konoplya/Desktop/python/etl_finservice/questionaries.py", line 16, in load for item in mydb.questionaries.find({ 'created_at' : {"$gt": datetime(2019,10,30), "$lt": datetime(2019,10,31)}}): File "C:\Users\d.konoplya\AppData\Local\Programs\Python\Python37\lib\site-packages\pymongo\cursor.py", line 1156, in next if len(self.__data) or self._refresh(): File "C:\Users\d.konoplya\AppData\Local\Programs\Python\Python37\lib\site-packages\pymongo\cursor.py", line 1093, in _refresh self.__send_message(g) File "C:\Users\d.konoplya\AppData\Local\Programs\Python\Python37\lib\site-packages\pymongo\cursor.py", line 955, in __send_message address=self.__address) File "C:\Users\d.konoplya\AppData\Local\Programs\Python\Python37\lib\site-packages\pymongo\mongo_client.py", line 1346, in _run_operation_with_response exhaust=exhaust) File "C:\Users\d.konoplya\AppData\Local\Programs\Python\Python37\lib\site-packages\pymongo\mongo_client.py", line 1464, in _retryable_read return func(session, server, sock_info, slave_ok) File "C:\Users\d.konoplya\AppData\Local\Programs\Python\Python37\lib\site-packages\pymongo\mongo_client.py", line 1340, in _cmd unpack_res) File "C:\Users\d.konoplya\AppData\Local\Programs\Python\Python37\lib\site-packages\pymongo\server.py", line 131, in run_operation_with_response user_fields=user_fields) File "C:\Users\d.konoplya\AppData\Local\Programs\Python\Python37\lib\site-packages\pymongo\cursor.py", line 1030, in _unpack_response legacy_response) File "C:\Users\d.konoplya\AppData\Local\Programs\Python\Python37\lib\site-packages\pymongo\message.py", line 1538, in unpack_response self.documents, codec_options, user_fields) File "C:\Users\d.konoplya\AppData\Local\Programs\Python\Python37\lib\site-packages\bson\__init__.py", line 1098, in _decode_all_selective bson.errors.InvalidBSON: year 0 is out of range

{
"_id" : ObjectId("5db9849eb491a900016f913b"),
"updated_at" : ISODate("2019-10-30T13:10:29.320Z"),
"created_at" : ISODate("2019-10-30T12:39:58.277Z"),
"state" : "credit_issued",
"registred_in_1c_at" : ISODate("2019-10-30T13:33:19.504Z"),
"signer_id" : ObjectId("5d584ab05aeafd000191518a")
}

但是每条记录的日期> 0。可能是什么问题？

谢谢

标签： pythonpymongo

解决方案

我实际上找到了，这是因为 MongoDB / BSON 可以表示datetime.datetimePython 范围之外的日期。

PyMongo 将 BSON 日期时间值解码为 Python 的 datetime.datetime 实例。datetime.datetime 的实例仅限于 datetime.MINYEAR（通常为 1）和 datetime.MAXYEAR（通常为 9999）之间的年份。一些 MongoDB 驱动程序（例如 PHP 驱动程序）可以存储 BSON 日期时间，其年份值远远超出 datetime.datetime 支持的值。

在 pymongo 常见问题解答中阅读更多相关信息：https ://pymongo.readthedocs.io/en/stable/faq.html#why-do-i-get-overflowerror-decoding-dates-stored-by-another-language-s -司机

他们建议以下解决方法。

一种是查询有效的日期范围：

>>> from datetime import datetime
>>> coll = client.test.dates
>>> cur = coll.find({'dt': {'$gte': datetime.min, '$lte': datetime.max}})

另一种是从结果中排除违规字段：

>>> cur = coll.find({}, projection={'dt': False})

推荐阅读