CODEWITHSUNDEEP
pythonsqlalchemy
A Magoon
A Magoon

Reputation: 1210

SQLAlchemy dynamic query using object attribute

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.

Upvotes: 3

Views: 6411

Answers (1)

RazerM
RazerM

Reputation: 5482

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

Upvotes: 8

Related Questions