SQLAlchemy inner join two Tables

Question

I have a Database witch stores some user information.

It's separated into two tables that I declared and want to query in my Python code using SQLAlchemy:

class Users(users_db.Model, UserMixin):
    id = users_db.Column(users_db.Integer, primary_key=True)
    first_name = users_db.Column(users_db.String(50))
    last_name = users_db.Column(users_db.String(50))
    username = users_db.Column(users_db.String(50), unique=True)
    password = users_db.Column(users_db.String(255))
    role = users_db.Column(users_db.Integer, users_db.ForeignKey('roles.id'), default=1) 

# Users Roles
class Roles(users_db.Model):
    id = users_db.Column(users_db.Integer, primary_key=True)
    description = users_db.Column(users_db.String(50))
    users = users_db.relationship('Users')

So now if i want to query the usersTable, i use Users.query.all(), witch gives me a List of all rows. How can i create a query that joins the Roles.description inside of the Users.role?

Similar SQL code would be:

SELECT users.first_name, users.last_name, users.username, users.password, roles.description FROM users
INNER JOIN roles ON users.role=roles.id

So the Output i want to have is like

| first_name | last_name | ... | description |
|------------|-----------|-----|-------------|
|guest       |           |     | guest       |
|Admin       |           |     | admin       |

edit:

I have now bypassed the problem by querying the whole roles table with Roles.query.all() and iterating through the table by a for loop and a if statement inside the jinja html code:

{% for role in roles %}
    {% if role.id == user_data.role %}
        <td>{{role.description}}</td>
    {% endif %}
{% endfor %}

SQLAlchemy inner join two Tables

0 Answers0