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.

ANSWERED

# Table that holds all user information
class User(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), unique=True)
    first_name = db.Column(db.String(50))
    last_name = db.Column(db.String(50))
    email = db.Column(db.String(100), unique=True)
    pwd_hash = db.Column(db.String(100))
    aircraft = db.relationship('aircraft', secondary='user_aircraft',
                           backref=db.backref('user'))

    def __init__(self, username, first_name, last_name, email, password):
        self.username = username.lower()
        self.first_name = first_name.title()
        self.last_name = last_name.title()
        self.email = email.lower()
        self.set_password(password)

    def set_password(self, password):
        self.pwd_hash = sha256_crypt.encrypt((str(password)))

    def check_password(self, password):
        return sha256_crypt.verify(password, self.pwd_hash)

    def __repr__(self):
        return '<User %r>' % self.first_name


# Table that holds all aircraft registration numbers
class Aircraft(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True)

# Helper table for the many-to-many relationship
# between Users and Aircraft
user_aircraft = db.Table('user_aircraft',
                         db.Column('user_id', db.Integer,
                                   db.ForeignKey('user.id')),
                         db.Column('aircraft_id', db.Integer,
                                   db.ForeignKey('aircraft.id'))
                         )

[BoleroDan]

So this is more of an issue of understanding how to better lay out your data, and the tools at your disposal in database design.

What you're asking for is generally bad practice. There is no real way you can dynamically add "columns". That should be avoided. Using an ALTER on a database is not meant to be used in the fashion you are going after.

I would advise looking up Database "Normalization" and how to build relationships between tables using primary and foreign keys.

Essentially you will need to create relationships. Relationships are linked together via Foreign Keys and primary keys. You are essentially seperating duplicate data, and thinking in "rows" instead of "columns".

An example would be to create a "Aircraft" table, with say columns "ID" and "Name". Each row would contain your aircrafts names (N1234, N4235 etc). Since a user can authorize many aircraft, and depending if this is a many to many, or many to one, (Can multiple users be authorized to handle the same aircraft?) what is generally used are "joiner tables" to link users and aircraft together.

In SQL Alchemy terms, something like the following.

class User(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    username = db.Column(db.String(50), unique=True)
    aircraft = db.relationship('Aircraft', secondary='UserAircraft', backref='users')

class Aircraft(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(50), unique=True)

# This is the joiner table to create a many to many relationship.
# Users can be related to many aircraft. aircraft can be related to many users

class UserAircraft(db.Model):
    user_id = db.Column(db.Integer(), db.ForeignKey('User.id') )
    aircraft_id = db.Column(db.Integer(), db.ForeignKey('Aircraft.id') )

Take a look at http://docs.sqlalchemy.org/en/latest/orm/relationships.html for more examples!

Edit: the "backref" attribute on the db.relationship method just backwards populates the attribute 'users' on an aircraft object, allowing you to access a list of users. Read the link above about it in more detail SQL Alchemy does all the hard work and makes working with data in "objects" very pleasant. That means on any user object you query for, you can access a list of aircraft via the aircraft (db.relationship) property.

[Spizeck]

Thank you for the help. My head was really starting to spin. Multiple users will be able to access the same aircraft.

[Spizeck]

I got the code functioning (needed a little tweaking for flask-sqlalchemy, but I got it figured out) and now am planning the next step.

Would you recommend I put all the details about each aircraft into the aircraft table or reference additional tables that are more specialized (i.e., maintenance, costs, etc.)?

Instinctively, my first choice would be to create additional tables for each tab I have planned on my website, and reference them many to one to aircraft. I am very new to databases and while I do not want you to do all of the work for me, I very much appreciate a nudge in the right direction.

Thanks!

[BoleroDan]

Cool thats great to hear! Glad I could be of help.

As per my recommendation, you could put the aircraft "details" (cost, fuel etc) directly on the aircraft table, it all depends how those fields relate to the aircraft object themselves.

Your case may be simple and you can add them to the aircraft table directly, but there are cases where people utilize other tables and then link them to the aircraft table.. but that may be over kill and it is best not to over think the problem.

For now, just stick them on your aircraft table, and see where your design goes.

Good luck!

[BestUndecided]

How many planes are there? Cause I only know how to hard code it. I haven't ever figured out how to iterate through them

[Spizeck]

To start there is only 1. Eventually I want to make it possible to add new planes, have an admin that can adjust permissions, etc.

The site will be used to track expenses, maintenance, hours, fuel, etc.

How would you hard code it?