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:
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()
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
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.