基于MySQL数据库的Pyomo线性规划建模约束与执行问题排查

核心问题分析 #

• 布尔逻辑约束无效：Pyomo线性规划求解器不支持直接在约束中使用and、== True这类布尔逻辑，也不能直接调用判断函数（如is_weekend）处理决策变量
• 变量未初始化错误：求解前调用value(model.xxx)会报错，因为变量还未被求解器赋值；约束中错误尝试用fix()修改变量属于建模逻辑错误

针对性修正方案 #

1. 布尔逻辑约束线性化 #

将日期是否为周末的判断转化为线性约束：

• 预计算所有可能日期的周末状态，存入字典
• 引入二进制变量标记对应日期是否为周末
• 使用大M法将布尔判断转化为线性等式约束

2. 修复变量初始化与value()调用问题 #

• 移除所有求解前的value()调用，约束直接基于决策变量构建
• 用等式约束替代fix()操作，实现“同菌种批次起始日期相同”的逻辑
• 修正目标函数的索引关联，确保数据库产量数据与决策变量正确匹配

3. 其他建模错误修复 #

• 用date_range_to_int正确转换日期为序数集合
• 修复get_seed_days_range的字段索引错误
• 移除未生效的unique_incubation_start_dates约束

修正后的完整代码 #

from pyomo.environ import ConcreteModel, Set, Var, Objective, Constraint, NonNegativeIntegers, Binary, SolverFactory, SolverStatus, TerminationCondition
import datetime

# Get the start and end dates from the dates dictionary
start_date_1 = dates["start_date_1"]
start_date_2 = dates["start_date_2"]
end_date_1 = dates["end_date_1"]
end_date_2 = dates["end_date_2"]

def date_range_to_int(start_date, end_date):
    start_ordinal = start_date.toPython().toordinal()
    end_ordinal = end_date.toPython().toordinal()
    return list(range(start_ordinal, end_ordinal+1))

# 预计算所有日期的周末状态
def precompute_weekend_status(date_list):
    weekend_status = {}
    for date_ord in date_list:
        date_obj = datetime.fromordinal(date_ord)
        weekend_status[date_ord] = 1 if date_obj.weekday() >=5 else 0
    return weekend_status

# Retrieve information from the gp_predictions table
query = "SELECT mold_id, plate_days, seed_days, incubation_days, pk, `lot*weeks`, predicted_average_yield_per_liter FROM gp_predictions"
cursor.execute(query)
prediction_list = cursor.fetchall()

# 构建菌种到预测数据的映射，提升查询效率
mold_pred_map = {x[0]: x for x in prediction_list}

# Create a model
model = ConcreteModel()

# 转换日期范围为序数
incubation_date_range = date_range_to_int(start_date_1, start_date_2)
harvest_date_range = date_range_to_int(end_date_1, end_date_2)

# Define sets
model.selected_mold_ids = Set(initialize=selected_mold_ids)
model.lots = Set(initialize=range(1, len(lots)+1))
model.incubation_start_dates = Set(initialize=incubation_date_range, within=NonNegativeIntegers)
model.harvest_dates = Set(initialize=harvest_date_range, within=NonNegativeIntegers)
# 覆盖所有可能的起始日期，用于周末状态映射
all_possible_dates = set(incubation_date_range + harvest_date_range + 
                         [d-12 for d in incubation_date_range])
model.all_dates = Set(initialize=all_possible_dates, within=NonNegativeIntegers)

# 预计算周末状态字典
weekend_status = precompute_weekend_status(model.all_dates)

# Define the decision variables
model.plate_start_dates = Var(model.selected_mold_ids, within=NonNegativeIntegers)
model.seed_start_dates = Var(model.selected_mold_ids, within=NonNegativeIntegers)
model.inc_start_dates = Var(model.selected_mold_ids, within=model.incubation_start_dates)
model.plate_days = Var(model.selected_mold_ids, within=NonNegativeIntegers, initialize=6)
model.seed_days = Var(model.selected_mold_ids, within=NonNegativeIntegers, initialize=6)
model.incubation_days = Var(model.selected_mold_ids, within=NonNegativeIntegers, initialize=10)
model.harvest_start_dates = Var(model.selected_mold_ids, within=model.harvest_dates)

# 二进制变量：标记日期是否为周末
model.is_plate_weekend = Var(model.selected_mold_ids, within=Binary)
model.is_seed_weekend = Var(model.selected_mold_ids, within=Binary)
model.is_harvest_weekend = Var(model.selected_mold_ids, within=Binary)

# ---- Objective Function --- #
def obj_rule(model):
    total = 0
    for mold_id in model.selected_mold_ids:
        pred_data = mold_pred_map[mold_id]
        # 关联菌种对应的批次数量，根据实际逻辑调整
        total += pred_data[5] * len([lot for lot in lots if lot['mold_id'] == mold_id])
    return total

model.obj = Objective(expr=obj_rule, sense=minimize)

# ---- Constraints --- #
# 平板培养时长范围约束
def plate_days_range(model, mold_id):
    pred_data = mold_pred_map[mold_id]
    min_plate = pred_data[1]
    max_plate = pred_data[1]  # 若为范围则调整为对应数据库字段
    return (min_plate <= model.plate_days[mold_id], model.plate_days[mold_id] <= max_plate)

