How to get list of objects from multi-value field with SqlAlchemy using ORM?

I have MS Access DB file (.accdb) from my client and need to describe tables and columns with declarative_base class. As I can see in table constructor – one of column has Integer value and has relationship “one-to-many” with another column in some another table (foreign key). But actually in this foreign key stored not single Integer value, but string with number values separated with semicolons. This technique called as “multi-value fields”. In fact this is “many-to-many” relationship without associative tables.

Very simplified scheme:

Persons
-------------
id - Integer
name - String
vacancy_id - Integer (multi-value, Foreign key of Vacancies.id)

Vacancies
-------------
id - Integer
vacancy_name - String

I tried to map classes to tables using declarative_base parent class. But can’t find how to declare “many-to-many” relationship without associative table. Now I have such code.

Base = declarative_base()


class Vacancy(Base):
    __tablename__ = 'Vacancies'
    id = sa.Column(sa.Integer, name='id', primary_key=True, autoincrement=True)
    vacancy_name = sa.Column(sa.String(255), name='vacancy_name')


class Person(Base):
    __tablename__ = 'Persons'
    id = sa.Column(sa.Integer, name='id', primary_key=True, autoincrement=True)
    name = sa.Column(sa.String(255), name='name')
    vacancy_id = sa.Column(sa.Integer, ForeignKey(Vacancy.id), name='vacancy_id')
    vacancies = relationship(Vacancy)

During request Person I have strange behavior:

• If vacancy_id not specified, I get Person.vacancies as None.
• If vacancy_id specified as single value (i.e. “1”), in Person.vacancies I get single object of Vacancy class.
• If vacancy_id specified as multiple value (i.e. “1;2;3”), in Person.vacancies I also get None.

Of course I can request raw Person.vacancy_id, split it with semicolon, and make request to get Vacancies with list of ID’s.

But I wonder – if SqlAlchemy can process “multi-value fields”? And what the best way to work with such fileds?

UPDATE At present I made following workaround to automatically parse multi-value columns. This should be added to Persons class:

@orm.reconstructor
def load_on_init(self):
    if self.vacancy_id:
        ids = self.vacancy_id.split(';')
        self.vacancies = [x for x in Vacancy.query.filter(Vacancy.id.in_(ids)).all()]
    else:
        self.vacancies = []

Vacancies class should have fllowing attribute:

query = DBSession.query_property()

Finally we have to prepare session for in-class usage:

engine = create_engine(CONNECTION_URI)
DBSession = scoped_session(sessionmaker(bind=engine))
Base = declarative_base()

Answer

Access ODBC provides very limited support for multi-value lookup fields. Such fields are actually implemented using a hidden association table (with a name like f_1BC9E55B5578456EB5ACABC99BB2FF0B_vacancies) but those tables are not accessible from SQL statements:

SELECT * from f_1BC9E55B5578456EB5ACABC99BB2FF0B_vacancies

results in the error

The Microsoft Access database engine cannot find the input table or query ”. Make sure it exists and that its name is spelled correctly.

As you have discovered, Access ODBC will read the key values of the multiple entries and present them as a semicolon-separated list that we can parse, but we cannot update those values

UPDATE Persons SET vacancies = '1;2' WHERE id = 1

fails with

An UPDATE or DELETE query cannot contain a multi-valued field. (-3209)

So, TL;DR, if you only need to read from the database then your workaround may be sufficient, but if you need to modify those multi-valued fields then Access ODBC is not going to get the job done for you.