How to get column and values dictionary in SQLAlchemy model?

Question

I have the following table:

Base = declarative_base()
metadata = Base.metadata

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String(255))
    email = Column(String(255))

Is it possible to get a dictionary that for empty record will return something like:

{'id': None, 'username': None, 'email': None}

And if I do:

user = User()
user.username = "testuser"
user.email = "[email protected]"

I want to be able to get the dictionary with the corresponding values. Of course if I save the record I'd expect it to have the id there as well.

Answer 1

In most scenarios, column name is fit for them. But maybe you write the code like follows:

class UserModel(BaseModel):
    user_id = Column("user_id", INT, primary_key=True)
    email = Column("user_email", STRING)

the column.name "user_email" while the field name is "email", the column.name could not work well as before.

There is a another trick by using Python metaclass, the sample code:

sqlalchemy_base_model.py

Answer 2

You can use the following method which is inspired by the answer alecxe gave.

def get_model_dict(model, row):
    if isinstance(row, model):
        columns = [x.name for x in list(model.__table__.columns)]
        return {x: getattr(row, x) for x in columns}
    else:
        raise ValueError(f"The provided row is not of type {model.__table__.name.title()}")

Now all you have to do is pass the model and the row of the same models to get the dictionary representation of the data.