将数据库表映射到对象的属性

I've come across a place in my current project where I have created several classes for storing a complicated data structure in memory and a completed SQL schema for storing the same data in a database. I've decided to use SQLAlchemy as an ORM layer as it seems the most flexible solution that I can tailor to my needs.

我在当前项目中遇到过一个地方,我创建了几个用于在内存中存储复杂数据结构的类,以及一个用于在数据库中存储相同数据的完整SQL模式。我决定使用SQLAlchemy作为ORM层,因为它似乎是我可以根据我的需求量身定制的最灵活的解决方案。

My problem is that I now need to map an entire table to just an array attribute in memory and am struggling to find out if this is possible and, if it is, how to do it with SQLAlchemy. It is still possible for me to change the data structures in code (although less than ideal) if not, but I would prefer not to.

我的问题是我现在需要将整个表映射到内存中的一个数组属性,并且很难找出这是否可行,如果是,那么如何使用SQLAlchemy来完成它。我仍然可以改变代码中的数据结构(虽然不是理想的),但我不愿意。

The table in question is created using the following SQL:

使用以下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 the same thing is going to be required in several places.

这只是整个数据库的一小部分,在几个地方也需要同样的东西。

1 个解决方案

#1

It seems you want something like

看起来你想要的东西

work_instance.variants = [<some iterable of variants>]

If not please clarify in your question.

如果没有,请在您的问题中澄清。

Ideally you should have 2 mappings to these 2 tables. It doesn't matter if you won't access the second mapping anywhere else. work mapping should have a one-to-many relationship to variant mapping. This would give you a list of variant instances associated with a particular work on whichever attribute you define your relationship.

理想情况下,这两个表应该有2个映射。如果您不在其他地方访问第二个映射并不重要。工作映射应该与变量映射具有一对多的关系。这将为您提供与您定义关系的任何属性的特定工作相关联的变体实例的列表。

#1