Adding multiple columns in a table such as using a for / while loop in flask-sqlalchemy

Question

I want to create a child table using Flask-SQLAlchemy that holds around 600 columns.
Each column is supposed to be a different hobby with a boolean value of true or false (whether or not he has this hobby).
However I do have an issue. Using Flask-SQLAlchemy's Model to create it seems problematic since I will have to write each field myself.
i.e:

class hobbies(database.Model):
   id = db.Column(db.Integer, primary_key=True)
   user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
   hobby1 = db.Column(db.Boolean)
   hobby2 = db.Column(db.Boolean)
   hobby3 = db.Column(db.Boolean)
   ....
   ....
   hobby600 = db.Column(db.Boolean)

(database stands for the variable that holds - database = SQLAlchemy(app))

Is there a way such as using a for or a while loop to add all of the columns to the table?

Thank you for your time.

faris404 · Accepted Answer · 2021-07-29 16:50:45Z

3

This is bad table design(no offence), instead of this you can create table as given below

class Hobbies(database.Model):
   id = db.Column(db.Integer, primary_key=True)
   hobby = db.Column(db.String(50))

class UserHobbies(database.Model):
   user_id = db.Column(db.Integer, db.ForeignKey('user.id'),primary_key=True)
   hobbie_id = db.Column(db.Integer, db.ForeignKey('hobbies.id'),primary_key=True)

Instead of creating 600 columns in hobbies table just create 600 rows and create another table UserHobbies for many to many relationship with users and hobbies.
You can also utilize bulk_insert_mappings(ModelName,list_data) function for inserting bulk data in to hobbies table.

answered Jul 29, 2021 at 16:50

faris404

3731 gold badge4 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shnap Over a year ago

I have a related question regarding many to many relationship like your solution. If I have a similar thing I want to save per person as well as compare between users for example if they like basketball, soccer and hockey (3 Boolean collumns). Is it better to make a many to many relationship like your solution or since it's only 3 fields I can just add them as columns in the user's table?

faris404 Over a year ago

From my research the more standard ways is creating many to many relationship.But in this case I prefer creating three columns for each hobbies like you since its only three fields.

Collectives™ on Stack Overflow

Adding multiple columns in a table such as using a for / while loop in flask-sqlalchemy

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related