CODEWITHSUNDEEP
pythonsqlalchemy
Sinclair Akoto
Sinclair Akoto

Reputation: 67

query a joined table with sqlalchemy

I have two tables one for information and the other for salary. I have successfully joined the two tables together in my postgres CLI and in my flask app, but having problems trying to query the data in sqlalchemy.

I have tried my query on my psql CLI and works fine!

SELECT date, name, profession, wage FROM test2_table JOIN salary ON salary.test2_id = test2_table.id WHERE profession = 'media';

I suppose my main question is how could I do this in sqlalchemy?

Database class

class Test_db_02(Base):
    __tablename__ = 'test2_table'
    id = Column('id', Integer, primary_key=True)
    date = Column('date', String(10))
    age = Column('age', Integer)
    profession = Column('profession', String(60))
    city = Column('city', String(60))
    country = Column('country', String(60))

    def __init__(self, date, name, age, profession, city, country):
        self.date = date
        self.name = name
        self.age = age
        self.profession = profession
        self.city = city
        self.country = country

class Salary(Base):
    __tablename__ = 'salary'
    id = Column('id', Integer, primary_key=True)
    wage = Column('wage', String(20))
    test2_id = Column('test2_id', Integer, ForeignKey('test2_table.id')
    wages = relationship("Test_db_02", backref="salary", primaryjoin="Test_db_02.id == Salary.test2_id")

My query in python:

@app.route('/reports', methods=['GET', 'POST'])
def reports():
    if request.method == 'GET':
        return render_template('reports.html')
    else:
        if request.form.get("report_options) == "media_prof":
            db_entry = session.query(Test_db_02).join(Test_db_02.salary).filter(Test_db_02.profession=='media')
        media_prof = db_entry.all()
        return render_template('reports.html', media_prof=media_prof)

jinja template in reports.html:

{% if media_prof %}
    <h2>Media Freelance Reports</h2>
        <tr>
            <th>ID</th>
            <th>NAME</th>
            <th>PROFESSION</th>
            <th>SALARY</th>
        </tr>
    {% for d in media_prof %}
        <tr>
            <th>{{ d.id }}</th>
            <th>{{ d.name }}</th>
            <th>{{ d.profession }}</th>
            <th>{{ d.salary }}</th>
        </tr>
    {% endfor %}
{% endif %}

Fortunately it doesn't crash my web app but instead of producing the salary for each media freelancer I get: [<__main__.Salary object at 0x00CF11B0>]

I have a feeling it must be the way I queried the database in the python app or the way I am calling the variable in the jinja..

Upvotes: 2

Views: 2460

Answers (1)

bagerard
bagerard

Reputation: 6364

Since you are using SQLAlchemy in the ORM mode (as opposed to core mode), the result of querying 

db_entry = session.query(Test_db_02).join(Test_db_02.salary).filter(Test_db_02.profession=='media')
media_prof = db_entry.all()

Is a list of Test_db_02 instances, with each of the instance having a .salary attribute being a Salary instance. So its normal that when you use this in your jinja template:

{% for d in media_prof %}
    <tr>
        <th>{{ d.id }}</th>
        <th>{{ d.name }}</th>
        <th>{{ d.profession }}</th>
        <th>{{ d.salary }}</th>
    </tr>
{% endfor %}

Since d.salary is an actual list of Salary instance (due to your backref and the fact that your have a 1-to-many relationship), it means that you have access to the attribute of each salary object, e.g: .id, or .wage e.g:

{% for d in media_prof %}
    <tr>
        <th>{{ d.id }}</th>
        <th>{{ d.name }}</th>
        <th>{{ d.profession }}</th>
        {% for s in media_prof.salary %}
            <th>{{ s.id }}</th>
            <th>{{ s.wage }}</th>
        {% endfor %}
    </tr>
{% endfor %}

Note: consider renaming your backref "salary" into "salaries" to make that more explicit

EDIT: Note that it is easier to troubleshoot things outside of the template. From a terminal, I'm able to get a proper output using the following simplified case:

# Load an object in the db with 2 salaries attached to it
t = Test_db_02(city='NY')
s1 = Salary(wage='crazy rich', test2_id=t)
s2 = Salary(wage='mega rich', test2_id=t)
t.salaries = [s1, s2]

session = Session()
session.add(t)
session.commit()

# query & print
session2 = Session()
for test in session2.query(Test_db_02).all():
    print(test.id, test.city)
    for salary in test.salaries:
        print(salary.id, salary.wage)

which prints

1 NY
1 crazy rich
2 mega rich

Upvotes: 2

Related Questions