基本上,我需要构建一个函数,根据给定的日期过滤查询并返回新的查询结果。我对SQLAlchemy不熟悉,虽然我查找了类似的问题,但仍然遇到了相同的错误:
`Don't know how to literal-quote value datetime.datetime(2018, 1, 1, 8, 3, 1, 438278)`
这是我的代码:
def filter_dates(query_obj, datecols, start = None, end = None):
if end is None:
end = datetime.datetime.now()
if start is None:
start = end - datetime.timedelta(weeks=12)
print("%s to %s" % (start, end))
for datecol in datecols:
print("Filtrando datas!")
query_obj = query_obj.filter(datecol >= start)
query_obj = query_obj.filter(datecol <= end)
ReevTable.print_query(query_obj)
return query_obj
datecols是一个orm.attributes对象。假设我有一个名为User
的对象,其中有一个名为created_at
的Datetime
属性。这是预期的行为:
query = session.query(Company.name, Company.created_at, Company.number_of_employees, Company.email_bounce_rate)
query = filter_dates(query_obj=query, datecols = [Company.created_at, Company.email_events.created_at])
query.all()
期望输出是一张表格,其中包含在指定日期范围内创建的公司,并且跳出率应该仅在该指定日期范围内计算。这可能看起来很奇怪,但我不仅计算电子邮件,还计算其他类型的交互,因此我需要输入一个属性列表而不仅仅是单个属性。这就是为什么我需要使用一个方法来分隔这个筛选的原因。
我尝试过使用pandas datetime和timedelta、内置的python datetime模块以及简单的字符串与pd.to_datetime,但都没有成功。每次都会引发相同的错误。我的Company列是DateTime类型,所以我不知道还能做什么。
class Company(Base)
created_at = Column(DateTime, nullable=False)
我完全不了解SQLAlchemy,我做错了什么吗?
完整的回溯:
`Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "reev-data-science/tables/reevtable.py", line 128, in import_data
self.print_query(query_obj)
File "reev-data-science/tables/reevtable.py", line 107, in print_query
print(bcolors.OKBLUE + bcolors.BOLD + str(query_obj.statement.compile(compile_kwargs={"literal_binds":True})) + bcolors.ENDC)
File "<string>", line 1, in <lambda>
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/elements.py", line 442, in compile
return self._compiler(dialect, bind=bind, **kw)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/elements.py", line 448, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/compiler.py", line 453, in __init__
Compiled.__init__(self, dialect, statement, **kwargs)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/compiler.py", line 219, in __init__
self.string = self.process(self.statement, **compile_kwargs)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/compiler.py", line 245, in process
return obj._compiler_dispatch(self, **kwargs)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/annotation.py", line 80, in _compiler_dispatch
self, visitor, **kw)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
return meth(self, **kw)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/compiler.py", line 1815, in visit_select
text, select, inner_columns, froms, byfrom, kwargs)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/compiler.py", line 1899, in _compose_select_body
t = select._whereclause._compiler_dispatch(self, **kwargs)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
return meth(self, **kw)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/compiler.py", line 829, in visit_clauselist
for c in clauselist.clauses)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/compiler.py", line 826, in <genexpr>
s for s in
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/compiler.py", line 829, in <genexpr>
for c in clauselist.clauses)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
return meth(self, **kw)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/compiler.py", line 829, in visit_clauselist
for c in clauselist.clauses)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/compiler.py", line 826, in <genexpr>
s for s in
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/compiler.py", line 829, in <genexpr>
for c in clauselist.clauses)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
return meth(self, **kw)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/compiler.py", line 1080, in visit_binary
return self._generate_generic_binary(binary, opstring, **kw)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/compiler.py", line 1113, in _generate_generic_binary
self, eager_grouping=eager_grouping, **kw)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
return meth(self, **kw)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/compiler.py", line 1244, in visit_bindparam
bindparam, within_columns_clause=True, **kwargs)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/compiler.py", line 1277, in render_literal_bindparam
return self.render_literal_value(value, bindparam.type)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/compiler.py", line 1295, in render_literal_value
"Don't know how to literal-quote value %r" % value)
NotImplementedError: Don't know how to literal-quote value datetime.datetime(2018, 1, 1, 9, 24, 46, 54634)`
打印查询(print_query()) 方法:
`def print_query(query_obj):
print(bcolors.OKBLUE + bcolors.BOLD + str(query_obj.statement.compile(compile_kwargs={"literal_binds":True})) + bcolors.ENDC)`
ReevTable.print_query(query_obj)
是罪魁祸首,尽管你没有包含它的定义。 - Ilja EveriläTypeDecorator
来处理不支持渲染字面量的类型,并实现TypeDecorator.process_literal_param()
。 - Ilja Everilä