Persistent DataPortal Connection Data Corruption

[jeff-ws]

Summary

I'm working on a large pyomo model with dozens of sets & parameters loaded from a sqlite database. I'm using a DataPortal as described in the dox to load an abstract model. This construct has many data_portal.load() statements. I said to myself, "Golly, I wonder if under the hood, the data portal is making/breaking connection every time, and if so is there a persistent connection available?" I dug around and found the .connect() and .disconnect() options and attempted to use them to persist the connection and speed loading.

I noticed that when transitioning from loading the sets to the params, there appears to be a corruption issue. (I'm pulling the sets from separate tables than the params.) The "last set" being loaded is corrupted within the namespace in the DataPortal to include junk from the parameter loading and none of the parameter data is present in the namespace.

Perhaps I'm missing a keyword or using this incorrectly, but it seems like all of the 3 examples below should produce the same namespace, and the problem example (dp_2) which is persistent, but does not disconnect/reconnect between sets and param loading is a problem. Loading all params or all sets works fine, there is something in the transition that isn't working.

Unsure if there is some kind of persistent state in the connection that is removed by the disconnect/reconnect or if there is something about loading params & sets separately, or if I'm just gooning this away.

Steps to reproduce the issue

"""
attempt to break data portal...
"""

import pyomo.environ as pyo

m = pyo.AbstractModel()

m.t = pyo.Set()
m.s = pyo.Set()
m.cost = pyo.Param(m.t)

### non-persistent connection ###
dp_1 = pyo.DataPortal()

dp_1.load(using='sqlite3', filename='data.sqlite', set=m.t, query='SELECT t FROM Idx')
dp_1.load(using='sqlite3', filename='data.sqlite', set=m.s, query='SELECT s FROM Idx')
dp_1.load(using='sqlite3', filename='data.sqlite', param=m.cost, query='SELECT t, cost FROM Params')

# see what we hooked
print('non-persistent:')
for item in dp_1.items():
    print(item)

### persistent connection ###

dp_2 = pyo.DataPortal()
# try a persistent connection...
dp_2.connect(using='sqlite3', filename='data.sqlite')
dp_2.load(set=m.t, query='SELECT t FROM Idx')
dp_2.load(set=m.s, query='SELECT s FROM Idx')
dp_2.load(param=m.cost, query='SELECT t, cost FROM Params')

dp_2.disconnect()

print('\npersistent (BAD/CORRUPTED "LAST" SET & NO PARAMS):')
# see what we hooked
for item in dp_2.items():
    print(item)

### persistent w/interrupt between sets & params ###

dp_3 = pyo.DataPortal()

dp_3.connect(using='sqlite3', filename='data.sqlite')
dp_3.load(set=m.t, query='SELECT t FROM Idx')
dp_3.load(set=m.s, query='SELECT s FROM Idx')
# Disconnect/reconnect fixes issue...
dp_3.disconnect()
dp_3.connect(using='sqlite3', filename='data.sqlite')
dp_3.load(param=m.cost, query='SELECT t, cost FROM Params')

dp_3.disconnect()

print('\npersistent with disconnects between set/param:')
# see what we hooked
for item in dp_3.items():
    print(item)

Output

non-persistent:
('t', [1, 2, 3])
('s', [7, 8, 9])
('cost', {1: 4.4, 2: 5.5, 3: 6.6})

persistent (BAD/CORRUPTED "LAST" SET & NO PARAMS):
('t', [1, 2, 3])
('s', [(1, 4.4), (2, 5.5), (3, 6.6)])

persistent with disconnects between set/param:
('t', [1, 2, 3])
('s', [7, 8, 9])
('cost', {1: 4.4, 2: 5.5, 3: 6.6})

Process finished with exit code 0

Information on your system

Pyomo version: 6.7
Python version: 3.11 / 3.12
Operating system: MacOS
How Pyomo was installed (PyPI, conda, source): PyPI
Solver (if applicable): N/A

data.sqlite