Flask SQLAlchemy - Access record data without column name

Question

Long time reader, first time asker. I'm a little unsure of how to express what I am trying to accomplish. Using Flask and SQLAlchemy, I am attempting to put data from a record (in this case, system.xxx) into a table. When I use system.xxx, I also get the column name, as pictured below:

what I don't want

This is the code I'm using in my jinja template:

{% block page_content %}
<div class="page-header">
    <h1>{{ system.serial }}</h1>
</div>
<hr width="80%">
<table class="table", border="5">
    <tr>
        <th>Status</th>
        <th>Assignee</th>
    </tr>
    <tr>
        <td>{{ system.status }}</td>
        <td>{{ system.assignee }}</td>
    </tr>
</table>
{% endblock %}

How can I access system.xxx without getting the 'Status u' or 'Assignee u' column info? I've spent literally hours on the inter webs trying to figure this out, but I don't know how to properly phrase the question.

Added: the flask view.

@main.route('/system/<serial>', methods=['GET'])
def system(serial):
    system = System.query.filter_by(serial=serial).first_or_404()
    return render_template('system.html', system=system)

Added: the models in question

#table of individual systems
class System(db.Model):
    __tablename__ = 'systems'
    id = db.Column(db.Integer, primary_key=True)
    serial = db.Column(db.String(64))
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    status_id = db.Column(db.Integer, db.ForeignKey('statuses.id'))
    assignee_id = db.Column(db.Integer, db.ForeignKey('assignees.id'))
    admin = db.Column(db.String)

    def __repr__(self):
        return '<System %r>' % self.serial

#table of assignees
class Assignee(UserMixin, db.Model):
    __tablename__ = 'assignees'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    email = db.Column(db.String(64), unique=True, index=True)
    systems = db.relationship('System', backref='assignee', lazy='dynamic')

    def __repr__(self):
        return '<Assignee %r>' % self.name

#table of status options
class Status(db.Model):
    __tablename__ = 'statuses'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    systems = db.relationship('System', backref='status', lazy='dynamic')

    @staticmethod
    def insert_statuses():
        statuses = ['Available', 'Loaned', 'Scrapped']
        for status in statuses:
            s = Status(name=status)
            db.session.add(s)
        db.session.commit()

    def __repr__(self):
        return '<Status %r>' % self.name

Can you share the flask view that generate this template please ? — lee-pai-long, Sep 12 '17 at 09:42
Are `status` and `assignee` actually relationship properties? Please include your model. In other words produce a [mcve]. Prolly — Ilja Everilä, Sep 12 '17 at 09:55
Thanks for the feedback. I've updated the post with the models in question. — Mark Edmondson, Sep 14 '17 at 05:03

score 1 · Accepted Answer · answered Sep 14 '17 at 07:29

1

As I thought you're accessing the related Status and Assignee objects through a relationship property in your template, which then renders them using their string representation. Instead you should access the attributes you want to display:

        <td>{{ system.status.name }}</td>
        <td>{{ system.assignee.name }} {{ system.assignee.email }}</td>

Since you'll always be accessing those relationships you should consider eagerloading them.

answered Sep 14 '17 at 07:29

Ilja Everilä

50,538
7
126
127

That's exactly what I was looking for, thanks for the help! I'll look into eagerloading. – Mark Edmondson Sep 14 '17 at 15:59

score 0 · Answer 2 · answered Sep 12 '17 at 11:52

0

You can explicitly query for only the columns you want.

first_result = session.query(System.xxx).filter(System.serial == serial).scalar()

You may also wish to use one_or_none(), one() or all() depending on your use case.

answered Sep 12 '17 at 11:52

Ivan Choo

1,997
15
15

I am able to get the information I'm looking for ( system.status), but it also includes the column name, so it shows up as , instead of simply Loaned. – Mark Edmondson Sep 14 '17 at 06:13
I'm guessing `System.status` is of `Enum` type. If you wish to get the string representation of `status`, you'll have to use `str(system.status)` or `system.status.value` or whatever your actual Enum type implementation offers. – Ivan Choo Sep 14 '17 at 06:28

Flask SQLAlchemy - Access record data without column name

2 Answers2