sorry, i have copy-and-paste incomplete parts of my real model definitions.
run the attached file and you will see the exception....
sqlalchemy.exc.InterfaceError: (InterfaceError) Error binding parameter 0 -
probably unsupported type. u'SELECT (SELECT
sum(nestingsheetlayoutitem.quantity) AS sum_1 \nFROM
nestingsheetlayoutitem, nestingsheetlayout \nWHERE
nestingsheetlayout.project_id = ? AND jobitem.part_id =
nestingsheetlayoutitem.part_id) AS anon_1, (SELECT job.project_id \nFROM
job \nWHERE job.id = jobitem.job_id) AS anon_2, jobitem.id AS jobitem_id,
jobitem.job_id AS jobitem_job_id, jobitem.quantity AS jobitem_quantity,
jobitem.part_id AS jobitem_part_id \nFROM jobitem' (<ColumnProperty at
0x935c48c; no key>,)
On Sunday, June 1, 2014 1:47:06 AM UTC+3, Michael Bayer wrote:
>
> your example is incomplete in that it is missing named columns such as
> “part_id” and such, but when I fill those in I’m able to form a SELECT
> using the properties you refer to:
>
> SELECT (SELECT sum(nestingsheetlayoutitem.quantity) AS sum_1
> FROM nestingsheetlayoutitem, nestingsheetlayout
> WHERE nestingsheetlayout.project_id = :project_id_1 AND jobitem.part_id =
> nestingsheetlayoutitem.part_id) AS anon_1, (SELECT job.project_id
> FROM job
> WHERE job.id = jobitem.job_id) AS anon_2, jobitem.id AS jobitem_id,
> jobitem.job_id AS jobitem_job_id, jobitem.quantity AS jobitem_quantity,
> jobitem.part_id AS jobitem_part_id
> FROM jobitem
>
>
> I can see that this query has problems, such as that your column property
> isn’t equating nestingsheetlayoutitem and nestingsheetlayout to each other
> and you might be getting too many rows back, but the query is representing
> as close as I can tell what you’re asking it to render. The subqueries
> correlate to job_item on the outside, and sometimes there’s extra things
> needed to make these correlations work out OK but here they seem to be
> unambiguous.
>
> please work with the attached to show more specifically the part that is
> not working. I will note that column_property does have some
> configurational limitations within declarative, in that if you wanted to
> refer to a column_property attached to JobItem directly you have to jump
> through some extra hoops at the moment, there’s plans to get column_props
> to be more flexible within declarative. But the mapping here doesn’t seem
> to be running into those.
>
>
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr
Base = declarative_base()
class Project(Base):
__tablename__ = 'project'
id = Column(Integer, autoincrement=True, primary_key=True)
class NestingSheetLayout(Base):
__tablename__ = 'nestingsheetlayout'
id = Column(Integer, autoincrement=True, primary_key=True)
project_id = Column(None, ForeignKey(Project.id, ondelete='CASCADE'))
project = relationship(Project,
backref=backref('nesting_sheet_layouts',
cascade='all,delete-orphan', passive_deletes=True))
class NestingSheetLayoutItem(Base):
__tablename__ = 'nestingsheetlayoutitem'
id = Column(Integer, autoincrement=True, primary_key=True)
part_id = Column(Integer)
quantity = Column(Integer)
nesting_sheet_layout_id = Column(None, ForeignKey(NestingSheetLayout.id, ondelete='CASCADE'))
nesting_sheet_layout = relationship(NestingSheetLayout,
backref=backref('items', cascade='all,delete-orphan', passive_deletes=True))
@declared_attr
def project_id(cls):
return column_property(
select(
[NestingSheetLayout.project_id],
NestingSheetLayout.id==cls.nesting_sheet_layout_id
),
)
class Job(Base):
__tablename__ = 'job'
id = Column(Integer, autoincrement=True, primary_key=True)
project_id = Column(None, ForeignKey(Project.id))
project = relationship(Project,
backref=backref('jobs', cascade='all, delete-orphan'))
class JobItem(Base):
__tablename__ = 'jobitem'
id = Column(Integer, autoincrement=True, primary_key=True)
job_id = Column(None, ForeignKey(Job.id, ondelete='CASCADE'))
job = relationship(Job, lazy=True,
backref=backref('items', cascade='all', passive_deletes=True))
quantity = Column(Integer)
part_id = Column(Integer)
@declared_attr
def project_id(cls):
return column_property(
select([Job.project_id], Job.id==cls.job_id),
)
@declared_attr
def nested_quantity(cls):
return column_property(
select(
[func.sum(NestingSheetLayoutItem.quantity)],
(
(cls.project_id==NestingSheetLayout.project_id) &
(cls.part_id == NestingSheetLayoutItem.part_id)
)
)
)
s = Session()
s.bind = engine = create_engine('sqlite:///')
Base.metadata.create_all(engine)
print s.query(JobItem).all()
