如何在表中插入多行数据以维护1NF?现在我有一个html表单,可以创建一个组,您可以在其中添加/邀请组成员加入。您可以添加/邀请多个或一个:组成员的输入可能因用户而异。我想获取用户输入并将其添加到维护1NF的表中。我想在MySQL中创建一个存储过程,一旦我有了值列表并进行“I”insert调用(I是用户邀请的成员数),就调用它
创建团队的我的App.route:现在我正在将数据插入tb_团队表,接下来我想用用户和相关团队id填充团队成员表
@app.route('/create-team/', methods=['GET', 'POST'])
@login_required
def create_team():
msg = ''
if request.method == 'POST':
# Create variables for easy access
team_name = request.form['team_name']
invite = request.form['invite']
team_desc = request.form['content']
if not team_name:
msg='Please enter a group name'
return render_template('create_team.html', msg=msg, team_name=team_name, invite=invite, content=team_desc)
if not invite:
msg = 'Please invite users to the team'
return render_template('create_team.html', msg=msg, team_name=team_name, invite=invite, content=team_desc)
if not invite and not team_name:
msg = 'Please fill out the form'
return render_template('create_team.html', msg=msg)
cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
cursor.execute('SELECT user_id from tb_user WHERE user_name = %s', (invite,))
invite_id = cursor.fetchone()
invite_id_final = invite_id['user_id']
# Check if team_name exists in tb_team table
cursor.execute('SELECT * FROM tb_team WHERE team_name = %s', (team_name,))
name = cursor.fetchone()
# If team_name currently exists, show error that it is taken
if name:
msg = 'This Project Name is already taken!'
return render_template('create_team.html', msg=msg, team_name=team_name, invite=invite, content=team_desc)
else:
msg = 'You have successfully created a team!'
cursor.execute("INSERT INTO tb_team (team_name)"
" VALUES (%s)", (team_name))
mysql.connection.commit()
return redirect(url_for('team_page', group_name=team_name))
return render_template('create_team.html')
以下是我的数据库设计以及我希望最终实现的目标:
使用此处的虚拟数据:
编写存储过程是插入这些组成员的正确方法吗
传递要添加字符串变量的用户列表,使其通过while循环,获取每个由逗号分隔的用户,并将其添加到表中,直到字符串变量中不再存在其他用户?(请参见下面关于我的存储过程的代码)
DELIMITER
CREATE PROCEDURE basicInsert()
BEGIN
SET @myArrayOfUsers = '100,102,105,'
WHILE(LOCATE(',', @myArrayOfUsers) > 0) DO
SET @value = ELT(1, @myArrayOfUsers);
SET @myArrayOfUsers= SUBSTRING(@myArrayOfUsers, LOCATE(',',@myArrayOfUsers) + 1);
INSERT INTO `group_member` VALUES(1001, @value);
END WHILE;
END;
DELIMITER ;
CALL basicInsert();
谢谢大家!!我们衷心感谢您的帮助
你的程序有一些大问题
ELT不会给你想要的东西。它实际上返回完整的@MyArrayFuser
如果没有临时表,调试循环是很棘手的,但是当您不确定代码的结果是什么时,请临时删除该循环并添加更多@user变量,然后逐行调试它
我测试,正如你所看到的,在结尾没有逗号的情况下
就像你只想做的那样!NF,我不检查已经存在的入口
相关问题 更多 >
编程相关推荐