CODEWITHSUNDEEP
pythonsqlalchemy
Stan
Stan

Reputation: 400

How to parse filter clause in sqlalchemy

How can I parse clauses, given as parameters in a filter method?

Running of

filters_clause = Record.start>='2017-07-17'

print(filters_clause)

gives a string

records.start >= :start_1

It's necessary a real value instead of :start_1 and that value must have been passed by process_bind_param function. Which method I have to use to get a string like that: records.start >= '1500321600.0'?

#!/usr/bin/env python
# coding=utf-8

from __future__ import ( division, absolute_import,
                         print_function, unicode_literals )

import time

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, attributes
from sqlalchemy.types import TypeDecorator
from sqlalchemy import create_engine, MetaData, Column, Integer, String


Base = declarative_base()


class EpochTime(TypeDecorator):
    impl = Integer

    def process_bind_param(self, value, dialect):
        if isinstance(value, unicode):
            if value.isdigit():
                return value

            if len(value) == 10:
                value = time.mktime(time.strptime(value, "%Y-%m-%d"))
            elif len(value) == 13:
                value = time.mktime(time.strptime(value, "%Y-%m-%d %H"))
            elif len(value) == 16:
                value = time.mktime(time.strptime(value, "%Y-%m-%d %H:%M"))

        return value

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


class Record(Base):
    __tablename__ = 'records'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    start = Column(EpochTime)


engine = create_engine('sqlite://')
session = scoped_session(sessionmaker())
session.configure(bind=engine)
Base.metadata.create_all(engine)

record = Record(name = 'Record 1', start = '2017-07-16')
session.add(record)
record = Record(name = 'Record 2', start = '2017-07-17')
session.add(record)
record = Record(name = 'Record 3', start = '2017-07-18')
session.add(record)

session.commit()


filters_clause = Record.start>='2017-07-17'
s = session.query(Record).filter(filters_clause)
res = s.all()
names = [i.name for i in Record.__table__.c]
rows = [[row.__dict__.get(i) for i in names] for row in res]

for row in rows:
    print(row)

print(0, s)
print(1, filters_clause)

Upvotes: 0

Views: 1432

Answers (2)

univerio
univerio

Reputation: 20548

I think what you want is literal_binds:

print(filters_clause.compile(engine, compile_kwargs={"literal_binds": True}))
# records.start >= 1500274800.0

Be wary of SQL injection if you use literal_binds, though.

Upvotes: 1

krassowski
krassowski

Reputation: 15449

I don't think that you can achieve this simply with vanilla sqlalchemy. You can try using a custom function, like:

import re

def represent_filter(expression):
    expression = expression.compile()
    params = expression.params
    prefixed_names = [':' + param for param in params]
    pattern = re.compile('(%s)' % '|'.join(prefixed_names))

    def substitute_param_value(match):
        return params[match.group()[1:]]

    return pattern.sub(substitute_param_value, expression.string)

Then use it calling represent_filter(filters_clause) and it will return records.start >= 2017-07-17.

You could force represent_filter function to quote substituted value, using something like repr(str(params[match.group()[1:]])) instead of params[match.group()[1:]].

An other approach would be to traverse given expression (in your case filters_clause is a BinaryExpression, there are unary expressions too), check left and right sides and build your own representation. If you only want to recover the value (the date which you provided to filter) it is in filters_clause.right.effective_value.

Upvotes: 0

Related Questions