从SQLalchemy关系中检索列 [英] Retrieving column from a SQLalchemy relationship
问题描述
$ $ p $
class User(Base):
__tablename__ ='user'
id = Column(Integer,primary_key = True)
name = Column(String(250),nullable = False)
class (Base):
__tablename__ ='category'
id = Column(Integer,primary_key = True)
name = Column(String(250),nullable = False)
class Thing(Base):
__tablename__ ='thing'
id = Column(Integer,primary_key = True)
description = Column(500,nullable = False)
user_id = Column(Integer,ForeignKey('user.id'),nullable = True)
user = relationship(User,foreign_keys = [user_id])
category_id = Column(Integer,ForeignKey(' (类别,foreign_keys = [category_id])
class RelationBox(wx.ComboBox):
def __init __(self,parent,column):
wx.ComboBox .__ init __(self,parent,style = wx.CB_READONLY)
self.nullable = True#column.nullable
self.linked_table = column.mapper.class_
如果self.nullable:
self.SetItems([])
self.options = session.query(self.linked_table)
session.mit()
用于self.options中的选项:
self.Append(option .__ repr __(),option)
我简化了代码,只提取了一些代码,希望能给你一个更清晰的画面。我已经实现了它:
categories = [user,category]
category_sizer = wx .BoxSizer(wx.HORIZONTAL)
self.column_widgets = {}
类别中的类别:
box = wx.BoxSizer(wx.VERTICAL)
box。添加(wx.StaticText(self,-1,category.capitalize()),0,wx.ALIGN_CENTRE | wx.ALL,5)
self.column_widgets [category] = RelationBox(self,getattr(Thing,category ))
box.Add(self.column_widgets [category],1,wx.ALIGN_CENTRE | wx.ALIGN_TOP | wx.ALL,2)
category_sizer.Add(box,0,wx.ALIGN_CENTRE | wx .ALL,5)
我想获取链接到关系的列,以便设置您可以通过检查<$ c>来获得与关系相关联的列。
$ c> .prop 属性:>>> Thing.category.prop.local_columns
set([Column('category_id',Integer(),ForeignKey('category.id'),table =< thing>)])
>> > Thing.category.prop.remote_side
set([Column('id',Integer(),table =< category> ;, primary_key = True,nullable = False)]))
因为有一个外键有两面,所以你需要小心哪一个(local_columns
remote_side )。
要从实例中获取值,请执行以下操作:
col,= Thing.category.prop.local_columns
key = Thing .__ mapper __。get_property_by_column(col).key
getattr(thing,key)
I'm working on some wxpython widgets that integrate SQLalchemy, CRUD stuff. I've got a wx.ComboBox that lists the rows of a table linked by a relationship.
class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key=True) name = Column(String(250), nullable=False) class Category(Base): __tablename__ = 'category' id = Column(Integer, primary_key=True) name = Column(String(250), nullable=False) class Thing(Base): __tablename__ = 'thing' id = Column(Integer, primary_key=True) description = Column(String(500), nullable=False) user_id = Column(Integer, ForeignKey('user.id'), nullable=True) user = relationship(User, foreign_keys = [user_id]) category_id = Column(Integer, ForeignKey('category.id'), nullable=True) category = relationship(Category, foreign_keys = [category_id]) class RelationBox(wx.ComboBox): def __init__(self, parent, column): wx.ComboBox.__init__(self, parent, style = wx.CB_READONLY) self.nullable = True # column.nullable self.linked_table = column.mapper.class_ if self.nullable: self.SetItems([""]) self.options = session.query(self.linked_table) session.commit() for option in self.options: self.Append(option.__repr__(), option)
I've simplified the code and only extracted some of it to hopefully give you a clearer picture. I've implemented it like this:
categories = ["user", "category"] category_sizer = wx.BoxSizer(wx.HORIZONTAL) self.column_widgets = {} for category in categories: box = wx.BoxSizer(wx.VERTICAL) box.Add(wx.StaticText(self, -1, category.capitalize()), 0, wx.ALIGN_CENTRE|wx.ALL, 5) self.column_widgets[category] = RelationBox(self, getattr(Thing, category)) box.Add(self.column_widgets[category], 1, wx.ALIGN_CENTRE|wx.ALIGN_TOP|wx.ALL, 2) category_sizer.Add(box, 0, wx.ALIGN_CENTRE|wx.ALL, 5)
I want to get the Column that is linked to the relationship so I can set whether the widget has a blank option or not.
解决方案You can get the column associated with a relationship by inspecting the
.prop
attribute:>>> Thing.category.prop.local_columns set([Column('category_id', Integer(), ForeignKey('category.id'), table=<thing>)]) >>> Thing.category.prop.remote_side set([Column('id', Integer(), table=<category>, primary_key=True, nullable=False)]))
Because there are two sides to a foreign key, you need to be careful which one (
local_columns
orremote_side
) you choose.To then fetch the value from an instance, do the following:
col, = Thing.category.prop.local_columns key = Thing.__mapper__.get_property_by_column(col).key getattr(thing, key)
这篇关于从SQLalchemy关系中检索列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!