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: ignore
  File "C:\Users\6092003521\Miniconda3\lib\site-packages\sqlalchemy\engine\result.py", line 1786, in 
all
    return self._allrows()
  File "C:\Users\6092003521\Miniconda3\lib\site-packages\sqlalchemy\engine\result.py", line 554, in _allrows
    rows = self._fetchall_impl()
  File "C:\Users\6092003521\Miniconda3\lib\site-packages\sqlalchemy\engine\result.py", line 1693, in 
_fetchall_impl
    return self._real_result._fetchall_impl()
  File "C:\Users\6092003521\Miniconda3\lib\site-packages\sqlalchemy\engine\result.py", line 2293, in 
_fetchall_impl
    return list(self.iterator)
  File "C:\Users\6092003521\Miniconda3\lib\site-packages\sqlalchemy\orm\loading.py", line 191, in chunks
    fetch = cursor._raw_all_rows()
  File "C:\Users\6092003521\Miniconda3\lib\site-packages\sqlalchemy\engine\result.py", line 547, in _raw_all_rows
    return [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_processors
  File "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 Columns
    date = 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_test


session = 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()

参考
https://docs.sqlalchemy.org/en/20/orm/extensions/automap.html

使用xxxx_ _processor 这些回调函数。

官方例子
https://docs.sqlalchemy.org/en/20/core/custom_types.html#applying-sql-level-bind-result-processing

参考

https://docs.sqlalchemy.org/en/20/core/custom_types.html
https://docs.sqlalchemy.org/en/13/core/type_basics.html