CODEWITHSUNDEEP
python-3.xsqlalchemy
ankush981
ankush981

Reputation: 5417

Column-name mapping in SQLAlchemy

I'm just two days into learning SQLAlchemy, and have a question regarding mapping of column names. First off, the following statement in the official docs threw me off balance:

Matching columns on name works for simple cases but can become unwieldy when dealing with complex statements that contain duplicate column names or when using anonymized ORM constructs that don’t easily match to specific names. Additionally, there is typing behavior present in our mapped columns that we might find necessary when handling result rows.

Ugh, what?! An example or two of what this paragraph is trying to say would have been great, but never mind; it's a task for another day.

I kind of understood that sometimes the field list generated by our query will not map perfectly with the model, so we can specify expected columns positionally. This got me trying to think up an example where this mapping might be useful. So I thought, how about selecting a column from the table that is calculated on the fly? I simulated this in a sqlite database using the random() function like this:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()

Session = sessionmaker(bind=engine)
session = Session()

class User(Base):
    __tablename__ = 'users'
    id       = Column(Integer, primary_key=True)
    name     = Column(String)
    fullname = Column(String)
    password = Column(String)

    def __repr__(self):
        return "User<name={}, fullname={}, password={}>".format(self.name, self.fullname, self.password)

# Create all tables
Base.metadata.create_all(engine)

ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
session.add(ed_user)
session.add_all([
    User(name='wendy', fullname='Wendy Williams', password='foobar'),
    User(name='mary', fullname='Mary Contrary', password='xxg527'),
    User(name='fred', fullname='Fred Flinstone', password='blah')
])
session.commit()

stmt = text("SELECT name, id, fullname, random() FROM users WHERE name=:name")
stmt = stmt.columns(User.name, User.id, User.fullname ...

Now what? I can't write a column name because none exists, and I can't add it to the model definition because this column will be created unnecessarily. All I wanted to get some extra information from the database (like NOW()) that I can use in code.

How can this be done?

Upvotes: 1

Views: 5146

Answers (3)

Tim D
Tim D

Reputation: 690

Try explicitly listing column objects. This way "random()" can be any arbitrary SQL calculated column...

# You may have already imported column, but for completeness:
from sqlalchemy import column

# As an example:
find_name = 'wendy'

stmt = text("SELECT name, id, fullname, (random()) as random_value FROM users WHERE name=:name")
stmt = stmt.columns(column('name'), column('id'), column('fullname'), column('random_value'))

users = session.query(column('name'), column('id'), column('fullname'), column('random_value')).from_statement(stmt).params(name = find_name)
for user in users:
    print(user.id, user.name, user.fullname, user.random_value)

Upvotes: 1

ankush981
ankush981

Reputation: 5417

All right, so I seem to have figured this out myself (damn, that's twice in a day! The SQLAlchemy community here seems dead, which is forcing me to search for answers myself ... not a very bad thing, though!). Here's how I did it:

from sqlalchemy import func
session.query(User.name, User.id, User.fullname, func.current_timestamp()).filter(User.name=='ed').all()

Upvotes: -1

r-m-n
r-m-n

Reputation: 15120

You can use column labels

from sqlalchemy import func

users = session.query(User.id, User.name, User.fullname, func.random().label('random_value'))
for user in users:
    print(user.id, user.name, user.fullname, user.random_value)

Upvotes: 2

Related Questions