With SQLAlchemy, how can I convert a string to a "real" Python object?

Question

I am using SQLAlchemy with Alembic to simplify access to the database that I am using and any changes to the data structure that I make for tables. This works very well until I began to notice more and more problems with the "expiring" SQLAlchemy fields from my point of view, almost by accident.

An example is this snippet,

class HRDecimal(Model):
    dec_id = Column(String(50), index=True)

    @staticmethod
    def qfilter(*filters):
        """
        :rtype : list[HRDecimal]
        """
        return list(HRDecimal.query.filter(*filters))


class Meta(Model):
    dec_id = Column(String(50), index=True)

    @staticmethod
    def qfilter(*filters):
        """
        :rtype : list[Meta]
        """
        return list(Meta.query.filter(*filters))

the code:

ids = ['1', '2', '3']  # obviously fake list of ids

decs = HRDecimal.qfilter(
    HRDecimal.dec_id.in_(ids))
metas = Meta.qfilter(
    Meta.dec_id.in_(ids))
combined = []

for ident in ids:
    combined.append((
        ident,
        [dec for dec in decs if dec.dec_id == ident],
        [hm for hm in metas if hm.dec_id == ident]
    ))

There were no problems for the above, but when I process a list of identifiers that can contain several thousand identifiers, this process has worked for a huge amount of time, and if it is made from a web request into a flask, the thread is often killed.

When I started to peep why this was happening, the key area was

        [dec for dec in decs if dec.dec_id == ident],
        [hm for hm in metas if hm.dec_id == ident]

- ( ) Python, - dec.dec_id hm.dec_id, SQLAlchemy, ,

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

InstrumentedAttribute sqlalchemy/orm/attributes.py, , , , ,

def get(self, state, dict_, passive=PASSIVE_OFF):
    """Retrieve a value from the given object.
    If a callable is assembled on this object attribute, and
    passive is False, the callable will be executed and the
    resulting value will be set as the new value for this attribute.
    """
    if self.key in dict_:
        return dict_[self.key]
    else:
        # if history present, don't load
        key = self.key
        if key not in state.committed_state or \
                state.committed_state[key] is NEVER_SET:
            if not passive & CALLABLES_OK:
                return PASSIVE_NO_RESULT

            if key in state.expired_attributes:
                value = state._load_expired(state, passive)

Of AttributeImpl . , state._load_expired SQL-. , , , , "" SQL- , , , "" .

, session-options,

app = Flask(__name__)
CsrfProtect(app)
db = SQLAlchemy(app)

app = Flask(__name__)
CsrfProtect(app)
db = SQLAlchemy(
    app,
    session_options=dict(autoflush=False, autocommit=False, expire_on_commit=False))

, , , , , , , ( ) , "" SQLAlchemy - , .

- SQLAlchemy, "" Python, , , , ?

Answer 1

, , , , - . SQLAlchemy session ORM . , , , , . , expire_on_commit=False.

ORM , . , .. . . SQLAlchemy Core .

, 2 , , " " , , O (nm), n - m . , "join". SQL , , , MySQL , JSON.

, , :

from itertools import groupby
from operator import attrgetter

ids = ['1', '2', '3']  # obviously fake list of ids

# Order the results by `dec_id` for Python itertools.groupby. Cannot
# use your `qfilter()` method as it produces lists, not queries.
decs = HRDecimal.query.\
    filter(HRDecimal.dec_id.in_(ids)).\
    order_by(HRDecimal.dec_id).\
    all()

metas = Meta.query.\
    filter(Meta.dec_id.in_(ids)).\
    order_by(Meta.dec_id).\
    all()

key = attrgetter('dec_id')
decs_lookup = {dec_id: list(g) for dec_id, g in groupby(decs, key)}
metas_lookup = {dec_id: list(g) for dec_id, g in groupby(metas, key)}

combined = [(ident,
             decs_lookup.get(ident, []),
             metas_lookup.get(ident, []))
            for ident in ids]

, , all() , . SQL defaultdict(list):

from collections import defaultdict

decs = HRDecimal.query.filter(HRDecimal.dec_id.in_(ids)).all()
metas = Meta.query.filter(Meta.dec_id.in_(ids)).all()

decs_lookup = defaultdict(list)
metas_lookup = defaultdict(list)

for d in decs:
    decs_lookup[d.dec_id].append(d)

for m in metas:
    metas_lookup[m.dec_id].append(m)

combined = [(ident, decs_lookup[ident], metas_lookup[ident])
            for ident in ids]

, , , "" Python, Core ORM:

decs = HRDecimal.query.\
    filter(HRDecimal.dec_id.in_(ids)).\
    with_entities(HRDecimal.__table__).\
    all()

namedtuple , dict _asdict().