Aliasing field names in SQLAlchemy model or underlying SQL table

Question

Is it possible to alias (My)SQL columns or SQLAlchemy object attributes in either the table's schema or the model's class definition?

For instance, given the following table:

Users
---------------------
username | ...

I would like to have the following table representation:

Users
---------------------
id | username | ...

Where User.id maps to User.username without duplicating this data.

Embedding this into the table schema would be ideal, but a solution with the ORM would be sufficient.

class User():
    __tablename__ = 'Users'
    username = Column(STRING, primary_key=True, alias='id')

---

For the details

My use case is that I'm storing scraped data from various websites. I'm indexing this data with the best user ID that I have, which may be a username, numeric ID, or even a full name.

However, in order to normalize operations across tables I'd like each table to have an 'id' field, mapped to the best possible ID.

Of course I have two alternatives:

1. Renaming the selected ID as 'Users.id', losing readability for analysts.
2. Duplicating the data from the selected ID into 'Users.id', making database write operations more complex in addition to wasting storage space.

Answer 1

Here is an elegant solution described in the SQLAlchemy Docs, which does exactly what you want:

from sqlalchemy.orm import synonym
class User():
    __tablename__ = 'Users'
    username = Column(STRING, primary_key=True)
    id = synonym('username')

It works just as expected:

>>> john = session.query(User).filter_by(id='John Doe').first()
>>> print([john.id, john.username])
['John Doe', 'John Doe']
>>> john.id is john.username
True

By the way, the User class must be a subclass of SQLAlchemy declarative_base() class:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class User(Base):
    ...

Go to docs for further reference.