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

You can make use of user.__table__.columns:

def get_model_dict(model):
    return dict((column.name, getattr(model, column.name)) 
                for column in model.__table__.columns)

Usage:

user = User()
get_model_dict(user)

There are also other options at:

• Convert sqlalchemy row object to python dict

Answer 2

this is how.

getattr(User, 'id')