Datetime filtering with SQLAlchemy isn't working

Question

Basically, I need to build a function that will filter a query according to given dates and return a new query. I'm new to SQLAlchemy, I looked up similar questions but I still got the same error:

`Don't know how to literal-quote value datetime.datetime(2018, 1, 1, 8, 3, 1, 438278)`

Here's my code:

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 is an orm.attributes object. Suppose I have an Object called User with a Datetime attribute named created_at. This is the expected behaviour:

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

Expected output is a table with Companies that were only created within the date range, and the bounce rate should only be calculated during that specified date range. This might seem weird, but I calculate a not just emails, but other kinds of interactions too, so I need to input a list of attributes instead of just a single one. This is why I need to separate this filtering with a method.

I've tried using pandas datetime and timedelta, the built-in python datetime module, and simple strings with pd.to_datetime, but without success. The same error gets raised everytime. My Company column is in DateTime, so I don't know what else to do.

class Company(Base)
    created_at = Column(DateTime, nullable=False)

I'm completely new to SQLAlchemy, what am I doing wrong?

Full traceback:

`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)`

The print_query() method:

`def print_query(query_obj):
    print(bcolors.OKBLUE + bcolors.BOLD + str(query_obj.statement.compile(compile_kwargs={"literal_binds":True})) + bcolors.ENDC)`

Answer 1

Convert datatime object to str in your filter clause

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 >= str(start))
            query_obj = query_obj.filter(datecol <= str(end))
            ReevTable.print_query(query_obj)
        return query_obj

Answer 2

I had the same problem with the DateTime type. This error occurs if you want to compile your statement with only literal_binds: True argument and you have complex (not int or string) fields in query.

stmt.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True})

Solved it with TypeDecorator.

Solution:

from sqlalchemy import insert, create_engine, MetaData, Table, Column, DateTime, TypeDecorator
from sqlalchemy.dialects import postgresql
import psycopg2
from datetime import datetime

engine = create_engine('postgresql+psycopg2://postgres:[email protected]/postgres')
conn = engine.connect()

class MyDateTimeType(TypeDecorator):
    impl = DateTime

    def process_literal_param(self, value, dialect):
        return value.strftime("'%Y-%m-%d %H:%M:%S'")

meta = MetaData()
dates = Table('dates', meta, Column('created_at', MyDateTimeType(), nullable=False))

stmt = (
    insert(dates).
    values(created_at=datetime.now())
)

raw_sql = stmt.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True})

conn.execute(raw_sql)