Get sqlalchemy table Model and Field objects from strings?

Question

Very simple trying to run a query in Python 3 sqlalchemy to delete some records given string names of table and field to query against.

How do you get the table object from a string?
Given 1. how do you run a query via ORM with just a string of the field name?

I would assume all ORM's have an internal array or method like get with the name.



json_config = [
    {"table": "tableA",
     "field": "modified_on"
     "expires": 30},
    {"table": "tableB",
     "field": "event_on"
     "expires": 30}
]

for table_conf_item in self.json_config:
    table_name = table_conf_item["table"]
    field_name = table_conf_item["field"]
    expire_after = table_conf_item["expires"]
    table_obj = self.orm_session.TABLES[table_name]
    field_obj = self.orm_session.TABLES[table_name].FIELDS[field_name]
    result = self.orm_session.delete(table_obj).where(field_obj < expire_after)
    self.orm_session.commit()
    print(f"{table_name}: removed {result.row_count} objects")

snakecharmerb · Accepted Answer · 2022-11-20 11:45:33Z

2

Given the table's name, you can use reflection to get a Table object. Using SQLAlchemy's core layer, this is reasonably straightforward:

import sqlalchemy as sa

engine = sa.create_engine(...)

tbl = sa.Table(name_of_table, metadata, autoload_with=engine)

If you want to work with multiple tables, it may be more efficient to store them a Metadata instance for later access:

metadata = sa.MetaData()
metadata.reflect(engine, only=list_of_table_names)
tbl = metadata.tables[name_of_table]

Once you have a Table object you can reference columns by name like this: tbl.c[name_of_field].

Full example:

import sqlalchemy as sa

# Setup
engine = sa.create_engine('sqlite://', echo=True, future=True)

tbl = sa.Table(
    't',
    sa.MetaData(),
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('foo', sa.Integer),
)

tbl.create(engine)

with engine.begin() as conn:
    vals = [42, 43, 42, 43, 56, 87, 89]
    conn.execute(tbl.insert(), [{'foo': v} for v in vals])

del tbl

# Reflect the table.
metadata = sa.MetaData()
metadata.reflect(engine, only=['t'])
tbl = metadata.tables['t']

# Define some statements.
q1 = sa.select(tbl).where(tbl.c['foo'] == 42)
q2 = sa.select(tbl.c['id'], tbl.c['foo']).where(tbl.c['foo'] == 43)
q3 = sa.delete(tbl).where(tbl.c['foo'] != 42)

# Execute the statements.
with engine.connect() as conn:
    rows = conn.execute(q1)
    for row in rows:
        print(row)
    print()
    rows = conn.execute(q2)
    for row in rows:
        print(row)
    print()

with engine.begin() as conn:
    conn.execute(q3)


with engine.connect() as conn:
    rows = conn.execute(q1)
    for row in rows:
        print(row)
    print()

Doing the same through the ORM layer is more complicated, as table and column names must be mapped to ORM entity classes (models) and their attributes. This replicates the previous example for a simple mapping (it assumes the same initial data as above).

import sqlalchemy as sa
from sqlalchemy import orm

Base = orm.declarative_base()


class Thing(Base):
    __tablename__ = 't'

    id = sa.Column(sa.Integer, primary_key=True)
    thing_foo = sa.Column('foo', sa.Integer)


engine = sa.create_engine(...)
Base.metadata.create_all(engine)
Session = orm.sessionmaker(engine, future=True)

tablename = 't'
columnname = 'foo'

with Session.begin() as s:
    # Get the mappers for the Base class.
    mappers = Base.registry.mappers
    # Get the mapper for our table.
    mapper = next(m for m in mappers if m.entity.__tablename__ == tablename)
    # Get the entity class (Thing).
    entity = mapper.entity
    # Get the column from the Table.
    table_column = mapper.selectable.c[columnname]
    # Get the mapper property that corresponds to the column
    # (the entity attribute may have a different name to the 
    # column in the database).
    mapper_property = mapper.get_property_by_column(table_column)
    # Get the queryable entity attribute (Thing.thing_foo).
    attr = mapper.all_orm_descriptors[mapper_property.key]
    q = sa.select(entity).where(attr != 42)
    entities = s.scalars(q)
    for entity in entities:
        s.delete(entity)

with Session() as s:
    for thing in s.scalars(sa.select(Thing)):
        print(thing.id, thing.thing_foo)

edited Nov 20, 2022 at 11:45

answered Nov 18, 2022 at 15:13

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Ronaldo Nascimento Over a year ago

Why do all these SQL alchemy examples create and populate tables? I would assume >50% of work is done with pre-existing tables. I don't need to create tables or create schema, its already there. All I want to do is delete records via a config file that has 3 items: table name, field name, and expiration date. So I would assume an ORM that's nearing version 2.0 has a way to address EXISTING tables metadata.

Gord Thompson Over a year ago

@RonaldoNascimento - Examples are often intended to be self-contained — similar to a minimal reproducible example — so it is clear exactly what the example code is doing. They are provided to instruct, not to give you code that you can copy/paste into your project. If you want that then hire a consultant.

Gord Thompson Over a year ago

@RonaldoNascimento re: "a way to address EXISTING tables metadata" - See tbl = sa.Table('t', sa.MetaData(), autoload_with=engine) above.

snakecharmerb Over a year ago

@RonaldoNascimento Do the example statements not show you how to achieve your goal?

Ronaldo Nascimento Over a year ago

Thank you very much, this is where my confusion lies and I need to revisit learning and reading the SQLalchemy docs. I was frustrated trying to fix a bug before the weekend break, I apologize. I will try this is out.

|

Collectives™ on Stack Overflow

Get sqlalchemy table Model and Field objects from strings?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related