Sqlalchemy 为父母过滤父子表

Question

我认为@jrjc 的这个功能解决方案对我的理解来说相当令人困惑，因此，我更愿意稍微改变我自己的东西并进行调整。

所以，我花了一些时间才明白，当为情节创建第二个图例时，python 会自动删除第一个图例，这是add_artist()必须使用的时候。

添加第二个图例的另一个先决条件是命名图并将 .add_artist() 方法应用于该特定图，以便 python 知道将新部分粘贴在哪里。

简而言之，这就是我设法创建我想到的情节的方式，我希望这些评论能让它更清晰，对任何人都有用。

import matplotlib.pyplot as plt

from matplotlib.colors import LinearSegmentedColormap as coloring

import matplotlib.patches as mpatches

# copy the dfs below and use pd.read_clipboard() to reproduce

df_1

     A   B   C   D   E

Mg  10  15  23  25  27

Ca  30  33   0  20  17

df_2

     A   B   C   D   E

Mg  20  12   8  40  10

Ca   7  26  12  22  16

hatches=(' ', '//')

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 too

legend_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 again

figure.add_artist(legend_1) #python now knows that "figure" must take the "legend_1" along with "legend_2"

有两个传说的情节

我很确定它可以更加优雅和自动化。

Answer 1

此代码显示如何使用显式连接或子查询获取结果：import sqlalchemy as safrom sqlalchemy import ormfrom sqlalchemy.ext.declarative import declarative_baseBase = 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%'))))生成此 SQLSELECT 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(?)))该脚本从示例数据生成此输出：JoinParent(name=baz) [Child_s3(name=foobar)]Parent(name=boofoo) [Child_s3(name=spam)]SubqueryParent(name=boofoo) [Child_s3(name=spam)]Parent(name=baz) [Child_s3(name=foobar)]