sqlalchemy auto map ValueError: Invalid isoformat string: ‘xxxx‘

map ValueError: Invalid isoformat string: ‘xxxx‘"/>

sqlalchemy auto map ValueError: Invalid isoformat string: ‘xxxx‘

背景

我们用python 读取 现有数据时候，经常用到auto map 来帮我们自动完成ORM。但是有时候像日期这种东西。数据库里面可能不是标准的iso 格式。导致sqlalchemy 报 ValueError: Invalid isoformat string: ‘xxxx’

Traceback (most recent call last):File "D:\project\mysite\feiyangyang\test.py", line 54, in <module>u1=u1.all()File "C:\Users\6092003521\Miniconda3\lib\site-packages\sqlalchemy\orm\query.py", line 2688, in all return self._iter().all()  # type: ignoreFile "C:\Users\6092003521\Miniconda3\lib\site-packages\sqlalchemy\engine\result.py", line 1786, in 
allreturn self._allrows()File "C:\Users\6092003521\Miniconda3\lib\site-packages\sqlalchemy\engine\result.py", line 554, in _allrowsrows = self._fetchall_impl()File "C:\Users\6092003521\Miniconda3\lib\site-packages\sqlalchemy\engine\result.py", line 1693, in 
_fetchall_implreturn self._real_result._fetchall_impl()File "C:\Users\6092003521\Miniconda3\lib\site-packages\sqlalchemy\engine\result.py", line 2293, in 
_fetchall_implreturn list(self.iterator)File "C:\Users\6092003521\Miniconda3\lib\site-packages\sqlalchemy\orm\loading.py", line 191, in chunksfetch = cursor._raw_all_rows()File "C:\Users\6092003521\Miniconda3\lib\site-packages\sqlalchemy\engine\result.py", line 547, in _raw_all_rowsreturn [make_row(row) for row in rows]File "C:\Users\6092003521\Miniconda3\lib\site-packages\sqlalchemy\engine\result.py", line 547, in <listcomp>return [make_row(row) for row in rows]File "lib\\sqlalchemy\\cyextension\\resultproxy.pyx", line 16, in sqlalchemy.cyextension.resultproxy.BaseRow.__init__File "lib\\sqlalchemy\\cyextension\\resultproxy.pyx", line 73, in sqlalchemy.cyextension.resultproxy._apply_processorsFile "lib\\sqlalchemy\\cyextension\\processors.pyx", line 45, in sqlalchemy.cyextension.processors.str_to_date
ValueError: Invalid isoformat string: 'abc'

解决方法

• 改数据库中的数据格式让他变成标准的sqlalchemy 可以读取的格式
• 自定义一个ORM模型字段，使对应保存的列变成string格式
• 也是自定义ORM模型中的字段，不过使用回调函数来修改数据的写入和读取时候的格式。

自己写一个orm 对象覆盖指定的字段

# print(df.dtypes)from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine,insert,func,MetaData,TEXT,Column,Table,Integer,ForeignKey,String
import datetime# engine, suppose it has two tables 'user' and 'address' set up
engine = create_engine("sqlite:///D:\project\mysite\db.sqlite3")Base = automap_base()class Test(Base):__tablename__ = 'feiyangyang_test'#直接用string 覆盖# override schema elements like Columnsdate = Column('date', String)# reflect the tables
Base.prepare(autoload_with=engine)# mapped classes are now created with names by default
# matching that of the table name.
# test = Base.classes.feiyangyang_testsession = Session(engine)# collection-based relationships are by default named
# "<classname>_collection"# u1 = session.query(test,func.strftime("%Y/%m/%d",test.date))
# u1 = session.query(test,func.substr(test.date,))
u1 = session.query(Test)
print(u1.statement)
u1=u1.all()

参考
.html

使用xxxx_ _processor 这些回调函数。

官方例子
.html#applying-sql-level-bind-result-processing

参考

.html
.html