SQLAlchemy dynamic query using object attribute

Question

I'm looking to query an object's attribute dynamically. I will not know which attribute, or column in this case, that I'll be using at the time of execution.

class Product(Base):
    __tablename__ = 'products'

    sku = Column(String, primary_key=True)
    list_price = Column(String)
    status = Column(String)
    url = Column(String)
    special_price1 = Column(String)
    special_price2 = Column(String)
    special_price3 = Column(String)

I have a SQLAlchemy Base class Product that describes a few attributes, plus additional special prices that differ from the list price.

I then have a PriceList class, below, that has access to additional resources and methods that help with reporting and updating columns in the 'products' table. This class stores information about a unique special price list for all Product objects.

class PriceList:

    def __init__(self, name, db_col_name):
        # Display name
        self.name = name

        # Used for querying the database for a specific column
        # This will always be one of the 4 price related column names
        # list_price, special_price1, special_price2, or special_price3
        self.db_col_name = db_col_name

I later begin iterating over each Product and PriceList instance.

for product in products:
    for price_list in price_lists:
        # Do stuff

At this point my product object has a new special price, or multiple new special prices, that I plan on updating in the database. I could simply add my object to the database session and commit, but I need to get the old price(s) and keep them linked to their respective price lists before I do commit. The old prices are used in a report that later gets emailed to me. What I am doing now is below

for product in products:
    sku = product.sku
    for price_list in price_lists:
        # New price
        new_price = product.__getattribute__(price_list.db_col_name)

        # Get the existing special price from the database
        old_price = s.query(Product.__getattribute__(Product, price_list.db_col_name)).filter(Product.sku.like(sku)).first()

I feel like I'm drastically over complicating this by using __getattribute__(). It works, but this doesn't seem pythonic. Does anyone know a better way to get the value of an unknown column before you update? Database updates only occur once or twice every ~500 products, so storing each special price in an external variable while they're being processed isn't exactly efficient.

Why are you refetching the price if you have the product object right there? — univerio
– univerio, Commented Jul 14, 2016 at 20:52
The product object's price has changed by that point. It has a new price that differs from the database. — A Magoon
– A Magoon, Commented Jul 14, 2016 at 20:56
1. You can access the loaded value of an attribute before you flush. 2. Even if you couldn't, you can always save the attribute yourself. — univerio
– univerio, Commented Jul 14, 2016 at 21:11

RazerM · Accepted Answer · 2016-07-14 22:10:09Z

8

To dynamically access attributes, you should use the getattr builtin.

new_price = getattr(product, price_list.db_col_name)

If the instance is stale, you should use Session.expire, which means that the next time you access the attributes they will be retrieved from the database.

s.expire(product)

# or only expire price
s.expire(product, [price_list.db_col_name])

answered Jul 14, 2016 at 22:10

RazerM

5,5522 gold badges27 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

A Magoon Over a year ago

Thanks for your answer! My question was unique and confusing but you gave me enough to go on. getattr() is the right tool to use for this scenario. I was confused when it came to querying because getattr() returns the value of that attribute. When running s.query(Object.attribute) I've always thought of the argument not as a value but more a pointer based on a class... Thanks for breaking that bad habit. I also never knew about Session.expire, good to know!

rrh Over a year ago

Thanks. This helped me realize that what I was looking for is setattr(). My case was to set object's attribute dynamically.

Collectives™ on Stack Overflow

SQLAlchemy dynamic query using object attribute

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related