SQL正常错误 - "Please configure one or more attributes for these same-named columns explicitly."

Question

我是sqlalchemy的新手。试图得到一个查询工作，我有一个连接的问题。

我有两个表，它们都有一个名为"Id"的列，我需要在那个表上连接。我的代码是这样的:

table1 = server.tab1
table2 = server.tab2
joined = server.join(table1,table2, table1.Id == table2.Id)
where = table1.createDate > start
results = joined.filter(where).all()

这会导致以下错误消息:

隐式组合列table1。列表2的Id。Id属性下的Id。请显式地为这些同名列配置一个或多个属性。

问题是，我如何配置这些属性?

TIA !

Answer 1

With sql soup

joined = server.session.query(table1).join((table2,table1.id == table2.id))
where = table1.createDate > start
results = joined.filter(where).all()

Answer 2

我也有同样的问题，所以我想我会添加我想出的解决方案(基于http://www.mail-archive.com/[e-mail]/msg23735.html)。这当然不是我写过的最干净的代码，但是使用上面的例子，它大概是:

from sqlalchemy import select
aliased_table1 = select([
    table1.c.Id.label("renamed_id_col"),
    table1.c.any_other_columns_you_want_returned_but_not_renamed,
    ...
]).correlate(None).alias()
joined = server.join(aliased_table1, table2, aliased_table1.c.renamed_id_col == table2.Id)
where = aliased_table1.c.createDate > start
results = joined.filter(where).all()

Answer 3

一种方法是在一个表中标记所有列，这样您就不会有任何列名冲突:

table1 = server.tab1
table2 = server.with_labels(server.tab2)
joined = server.join(table1,table2, table1.Id == table2.tab2_Id)
where = table1.createDate > start
results = joined.filter(where).all()

table2最终成为一个带标签的表，其中所有列名都以表名开头，以便不干扰table1中的列名。

Answer 4

您可以使用sqlalchemy提供的连接特性，参见下面的示例，不需要手动操作，sqlalchme为我们完成，

from sqlalchemy import create_engine, Column, String, Integer, Table, ForeignKey
from sqlalchemy.orm import mapper, relationship
from sqlalchemy.schema import MetaData
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class User(Base):
    __tablename__ = 'users'
    id = Column('user_id',Integer,primary_key = True)
    name = Column('user_name',String(20))
    addresses = relationship("Address",backref="user")
class Address(Base):
    __tablename__ = 'addresses'
    id = Column('user_id',ForeignKey('users.user_id'))
    address = Column('address',String(30))
    pk = Column('address_id',Integer,primary_key=1)
if __name__ == "__main__":
    engine = create_engine("sqlite://", echo = True)
    Base.metadata.create_all(engine)  
    session = sessionmaker(bind=engine)()
    u1 = User(name='japan')
    session.add(u1)
    session.commit()
    u1.addresses.append(Address(address='a1'))
    u1.addresses.append(Address(address='a2'))
    session.flush()
    q = session.query(User).join(User.addresses).all()
    print "="*23,q