How to Make Column Names Dynamic and Deal with them As Strings in SQLAlchemy ORM?

Question

I am learning some SQL from the book Essential SQLAlchemy by Rick Copeland

I have not really used SQL much, and relied on frameworks like Pandas and Dask for data-processing tasks. As I am going through the book, I realise all the column names of a table are often part of the table attributes, and hence seems they need to be hard-coded, instead of being dealt with as strings. Example, from the book.

#!/usr/bin/env python3
# encoding: utf-8
class LineItem(Base):
    __tablename__ = 'line_items'

    line_item_id = Column(Integer(), primary_key=True)
    order_id = Column(Integer(), ForeignKey('orders.order_id'))
    cookie_id = Column(Integer(), ForeignKey('cookies.cookie_id'))
    quantity = Column(Integer())
    extended_cost = Column(Numeric(12, 2))

    order = relationship("Order", backref=backref('line_items', 
                                                  order_by=line_item_id))

    cookie = relationship("Cookie", uselist=False)

When I work with pandas dataframe, I usually deal with it like

#!/usr/bin/env python3
# encoding: utf-8 
col_name:str='cookie_id'
df[col_name] # To access a column

Is there any way to make the column names in SQL-alchemy dynamic, i.e. be represented (and added to table) purely as strings, and tables be created dynamically with different column names (the strings coming from some other function or even user input etc.), that I can later access with strings as well?

Or is my expectation wrong in the sense somehow SQL is not supposed to be used like that?

snakecharmerb · Accepted Answer · 2022-12-12 12:54:40Z

It's possible to use SQLAlchemy like this^*, but it's probably much more convenient to use a tool like Pandas which abstracts away all the work of defining columns and tables. If you want to try you should study the mapping documentation, which covers how model classes get mapped to database tables in detail.

Here's a fairly simple, not production-quality, example of how you might create some models and populate and query them dynamically.

import operator

import sqlalchemy as sa
from sqlalchemy import orm

metadata = sa.MetaData()

# Define some table schema data.
tables = [
    {
        'name': 'users',
        'columns': [('id', sa.Integer, True), ('name', sa.String, False)],
    },
]

# Create some table defiitions in SQLAlchemy (not in the database, yet).
for t in tables:
    tbl = sa.Table(
        t['name'],
        metadata,
        *[
            sa.Column(name, type_, primary_key=pk)
            for (name, type_, pk) in t['columns']
        ],
    )

# Create some empty classes for our model(s).
classes = {t['name']: type(t['name'].title(), (object,), {}) for t in tables}

# Map model classes to tables.
mapper_registry = orm.registry(metadata=metadata)
for c in classes:
    mapper_registry.map_imperatively(classes[c], metadata.tables[c.lower()])

# For convenience, make our model class a local variable.
User = classes['users']

engine = sa.create_engine('sqlite://', echo=True, future=True)
# Actually create our tables in the database.
metadata.create_all(engine)

Session = orm.sessionmaker(engine, future=True)

# Demonstrate we can insert and read data using our model.
user_data = [{'name': n} for n in ['Alice', 'Bob']]
with Session.begin() as s:
    s.add_all([User(**d) for d in user_data])

# Mock user input for a simple query.
query_data = {
    'column': 'name',
    'op': 'eq',
    'value': 'Alice',
}
with Session() as s:
    equals = getattr(operator, query_data['op'])
    where = equals(getattr(User, query_data['column']), query_data['value'])
    q = sa.select(User).where(where)
    users = s.scalars(q)
    for user in users:
        print(user.id, user.name)

^* Up to a point: SQLAlchemy generally does not support schema changes other than creating and deleting tables.

Collectives™ on Stack Overflow

How to Make Column Names Dynamic and Deal with them As Strings in SQLAlchemy ORM?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related