In SQLAlchemy is it possible to dump and load from a Table (not from a mapped class)?

Question

---

TL;DR Using SQLAlchemy is it possible to do a dumps(…) and load(…) using a Table (not a mapped class)? Just to clarify, I wrote those two tiny tests (currently red) to make my point.

---

Having this mapped class and session:

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.serializer import loads, dumps
from sqlalchemy.orm import mapper, scoped_session
from sqlalchemy.orm.session import sessionmaker


engine = create_engine('sqlite:///:memory:', echo=False)
Base = declarative_base(bind=engine)
session = scoped_session(sessionmaker())



class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    email = Column(String(140), index=True, unique=True)

Using the User mapped class I can dump and load data:

>>> # create a record
>>> Base.metadata.create_all()
>>> session.add(User(email='john@alchemydumps'))
>>> session.commit()
>>>
>>> # use dumps
>>> with open('dummy_file', 'wb') as file_handler:
...     file_handler.write(dumps(session.query(User).all()))
...
490
>>>
>>> # reset the database
>>> Base.metadata.drop_all()
>>> Base.metadata.create_all()
>>>
>>> # use loads
>>> with open('dummy_file', 'rb') as file_handler:
...     for row in loads(file_handler.read()):
...         session.merge(row)
...
<__main__.User object at 0x10977ea20>
>>> session.commit()
>>> 
>>> # assert data is back
>>> session.query(User).count()
1
>>>

But using a Table instead does not work:

>>> …
>>>
>>> # use a table instead (User.__table__, instead of User)
>>> with open('dummy_file', 'wb') as file_handler:
...    file_handler.write(dumps(session.query(User.__table__).all()))
...
115
>>> Base.metadata.drop_all()
>>> Base.metadata.create_all()
>>>
>>> with open('dummy_file', 'rb') as file_handler:
...     for row in loads(file_handler.read()):
...         session.merge(row)
...

This code crashes with an sqlalchemy.orm.exc.UnmappedInstanceError:

Traceback (most recent call last):
File "<stdin>", line 3, in <module>
File "/Users/cuducos/.virtualenvs/alchemydumps/lib/python3.5/site-packages/sqlalchemy/orm/scoping.py", line 157, in do
    return getattr(self.registry(), name)(*args, **kwargs)
File "/Users/cuducos/.virtualenvs/alchemydumps/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 1702, in merge
    object_mapper(instance)  # verify mapped
File "/Users/cuducos/.virtualenvs/alchemydumps/lib/python3.5/site-packages/sqlalchemy/orm/base.py", line 266, in object_mapper
    return object_state(instance).mapper
File "/Users/cuducos/.virtualenvs/alchemydumps/lib/python3.5/site-packages/sqlalchemy/orm/base.py", line 288, in object_state
    raise exc.UnmappedInstanceError(instance)
sqlalchemy.orm.exc.UnmappedInstanceError: Class 'sqlalchemy.util._collections.KeyedTuple' is not mapped

This message is already too long, so I avoided talking much about the context of the probelm. But I do have a situation in which I need to dump and load non-mapped classes… and I'm trying to find a solution for that. (Disclaimer: I wrote this package 2 years ago when I was a newbie in Python; yeah, the code looks terrible — but I have been fully refactoring it in the last weeks.)

Any ideas or recommendations for this newbie in SQLAlchemy?

Answer 1

After @univerio's comments I could better understand the situation and fixed, yay!

The thing is that if the SQLAlchemy dumps receives a Table instance, the results are not related to the table, they are purely a KeyedTuple. So I had to:

1. bring in a mapped class when it was a KeyedTuple load
2. transform the KeyedTuple into a dictionary (which is easy, as KeyedTuple has a _asdict() method)
3. and then merge (as it was originally my intention)

The tests are now green, but to summarize this is what I needed:

with open('dummy_file', 'rb') as file_handler:
...     for row in loads(file_handler.read()):
...         if isinstance(row, KeyedTuple):
...             row = User(**row._asdict())
...         session.merge(row)
>>> session.commit()