SQLService models missing fields that are visible to a plain SQLAlchemy query

[mathrick]

Hi, apologies for a lot of the details missing in this report; I've run into this issue while working on a unit test layer for a proprietary project at work, and I don't know how to reduce it to a small reproducible example, nor can I easily share a setup for you to access where the bug occurs. I'll be happy to provide any additional information you might need, if you can guide me in getting it for you.

I've run into a weird issue where query results are missing some, but not all fields when using reflected models based on SQLService declarative base, even though the database very much has them, and they're returned correctly when using a plain SQLAlchemy Table for the same query. This is the basic reflection setup we're using:

from sqlalchemy import MetaData, make_url, text
from sqlalchemy.ext.automap import automap_base
from sqlservice import Database, ModelBase, declarative_base


@contextmanager
def get_reflected_db(args):
    url = get_db_url(args)

    # Set up SQLService's enhanced declarative base together with Automap
    # for reflection of existing tables
    # See https://sqlservice.readthedocs.io/en/latest/model.html
    # and https://docs.sqlalchemy.org/en/20/orm/extensions/automap.html
    logger.info("Reflecting database {}", url)
    metadata = MetaData()
    Model = automap_base(declarative_base(ModelBase, metadata=metadata))
    db = Database(url, model_class=Model)
    try:
        db.reflect()
        # Invoke Automap
        Model.prepare(autoload_with=db.engine)
        yield db
    finally:
        db.close()

This is for a DB unit testing framework (not sure if that matters, but including it here for completeness), where in one of the tests, I'm exercising a stored procedure for adding users, and it fails the verification step to confirm that the results when querying it back match what was passed in. That's where the weirdness begins:

(Pdb++) usermodel
>>> <class 'sqlservice.model.users'>
(Pdb++) input_row
{'userid': None, 'secid': 1829, 'firstname': 'Dawn', 'lastname': 'Lopez', 'emailaddress': 'joshua58@xyz.gov', 'positionid': None, 'accreditationnum': 526, ...}

(Pdb++) q = select(usermodel).where(usermodel.secid == input_row["secid"])
(Pdb++) print(q)
SELECT users.userid, users.secid, users.firstname, users.lastname, users.emailaddress, users.positionid, users.accreditationnum, ...
FROM users 
WHERE users.secid = :secid_1
(Pdb++) db.execute(q).scalar_one()
users(userid=1127, secid=1829, firstname='Dawn', lastname='Lopez', emailaddress='joshua58@xyz.gov', positionid=None, accreditationnum=None, ...)

(Pdb++) q2 = select(usermodel.accreditationnum).where(usermodel.secid == input_row["secid"])
(Pdb++) print(q2)
SELECT users.accreditationnum 
FROM users 
WHERE users.secid = :secid_1
(Pdb++) db.execute(q2).scalar_one()
526

(Pdb++) q3 = select(usermodel.__table__).where(usermodel.secid == input_row["secid"])
(Pdb++) str(q) == str(q3)
True
(Pdb++) db(q3).one()._mapping
{'userid': 1127, 'secid': 1829, 'firstname': 'Dawn', 'lastname': 'Lopez', 'emailaddress': 'joshua58@xyz.gov', 'positionid': None, 'accreditationnum': 526, ...}

As you can see, the value for accreditationnum is missing when querying through the model, even though it's definitely visible if I ask for it explicitly, or drop down to the raw Table object. The query SQL generated is identical, and manually querying the DB confirms that the results are there.

Environment used:

$ python --version
Python 3.10.12

$ pip freeze
attrs==23.1.0
certifi==2023.7.22
charset-normalizer==3.2.0
click==7.1.2
exceptiongroup==1.1.2
Faker==19.2.0
fancycompleter==0.9.1
Flask==1.1.4
greenlet==2.0.2
idna==3.4
iniconfig==2.0.0
itsdangerous==1.1.0
Jinja2==2.11.3
loguru==0.7.0
MarkupSafe==2.1.3
packaging==23.1
pdbpp==0.10.3
pkginfo==1.9.6
pluggy==1.2.0
psycopg==3.1.9
Pygments==2.15.1
PyJWT==2.8.0
pyrepl==0.9.0
pytest==7.4.0
pytest-azurepipelines==1.0.4
pytest-loguru==0.2.0
pytest-nunit==1.0.3
python-dateutil==2.8.2
-e git+ssh://git@github.com/private/private.git@22fc3b6527726f8b55edce4fab5ee6a72ea318a4#egg=private&subdirectory=test
requests==2.31.0
six==1.16.0
SQLAlchemy==2.0.19
sqlservice==2.0.1
tomli==2.0.1
typing_extensions==4.7.1
urllib3==2.0.4
Werkzeug==1.0.1
wmctrl==0.4