python sqlalchemy 动态映射

sqlalchemy dynamic mapping

提问by Alex D

I have the following problem:

我有以下问题：

I have the class:

我有课：

class Word(object):

    def __init__(self):
        self.id = None
        self.columns = {}

    def __str__(self):
        return "(%s, %s)" % (str(self.id), str(self.columns))

self.columns is a dict which will hold (columnName:columnValue) values. The name of the columns are known at runtime and they are loaded in a wordColumns list, for example

self.columns 是一个包含 (columnName:columnValue) 值的字典。列的名称在运行时是已知的，并且它们被加载到一个 wordColumns 列表中，例如

wordColumns = ['english', 'korean', 'romanian']

wordTable = Table('word', metadata,
                  Column('id', Integer, primary_key = True)
                  )
for columnName in wordColumns:
    wordTable.append_column(Column(columnName, String(255), nullable = False))

I even created a explicit mapper properties to "force" the table columns to be mapped on word.columns[columnName], instead of word.columnName, I don't get any error on mapping, but it seems that doesn't work.

我什至创建了一个显式映射器属性来“强制”将表列映射到 word.columns[columnName]，而不是 word.columnName，我在映射时没有收到任何错误，但似乎这不起作用。

mapperProperties = {}
for column in wordColumns:
    mapperProperties['columns[\'%']' % column] = wordTable.columns[column]

mapper(Word, wordTable, mapperProperties)

When I load a word object, SQLAlchemy creates an object which has the word.columns['english'], word.columns['korean'] etc. properties instead of loading them into word.columns dict. So for each column, it creates a new property. Moreover word.columns dictionary doesn't even exists.

当我加载一个 word 对象时，SQLAlchemy 创建一个具有 word.columns['english']、word.columns['korean'] 等属性的对象，而不是将它们加载到 word.columns 字典中。因此，对于每一列，它都会创建一个新属性。此外 word.columns 字典甚至不存在。

The same way, when I try to persist a word, SQLAlchemy expects to find the column values in properties named like word.columns['english'] (string type) instead of the dictionary word.columns.

同样，当我尝试保留一个单词时，SQLAlchemy 期望在名为 word.columns['english']（字符串类型）而不是字典 word.columns 的属性中找到列值。

I have to say that my experience with Python and SQLAlchemy is quite limited, maybe it isn't possible to do what I'm trying to do.

我不得不说，我在 Python 和 SQLAlchemy 方面的经验非常有限，也许无法做我想做的事情。

Any help appreciated,

任何帮助表示赞赏，

Thanks in advance.

提前致谢。

回答by nosklo

It seems that you can just use the attributes directly instead of using the columnsdict.

似乎您可以直接使用属性而不是使用columnsdict.

Consider the following setup:

考虑以下设置：

from sqlalchemy import Table, Column, Integer, Unicode, MetaData, create_engine
from sqlalchemy.orm import mapper, create_session

class Word(object):
    pass

wordColumns = ['english', 'korean', 'romanian']
e = create_engine('sqlite://')
metadata = MetaData(bind=e)

t = Table('words', metadata, Column('id', Integer, primary_key=True),
    *(Column(wordCol, Unicode(255)) for wordCol in wordColumns))
metadata.create_all()
mapper(Word, t)
session = create_session(bind=e, autocommit=False, autoflush=True)

With that empty class you can do:

使用该空类，您可以执行以下操作：

w = Word()
w.english = u'name'
w.korean = u'??'
w.romanian = u'nume'

session.add(w)
session.commit()

And when you want to access the data:

当您想访问数据时：

w = session.query(Word).filter_by(english=u'name').one()
print w.romanian

That's the whole sqlalchemy's ORM point, instead of using a tupleor dictto access the data, you use attribute-likeaccess on your own class.

这就是整体sqlalchemy的 ORM 点，不是使用tupleordict访问数据，而是在自己的类上使用类似属性的访问。

So I was wondering for reasons you'd like to use a dict. Perhaps it's because you have strings with the language names. Well, for that you could use python's getattrand setattrinstead, as you would on any python object:

所以我想知道您想使用dict. 也许是因为您有带有语言名称的字符串。嗯，你可以使用Pythongetattr和setattr代替，因为你将在任何Python对象：

language = 'korean'
print getattr(w, language)

That should solve all of your issues.

那应该可以解决您的所有问题。

---

---

That said, if you still want to use dict-like access to the columns, it is also possible. You just have to implement a dict-like object. I will now provide code to do this, even though I think it's absolutely unnecessary clutter, since attribute access is so clean. If your issue is already solved by using the method above, don't use the code below this point.

也就是说，如果您仍然想使用dict对列的类似访问，那也是可能的。你只需要实现一个dict-like 对象。我现在将提供执行此操作的代码，尽管我认为这绝对是不必要的混乱，因为属性访问非常干净。如果您的问题已通过使用上述方法解决，请不要使用以下代码。

You could do it on the Wordclass:

你可以在Word课堂上做到：

class Word(object):
    def __getitem__(self, item): 
        return getattr(self, item)
    def __setitem__(self, item, value):
        return setattr(self, item, value)

The rest of the setup works as above. Then you could use it like this:

其余的设置工作如上。然后你可以像这样使用它：

w = Word()
w['english'] = u'name'

If you want a columnsattribute then you need a dict-like

如果你想要一个columns属性，那么你需要一个dict-like

class AttributeDict(DictMixin):
    def __init__(self, obj):
        self._obj = obj
    def __getitem__(self, item):
        return getattr(self._obj, item)
    def __setitem__(self, item, value):
        return setattr(self._obj, item, value)

class Word(object):
    def __init__(self):
        self.columns = AttributeDict(self)

Then you could use as you intended:

然后你可以按你的意图使用：

w = Word()
w.columns['english'] = u'name' 

I think you'll agree that all this is unnecessarly complicated with no added benefit.

我想你会同意所有这些都不必要地复杂，没有额外的好处。

回答by trash80

I used nosklo's solution (thanks!) but I already had a primary key (passed in as pk_col) within the column line (first line of csv). So I thought I'd share my modification. I used a ternary.

我使用了 nosklo 的解决方案（谢谢！）但我已经在列行（csv 的第一行）中有一个主键（作为 pk_col 传入）。所以我想我会分享我的修改。我用了三元。

table = Table(tablename, metadata,
    *((Column(pk_col, Integer, primary_key=True)) if rowname == pk_col else (Column(rowname, String())) for rowname in row.keys()))
table.create()