SQLAlchemy - 标签字典 [英] SQLAlchemy - Dictionary of tags

问题描述

我对 SQLAlchemy 有疑问.如何将类似字典的属性添加到我的映射类中，该属性将字符串键映射为字符串值，并将存储在数据库中(与原始映射对象在同一表或另一个表中).我希望这增加对我的对象的任意标签的支持.

我在 SQLAlchemy 文档中找到了以下示例:

from sqlalchemy.orm.collections import column_mapped_collection、attribute_mapped_collection、mapped_collection映射器(项目，项目表，属性={# 按列键'notes':relation(Note, collection_class=column_mapped_collection(notes_table.c.keyword)),# 或命名属性'notes2':relation(Note, collection_class=attribute_mapped_collection('keyword')),# 或任何可调用的'notes3':relation(Note, collection_class=mapped_collection(lambda entity: entity.a + entity.b))})项目 = 项目()item.notes['color'] = Note('color', 'blue')

但我想要以下行为:

mapper(Item, items_table, properties={# 按列键'注释':关系(...)，})项目 = 项目()item.notes['color'] = '蓝色'

在 SQLAlchemy 中可以吗?

谢谢

解决方案

简单的答案是是.

只需使用关联代理:

from sqlalchemy import Column, Integer, String, Table, create_engine从 sqlalchemy 导入 o​​rm、元数据、列、外键从 sqlalchemy.orm 导入关系、映射器、会话生成器从 sqlalchemy.orm.collections 导入 column_mapped_collection从 sqlalchemy.ext.associationproxy 导入 association_proxy

创建测试环境:

engine = create_engine('sqlite:///:memory:', echo=True)元 = 元数据(绑定 = 引擎)

定义表格:

tb_items = Table('items', meta,列('id'，整数，primary_key=True)，列('名称'，字符串(20))，列('说明'，字符串(100))，)tb_notes = Table('notes', meta,Column('id_item', Integer, ForeignKey('items.id'), primary_key=True),列('名称'，字符串(20)，primary_key=True)，列('值'，字符串(100))，)meta.create_all()

类(注意类中的association_proxy):

class Note(object):def __init__(self, name, value):self.name = 姓名self.value = 价值类项目(对象):def __init__(self, name, description=''):self.name = 姓名self.description = 描述notes = association_proxy('_notesdict', 'value', creator=Note)

映射:

mapper(Note, tb_notes)映射器(项目，tb_items，属性={'_notesdict':关系(注意，collection_class=column_mapped_collection(tb_notes.c.name))，})

然后测试一下:

Session = sessionmaker(bind=engine)s = 会话()i = Item('ball', '一个圆球')i.notes['color'] = '橙色'i.notes['size'] = 'big'i.notes['data'] = 'none's.add(i)s.commit()打印 i.notes

打印:

{u'color': u'orange', u'data': u'none', u'size': u'big'}

但是，那些在笔记表中吗?

<预><代码>>>>打印列表(tb_notes.select().execute())[(1, u'color', u'orange'), (1, u'data', u'none'), (1, u'size', u'big')]

它有效！！:)

I have question regarding the SQLAlchemy. How can I add into my mapped class the dictionary-like attribute, which maps the string keys into string values and which will be stored in the database (in the same or another table as original mapped object). I want this add support for arbitrary tags of my objects.

I found the following example in SQLAlchemy documentation:

from sqlalchemy.orm.collections import column_mapped_collection, attribute_mapped_collection, mapped_collection

mapper(Item, items_table, properties={
# key by column
'notes': relation(Note, collection_class=column_mapped_collection(notes_table.c.keyword)),
# or named attribute
'notes2': relation(Note, collection_class=attribute_mapped_collection('keyword')),
# or any callable
'notes3': relation(Note, collection_class=mapped_collection(lambda entity: entity.a + entity.b))
})

item = Item()
item.notes['color'] = Note('color', 'blue')

But I want the following behavior:

mapper(Item, items_table, properties={
# key by column
'notes': relation(...),
})

item = Item()
item.notes['color'] = 'blue'

It is possible in SQLAlchemy?

Thank you

解决方案

The simple answer is yes.

Just use an association proxy:

from sqlalchemy import Column, Integer, String, Table, create_engine
from sqlalchemy import orm, MetaData, Column, ForeignKey
from sqlalchemy.orm import relation, mapper, sessionmaker
from sqlalchemy.orm.collections import column_mapped_collection
from sqlalchemy.ext.associationproxy import association_proxy

Create a test environment:

engine = create_engine('sqlite:///:memory:', echo=True)
meta = MetaData(bind=engine)

Define the tables:

tb_items = Table('items', meta, 
        Column('id', Integer, primary_key=True), 
        Column('name', String(20)),
        Column('description', String(100)),
    )
tb_notes = Table('notes', meta, 
        Column('id_item', Integer, ForeignKey('items.id'), primary_key=True),
        Column('name', String(20), primary_key=True),
        Column('value', String(100)),
    )
meta.create_all()

Classes (note the association_proxy in the class):

class Note(object):
    def __init__(self, name, value):
        self.name = name
        self.value = value
class Item(object):
    def __init__(self, name, description=''):
        self.name = name
        self.description = description
    notes = association_proxy('_notesdict', 'value', creator=Note)

Mapping:

mapper(Note, tb_notes)
mapper(Item, tb_items, properties={
        '_notesdict': relation(Note, 
             collection_class=column_mapped_collection(tb_notes.c.name)),
    })

Then just test it:

Session = sessionmaker(bind=engine)
s = Session()

i = Item('ball', 'A round full ball')
i.notes['color'] = 'orange'
i.notes['size'] = 'big'
i.notes['data'] = 'none'

s.add(i)
s.commit()
print i.notes

That prints:

{u'color': u'orange', u'data': u'none', u'size': u'big'}

But, are those in the notes table?

>>> print list(tb_notes.select().execute())
[(1, u'color', u'orange'), (1, u'data', u'none'), (1, u'size', u'big')]

It works!! :)

这篇关于SQLAlchemy - 标签字典的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！