On 03/08/2016 12:50 PM, [email protected] wrote:
I've spotted the following strange behaviour while using the last
version (SQLAlchemy==1.0.12) with SQLite or PostgreSQL (possibly others,
too).
To be short, I created a few polymorphic classes that map to their
respective tables;
I'm unable to perform any query containing a LEFT OUTER JOIN from the
"superclass" to one of the subclasses.
In fact, queries containing the left outer join are of the following
form, resulting in sqlalchemy.exc.OperationalError/ProgrammingError both
on SQLite and on PostgreSQL.
|
SELECT employee.name,manager.manager_name
FROM employee JOIN (employee JOIN manager ON employee.id =manager.id)ON
employee.id =manager.id
|
As you can see, the "ON" condition appears twice, rendering the query
invalid.
A full program showing the problematic behaviour follows:
your results are not surprising, because the "Manager" mapping as a
whole represents the JOIN of employee and manager; you are then emiting
a JOIN against that JOIN and that's exactly what the SQL output is saying.
for Core, you need to refer to the specific employee/manager tables
individually, using Employee.__table__ and Manager.__table__ most
directly. The examples at
http://docs.sqlalchemy.org/en/rel_1_0/orm/inheritance.html#advanced-control-of-which-tables-are-queried
illustrates a brief example.
|
fromsqlalchemy importColumn,Integer,String,ForeignKey
fromsqlalchemy.ext.declarative importdeclarative_base
fromsqlalchemy.orm importsessionmaker
fromsqlalchemy importcreate_engine
importsqlalchemy.sql
Base=declarative_base()
engine =create_engine('sqlite:///:memory:',echo=True)
Session=sessionmaker(bind=engine)
Session.configure(bind=engine)
session =Session()
classEmployee(Base):
__tablename__ ='employee'
id =Column(Integer,primary_key=True)
name =Column(String(50))
type =Column(String(50))
__mapper_args__ ={
'polymorphic_identity':'employee',
'polymorphic_on':type
}
classEngineer(Employee):
__tablename__ ='engineer'
id =Column(Integer,ForeignKey('employee.id'),primary_key=True)
engineer_name =Column(String(30))
__mapper_args__ ={
'polymorphic_identity':'engineer',
}
classManager(Employee):
__tablename__ ='manager'
id =Column(Integer,ForeignKey('employee.id'),primary_key=True)
manager_name =Column(String(30))
__mapper_args__ ={
'polymorphic_identity':'manager',
}
Base.metadata.create_all(engine)
e =Employee
m =Manager
q =sqlalchemy.sql.select(
[e.name,m.manager_name],
).select_from(
e.__table__.join(m)
)
print(q)
session.execute(q)
|
Am I missing something here, maybe abusing the Core API?
Best regards,
Adriano
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
