CODEWITHSUNDEEP
pythonsqlalchemy
Allen Liu
Allen Liu

Reputation: 31

how to pass a parameter as a filter condition in SQLAlchemy method

I know, I can use Python's SQLAlchemy's query method as below:

session.query(User).filter(User.id == 0).first()

But I wish to parameterize it, so I could pass a table name, filter element and filter value, to query, for example:

filter element -> User.id
filter value -> 0

But when I pass User.filter_element to the query, I get an exception:

AttributeError: type object 'User' has no attribute 'filter_element'

Is what I am trying to do possible, and if so how?

Note that I know that I can use the sql.execute(<sql>) to get a similar result, but I prefer to use sqlalchemy.orm to do it with out the need to resort to a SQL statement.

Upvotes: 1

Views: 6709

Answers (2)

Jesvin Jose
Jesvin Jose

Reputation: 23078

With this bit of information, you can generate queries flexibly.

In [2]: User.email
Out[2]: <sqlalchemy.orm.attributes.InstrumentedAttribute at 0x7f42cd4b7410>

In [3]: User.email == ''
Out[3]: <sqlalchemy.sql.elements.BinaryExpression object at 0x7f42ca640550>

In [4]: User.email.in_([])
...warning
Out[4]: <sqlalchemy.sql.elements.BinaryExpression object at 0x7f42c9e07710>

You can do

user_attribute = getattr(User, 'email') # get attribute
if equals_filter:
    user_filter = user_attribute == equals_value # compute expression
else:
   user_filter = user_attribute.in_(in_list)
users = session.query(User).filter(user_filter) # filter using expression

Upvotes: 4

tell k
tell k

Reputation: 615

You can use. declarative_base and filter_by. 

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

engine = create_engine('sqlite:///test.db', convert_unicode=True)
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()


class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True)

    def __init__(self, name=None):
        self.name = name

    def __repr__(self):
        return '<User %r>' % (self.name)

Base.metadata.create_all(bind=engine)
db_session.add(User('name1'))
db_session.commit()

User.query.filter_by(name='name1').first()  # => <User u'name1'>

Upvotes: 1

Related Questions