model.plate_days_range = Constraint(model.selected_mold_ids, rule=plate_days_range)

# 种子培养时长范围约束
def seed_days_range(model, mold_id):
    pred_data = mold_pred_map[mold_id]
    min_seed = pred_data[2]
    max_seed = pred_data[2]  # 若为范围则调整为对应数据库字段
    return (min_seed <= model.seed_days[mold_id], model.seed_days[mold_id] <= max_seed)

model.seed_days_range = Constraint(model.selected_mold_ids, rule=seed_days_range)

# 平板结束=种子开始
def plate_to_seed_constraint(model, mold_id):
    return model.seed_start_dates[mold_id] == model.plate_start_dates[mold_id] + model.plate_days[mold_id]

model.plate_to_seed = Constraint(model.selected_mold_ids, rule=plate_to_seed_constraint)

# 种子结束=培养开始
def seed_to_inc_constraint(model, mold_id):
    return model.inc_start_dates[mold_id] == model.seed_start_dates[mold_id] + model.seed_days[mold_id]

model.seed_to_inc = Constraint(model.selected_mold_ids, rule=seed_to_inc_constraint)

# 培养结束=收获开始
def inc_to_harvest_constraint(model, mold_id):
    return model.harvest_start_dates[mold_id] == model.inc_start_dates[mold_id] + model.incubation_days[mold_id]

model.inc_to_harvest = Constraint(model.selected_mold_ids, rule=inc_to_harvest_constraint)

# 平板起始日期为周末约束（大M法线性化）
def plate_weekend_constraint(model, mold_id):
    M = 1000  # 取足够大的日期差值
    date_var = model.plate_start_dates[mold_id]
    return (weekend_status[date_var] - model.is_plate_weekend[mold_id]) <= 0, \
           (model.is_plate_weekend[mold_id] - weekend_status[date_var]) <= 0, \
           model.is_plate_weekend[mold_id] == 1

model.plate_weekend = Constraint(model.selected_mold_ids, rule=plate_weekend_constraint)

# 种子起始日期为周末约束
def seed_weekend_constraint(model, mold_id):
    M = 1000
    date_var = model.seed_start_dates[mold_id]
    return (weekend_status[date_var] - model.is_seed_weekend[mold_id]) <= 0, \
           (model.is_seed_weekend[mold_id] - weekend_status[date_var]) <= 0, \
           model.is_seed_weekend[mold_id] == 1

model.seed_weekend = Constraint(model.selected_mold_ids, rule=seed_weekend_constraint)

# 收获日期为周末约束
def harvest_weekend_constraint(model, mold_id):
    M = 1000
    date_var = model.harvest_start_dates[mold_id]
    return (weekend_status[date_var] - model.is_harvest_weekend[mold_id]) <= 0, \
           (model.is_harvest_weekend[mold_id] - weekend_status[date_var]) <= 0, \
           model.is_harvest_weekend[mold_id] == 1

model.harvest_weekend = Constraint(model.selected_mold_ids, rule=harvest_weekend_constraint)

# 同菌种批次平板起始日期相同约束
def same_plate_start_constraint(model, mold_id1, mold_id2):
    if mold_id1 != mold_id2 and mold_id1.split('_')[0] == mold_id2.split('_')[0]:
        return model.plate_start_dates[mold_id1] == model.plate_start_dates[mold_id2]
    return Constraint.Skip

model.same_plate_start = Constraint(model.selected_mold_ids, model.selected_mold_ids, rule=same_plate_start_constraint)

# 同菌种批次种子起始日期相同约束
def same_seed_start_constraint(model, mold_id1, mold_id2):
    if mold_id1 != mold_id2 and mold_id1.split('_')[0] == mold_id2.split('_')[0]:
        return model.seed_start_dates[mold_id1] == model.seed_start_dates[mold_id2]
    return Constraint.Skip

model.same_seed_start = Constraint(model.selected_mold_ids, model.selected_mold_ids, rule=same_seed_start_constraint)

# ---- Solve Model --- #
print('Pre solution check')

# 使用Gurobi求解（需配置许可证）
solver = SolverFactory('gurobi')
results = solver.solve(model, tee=True)

# 检查求解结果
if (results.solver.status == SolverStatus.ok) and (results.solver.termination_condition == TerminationCondition.optimal):
    print("Optimal solution found!")
else:
    print(f"Solver status: {results.solver.status}, Termination condition: {results.solver.termination_condition}")

# 输出结果
print(f"Objective value: {model.obj()}")
for var in model.component_objects(Var, active=True):
    var_obj = getattr(model, str(var))
    for idx in var_obj:
        print(f"{var.name}[{idx}] = {var_obj[idx].value}")

关键修正说明 #

1. 周末约束线性化：通过预计算日期状态+二进制变量+大M法，将布尔判断转化为线性约束，适配线性规划求解器
2. 移除无效操作：删除求解前的value()调用，用等式约束替代fix()实现批次日期同步
3. 数据结构优化：构建mold_pred_map字典，避免循环遍历数据库查询结果，提升建模效率

内容的提问来源于stack exchange，提问作者Encker