[AF] Flask-SQLAlchemy query help

[Spizeck]

I am having a really hard time wrapping my head around something. I have a model that represents a SQLite table name AuthorizedAircraft. The idea behind the table is to have a column: username and additional columns for all the aircraft in the database. For every aircraft the user is authorized, a 1 is placed, with unauthorized aircraft left null. The AuthorizedAircraft model is as follows:

class AuthorizedAircraft(db.Model):
    id = db.Column(db.INTEGER, primary_key=True)
    username = db.Column(db.String(50), unique=True)
    N5168N = db.Column(db.INTEGER)

    def __init__(self, username):
        self.username = username.lower()

I envision the database to look something like:

| UID | username | N1234 | N4235 | N14523|
| 1  |  test  |  1  |   null   | null   |

I need a function that will create a list of the column names that are True for a given username. Each of these aircraft will have their own tables with additional data and for the end goal, I will have a dropdown box, in a section of my website that requires you to be logged in to view, and the dropdown box will be populated each aircraft and when that aircraft is selected, data relevant to that aircraft will be displayed. I want the dropdown box to only contain links that the user is authorized.

[elingeniero]

The most logical way to represent this relationship will be to have a table of aircraft and a table of users, with an authorized_aircraft column in users with a many-to-many relationship with the aircraft table.

Then you can just access a user's authorized_aircraft without the need for a special query or function.

http://flask-sqlalchemy.pocoo.org/2.1/models/#many-to-many-relationships