Insert into one to many relationship with multiple data sqlalchemy flask

Question

I have two models that are in a one to many relationship. Inquiry ||------o<- Specialneed

I want to be able to insert multiple records in Specialneed, by using the relationship constructed in Flask SQLalchemy.

I can insert 1 record in Specialneed, like this:

applicationParams={
        'startdate': request.form['startdate'],
        'enddate':  request.form['enddate'],
        'starttime': request.form['starttime'],
        'endtime': request.form['endtime'],
        'budget': request.form['budget'],
        'specialneed': [SpecialNeed(description=request.form['specialNeeds'])]}

But when I try to give 'specialneed'-parameter a list of the specialneeds entered in the form, it gives me the error:

(pymysql.err.InternalError) (1241, 'Operand should contain 1 column(s)') 
[SQL: 'INSERT INTO specialneed (description, iid) VALUES 
    (%(description)s, %(iid)s)'] [parameters: 
        {'description': ['Speaks English', 'Can sing'], 'iid': 11}]

When I give it a list it looks like this in my code:

applicationParams={
        'startdate': request.form['startdate'],
        'enddate':  request.form['enddate'],
        'starttime': request.form['starttime'],
        'endtime': request.form['endtime'],
        'budget': request.form['budget'],
        'specialneed': [SpecialNeed(description=[sn.strip() for sn in request.form['specialNeeds'].split('.')])]}

The sql insert query should look like this to succeed:

SQL: 'INSERT INTO specialneed (description, iid) VALUES 
(%(description)s, %(iid)s)'] [parameters: 
      {'description': 'Speaks English', 'iid': 11}, 
      {'description': 'Can sing', 'iid': 11}]

How should I do to manage it?

Ramesh Ponnusamy · Accepted Answer · 2020-02-07 07:56:44Z

Models are

   class User(db.Model, UserMixin):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    active = db.Column('is_active', db.Boolean(), nullable=False, server_default='1')
    usermobilenumber = db.Column(db.String(100, collation='NOCASE'), nullable=False, unique=True)
    password = db.Column(db.String(255), nullable=False, server_default='')
    roles = db.relationship('UserRoles')

class UserRoles(db.Model):
    __tablename__ = 'user_roles'
    id = db.Column(db.Integer(), primary_key=True)
    user_id = db.Column(db.Integer(), db.ForeignKey('users.id', ondelete='CASCADE'))
    role_id = db.Column(db.Integer(), db.ForeignKey('roles.id', ondelete='CASCADE'))

iterate the request values through for loop and append it.

         @app.route('/usercreation', methods=['POST','GET'])
         def usercreation():
           if request.method == 'POST':
             usermobilenumber = request.form['mobilenumber']
             userpassword = request.form['password']

             # roless is a list(many values)
             roless = request.form.getlist('roles')

             user = User(username=usermobilenumber,password = 
                    generate_password_hash(userpassword))

             #append list values into model object
             for r in roless:
               role = UserRoles(role_id=r)
               user.roles.append(role)

             db.session.add(user)
             db.session.commit()

FluffPeg · Accepted Answer · 2017-05-23 10:43:33Z

0

I found out the answer through a friend. If I change this part:

'specialneed': [SpecialNeed(description=[sn.strip() for sn in request.form['specialNeeds'].split('.')])]

To this:

'specialneed': [SpecialNeed(description=sn.strip()) for sn in reuqest.form['specialNeeds'].split(".")]

It will work. Look at this site for more info about list comprehensions.

answered May 23, 2017 at 10:43

FluffPeg

437 bronze badges

Collectives™ on Stack Overflow

Insert into one to many relationship with multiple data sqlalchemy flask

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related