Call table values dynamically with sqlalchemy

ghz 12hours ago ⋅ 1 views

Let's say I have a table named "info_test" mapped like this:

class db_info_test(BASE):

__tablename__ = "info_test"

id              = Column(Integer, primary_key=True)
name            = Column(String)
project         = Column(String)
manufacturer    = Column(String)

If I want to get the names of all items from that table I would do something like this:

import db_info_test     
for row in self.session.query(db_info_test):
    print(row.name)

which would print:

name_of_item_A
name_of_item_B
...

Now I want to create a very generall function that prints the value of a key, where the key is given by an argument of that function. Something like this:

def get_value(self, table, key):
    for row in self.session.query(table):
        print(row.key)

Answer

To create a more general function that prints the value of a given key in a table, you can use SQLAlchemy's dynamic column access. The key you pass to the function can be used to dynamically access the column in the query result.

Here’s how you can modify your get_value function to achieve this:

General Function Definition

def get_value(self, table, key):
    for row in self.session.query(table):
        # Dynamically access the column value using getattr
        print(getattr(row, key))

Explanation:

  1. getattr(row, key): This is used to dynamically access an attribute of the row object, where row is an instance of your SQLAlchemy model (db_info_test in your case). key is the name of the column you want to access (like name, project, manufacturer, etc.).

    • getattr allows you to dynamically access an attribute of an object, based on a string name. This means you can pass any column name (as a string) to the key argument and it will return the corresponding value from that row.

  2. table: This parameter refers to the model class (db_info_test in your example) that corresponds to a table in your database.

Usage

Here’s how you can use this function:

# Assuming db_info_test is already defined as a mapped class
table = db_info_test
key = "name"  # This can be any valid column name

get_value(self, table, key)

Example Output

If key = "name", the function will print all the names:

name_of_item_A
name_of_item_B
...

Key Considerations:

  • Valid Column Name: Ensure that the value passed in key is a valid column name for the given table. If you pass an invalid column name, getattr will raise an AttributeError.
  • Efficiency: If you're querying large datasets, consider using .yield_per() or adding appropriate filtering (.filter()) to optimize the query performance.

Optional: Add Error Handling

You can add error handling to make sure the column name is valid:

def get_value(self, table, key):
    try:
        for row in self.session.query(table):
            print(getattr(row, key))
    except AttributeError:
        print(f"Error: Column '{key}' does not exist in table '{table.__tablename__}'")

This will handle cases where an invalid column name is passed and display a more informative error message.

#py