Sqlalchemy 为父母过滤父子表

Question

我认为@jrjc 的这个功能解决方案对我的理解来说相当令人困惑，因此，我更愿意稍微改变我自己的东西并进行调整。所以，我花了一些时间才明白，当为情节创建第二个图例时，python 会自动删除第一个图例，这是add_artist()必须使用的时候。添加第二个图例的另一个先决条件是命名图并将 .add_artist() 方法应用于该特定图，以便 python 知道将新部分粘贴在哪里。简而言之，这就是我设法创建我想到的情节的方式，我希望这些评论能让它更清晰，对任何人都有用。import matplotlib.pyplot as pltfrom matplotlib.colors import LinearSegmentedColormap as coloringimport matplotlib.patches as mpatches# copy the dfs below and use pd.read_clipboard() to reproducedf_1     A   B   C   D   EMg  10  15  23  25  27Ca  30  33   0  20  17df_2     A   B   C   D   EMg  20  12   8  40  10Ca   7  26  12  22  16hatches=(' ', '//')colors_ABCDE=['tomato', 'gold', 'greenyellow', 'forestgreen', 'palevioletred']dfs=[df_1,df_2]for each_df, df in enumerate(dfs):    #I name the plot as "figure"    figure=df.plot(ax=plt.subplot(111), kind="barh", \            stacked=True, hatch=hatches[each_df], \            colormap=coloring.from_list("my_colormap", colors_ABCDE), \            figsize=(7,2.5), position=len(dfs)-each_df-1, \            align='center', width=0.2, edgecolor="darkgrey", \            legend=False) #I had to False the legend toolegend_1=plt.legend(df_1.columns, loc='center left', bbox_to_anchor=(1.0, 0.5), fontsize=12)patch_hatched = mpatches.Patch(facecolor='beige', hatch='///', edgecolor="darkgrey", label='hatched')patch_unhatched = mpatches.Patch(facecolor='beige', hatch=' ', edgecolor="darkgrey", label='non-hatched')legend_2=plt.legend(handles=[patch_hatched, patch_unhatched], loc='center left', bbox_to_anchor=(1.15, 0.5), fontsize=12)# as soon as a second legend is made, the first disappears and needs to be added back againfigure.add_artist(legend_1) #python now knows that "figure" must take the "legend_1" along with "legend_2"有两个传说的情节我很确定它可以更加优雅和自动化。

Answer 1

此代码显示如何使用显式连接或子查询获取结果：

import sqlalchemy as sa

from sqlalchemy import orm

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

Parent_images = sa.Table(

    'Parent_images', Base.metadata,

    sa.Column('parent_id', sa.Integer, sa.ForeignKey('parents.id')),

    sa.Column('child_id', sa.Integer, sa.ForeignKey('children.id'))

)

class Parent(Base):

    __tablename__ = 'parents'

    id = sa.Column(sa.Integer, primary_key=True)

    name = sa.Column(sa.Unicode(100), nullable=False, unique=True)

    colours = orm.relationship('Child_s3', secondary=Parent_images, backref='parents')

    def __repr__(self):

        return 'Parent(name=%s)' % self.name

    __str__ = __repr__

class Child_s3(Base):

    __tablename__ = 'children'

    id = sa.Column(sa.Integer, primary_key=True)

    name = sa.Column(sa.Unicode)

    def __repr__(self):

        return 'Child_s3(name=%s)' % self.name

    __str__ = __repr__

if __name__ == '__main__':

    engine = sa.create_engine('sqlite:///')

    Base.metadata.drop_all(engine)

    Base.metadata.create_all(engine)

    Session = orm.sessionmaker(bind=engine)

    session = Session()

    for parent, child in [('boofoo', 'spam'), ('baz', 'foobar'), ('bar', 'quux')]:

        p1 = Parent(name=parent)

        session.add(p1)

        p1.colours.append(Child_s3(name=child))

    session.commit()

    print('Join')

    session = Session()

    q = (session.query(Parent)

                .join(Child_s3, Parent.colours)

                .filter(sa.or_(Parent.name.ilike('%foo%'),

                               Child_s3.name.ilike('%foo%'))))

    for p in q.all():

        print(p, p.colours)

    session.commit()

    print()

    print('Subquery')

    session = Session()

    q = (session.query(Parent)

                .filter(sa.or_(Parent.name.ilike('%foo%'),

                               Parent.colours.any(Child_s3.name.ilike('%foo%')))))

    for p in q.all():

        print(p, p.colours)

    session.commit()

    print()

连接查询

q = (session.query(Parent)

            .join(Child_s3, Parent.colours)

            .filter(sa.or_(Parent.name.ilike('%foo%'),

                           Child_s3.name.ilike('%foo%'))))

生成此 SQL

SELECT parents.id AS parents_id, parents.name AS parents_name 

FROM parents JOIN "Parent_images" AS "Parent_images_1" ON parents.id = "Parent_images_1".parent_id JOIN children ON children.id = "Parent_images_1".child_id 

WHERE lower(parents.name) LIKE lower(?) OR lower(children.name) LIKE lower(?)

子查询

q = (session.query(Parent)

            .filter(sa.or_(Parent.name.ilike('%foo%'),

                            Parent.colours.any(Child_s3.name.ilike('%foo%')))))

生成此 SQL：

SELECT parents.id AS parents_id, parents.name AS parents_name            

FROM parents                                                                                                                        

WHERE lower(parents.name) LIKE lower(?) OR (EXISTS (SELECT 1                                                                        

FROM "Parent_images", children                                                                                                      

WHERE parents.id = "Parent_images".parent_id AND children.id = "Parent_images".child_id AND lower(children.name) LIKE lower(?)))

该脚本从示例数据生成此输出：

Join

Parent(name=baz) [Child_s3(name=foobar)]

Parent(name=boofoo) [Child_s3(name=spam)]

Subquery

Parent(name=boofoo) [Child_s3(name=spam)]

Parent(name=baz) [Child_s3(name=foobar)]