SQLAlchemy dynamic query using object attribute
<p>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.</p>

<pre><code>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)
</code></pre>

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

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

<pre><code>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
</code></pre>

<p>I later begin iterating over each <code>Product</code> and <code>PriceList</code> instance.</p>

<pre><code>for product in products:
    for price_list in price_lists:
        # Do stuff
</code></pre>

<p>At this point my <code>product</code> object has a new special price, or multiple new special prices, that I plan on updating in the database. I <em>could</em> 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</p>

<pre><code>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()
</code></pre>

<p>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.</p>