I’m using SQLModel, which is a wrapper around SQLAlchemy.
The data model includes:
class Line(SQLModel, table = True):
id: int | None = Field(default = None, primary_key = True)
name: str
class Product(SQLModel, table = True):
id: int | None = Field(default = None, primary_key = True)
line_id: int | None = Field(default = None, foreign_key = 'line.id')
myob_uid: str
myob_name: str
weight: float
An example query that selects a subset of columns from both tables:
query = select(Product.id, Product.weight, Line.name)
.join(Line)
Notice the repetition of table name in the select statement. In SQLAlchemy we can do this:
select(Product.c['id','weight'], Line.c['name'])
However the c attribute is not available in SQLModel.
Is there a way to subset a table by multiple columns without repeating the name?
This is a pedantic request. However writing DRY queries is important as they become large and complex.
I’ve tried both the following however they aren’t as elegant for readability:
- Aliasing the tables to a letter (p or l for example).
select(*[getattr(Product, x) for x in ['id','weight']], Line.name).
Unfortunately, in SQLModel, there is no direct c attribute like in SQLAlchemy Core because SQLModel follows a more declarative ORM pattern.
However, you can still achieve DRY queries by using Python features to avoid repetition.
You can use a list comprehension but make it more readable by wrapping this functionality in a utility function. This way, you can keep your queries DRY without repeating table names in the select statement.
This is the code example.
# Helper function to dynamically extract columns from a table
def get_columns(table: SQLModel, columns: List[str]):
return [getattr(table, column) for column in columns]
# Use the helper function to get columns for both tables
query = select(*get_columns(Product, product_columns), *get_columns(Line, line_columns)).join(Line)
I hope this helps a little.