在用户界面中实现多对多关联数据的管理与SQL操作

<form action="/updateUserYards" method="post">
    <input type="hidden" name="userId" value="[当前用户ID]">

    <h3>关联场地:</h3>
    <div>
        <!-- 遍历所有场地 -->
        <label>
            <input type="checkbox" name="selectedYards[]" value="1" [如果Yard ID 1已关联则checked]> 场地A
        </label><br>
        <label>
            <input type="checkbox" name="selectedYards[]" value="2" [如果Yard ID 2已关联则checked]> 场地B
        </label><br>
        <label>
            <input type="checkbox" name="selectedYards[]" value="3" [如果Yard ID 3已关联则checked]> 场地C
        </label><br>
        <!-- ...更多场地... -->
    </div>

    <button type="submit">保存</button>
</form>

-- 查询所有场地
SELECT id, name FROM Yards;

-- 查询特定用户已关联的场地
SELECT Y.id, Y.name
FROM Yards Y
JOIN UsersYardsLink UYL ON Y.id = UYL.yard_id
WHERE UYL.user_id = [当前用户ID];

# 假设从表单获取的 userId 和 selected_yard_ids
user_id = request.form.get('userId')
submitted_yard_ids = set(request.form.getlist('selectedYards[]')) # 转换为集合方便比较

# 查询当前数据库中已关联的场地ID
current_yard_ids = set()
# 执行 SQL: SELECT yard_id FROM UsersYardsLink WHERE user_id = :user_id
# 假设查询结果是 [(1,), (3,)]
for row in db_query_result:
    current_yard_ids.add(row[0])

# 计算需要新增和删除的关联
yards_to_add = submitted_yard_ids - current_yard_ids
yards_to_remove = current_yard_ids - submitted_yard_ids

# 执行数据库操作
# 使用事务确保数据一致性
try:
    db_connection.begin_transaction()

    # 删除不再选中的关联
    for yard_id in yards_to_remove:
        # 执行 SQL: DELETE FROM UsersYardsLink WHERE user_id = :user_id AND yard_id = :yard_id
        db_connection.execute("DELETE FROM UsersYardsLink WHERE user_id = %s AND yard_id = %s", (user_id, yard_id))

    # 插入新增的关联
    for yard_id in yards_to_add:
        # 执行 SQL: INSERT INTO UsersYardsLink (user_id, yard_id) VALUES (:user_id, :yard_id)
        db_connection.execute("INSERT INTO UsersYardsLink (user_id, yard_id) VALUES (%s, %s)", (user_id, yard_id))

    db_connection.commit()
except Exception as e:
    db_connection.rollback()
    # 记录错误并向用户显示消息
    print(f"Error updating user yards: {e}")

INSERT INTO UsersYardsLink (user_id, yard_id)
VALUES ([当前用户ID], [待插入的Yard ID]);

INSERT INTO UsersYardsLink (user_id, yard_id)
VALUES
    ([当前用户ID], [Yard ID 1]),
    ([当前用户ID], [Yard ID 2]),
    -- ...
    ([当前用户ID], [Yard ID N]);

DELETE FROM UsersYardsLink
WHERE user_id = [当前用户ID] AND yard_id = [待删除的Yard ID];

DELETE FROM UsersYardsLink
WHERE user_id = [当前用户ID] AND yard_id IN ([Yard ID 1], [Yard ID 2], ..., [Yard ID N]);