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:
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
import sqlalchemy.sql
Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
Session.configure(bind=engine)
session = Session()
class Employee(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
}
class Engineer(Employee):
__tablename__ = 'engineer'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
engineer_name = Column(String(30))
__mapper_args__ = {
'polymorphic_identity': 'engineer',
}
class Manager(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].
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.
