Mapping a Database Table to an Object Attribute
I found a place in my current project where I created several classes to store a complex data structure in memory and a complete SQL schema to store the same data in a database. I decided to use SQLAlchemy as the ORM layer as it seems to be the most flexible solution I can adapt to my needs.
My problem is that now I need to map the whole table to an in-memory array attribute and I am trying to figure out if this is possible, and if so, how to do it using SQLAlchemy. I can still change the data structures in the code (albeit less than ideal) if not, but I would rather not.
The corresponding table is created using the following SQL:
CREATE TABLE `works` (
`id` BIGINT NOT NULL auto_increment,
`uniform_title` VARCHAR(255) NOT NULL,
`created_date` DATE NOT NULL,
`context` TEXT,
`distinguishing_characteristics` TEXT,
PRIMARY KEY (`id`),
INDEX (`uniform_title` ASC),
INDEX (`uniform_title` DESC)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 ;
CREATE TABLE `variant_work_titles` (
`id` BIGINT NOT NULL auto_increment,
`work_id` BIGINT NOT NULL,
`title` VARCHAR(255) NOT NULL,
PRIMARY KEY(`id`),
INDEX (`work_id`),
INDEX (`title` ASC),
INDEX (`title` DESC),
FOREIGN KEY (`work_id`)
REFERENCES `works` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
This is just a small part of the complete database and will need the same in several places.
a source to share
You seem to want something like
work_instance.variants = [<some iterable of variants>]
If not, please clarify in your question.
Ideally, you should have 2 mappings against these two tables. It doesn't matter if you don't access the second mapping anywhere. The mapping work
must have a one-to-many relationship with the mapping variant
. This will give you a list of instances variant
associated with a particular one work
, depending on which attribute you are defining your relationship to.
a source to share