I've tried what you proposed, but it still doesn't work:
my code is now:
production_request = column_property(
select([ProductionRequests.id])\
.where(ProductionRequests.id==cast(func.substring(nr_proforma,
3), Integer))\
.where(ProductionRequests.type==type),
deferred=False
)
this gives
SELECT documenteiesire."IDDoc"
FROM documenteiesire
WHERE documenteiesire."IDDoc" =
CAST(substring(documentedelucru."NrProforma", %(substring_1)s) AS INTEGER
where
'substring_1': 3,
even though nr_proforma='FP100032'
thus it returns None
btw, I've taken the substring command for postgresql, there
substring(mystring from 3) is meaningful
On Thu, Dec 2, 2010 at 3:54 PM, Michael Bayer <[email protected]>wrote:
>
> On Dec 2, 2010, at 5:53 AM, Nagy Viktor wrote:
>
> > hi,
> >
> > I would like to represent an existing database and its associations in
> sqlalchemy. Unfortunately, it has many flows, like two tables are related
> via a substring from one table.
> >
> > something like this
> >
> > class ParentClass:
> > id = ...
> > type = ...
> >
> > class ChildClass:
> > nr_proforma = Column('', String)
> > type = ...
> >
> > parent_id = column_property(
> > select([ParentClass.id])\
> > .where(ParentClass.id==cast(func.substring(nr_proforma + '
> from 3'), Integer))\
> > .where(ParentClass.type==type),
> > deferred=False
> > )
> >
> > I hope the above example describes well what I would like to achieve,
> unfortunately, the resulting sql is not what I would like, as nr_proforma is
> 'misinterpreted'
> >
> > The resulting query is:
> > SELECT documenteiesire."IDDoc" \nFROM documenteiesire \nWHERE
> documenteiesire."IDDoc" = CAST(substring(documentedelucru."NrProforma" ||
> %(NrProforma_1)s) AS INTEGER) AND documenteiesire."Tip" =
> documentedelucru."Tip"
> >
> > another alternative
> >
> > parent_id = column_property(
> > select([ParentClass.id])\
> > .where(ParentClass.id==cast(func.substring('%s from 3' %
> nr_proforma), Integer))\
> > .where(ParentClass.type==type),
> > deferred=False
> > )
> > results in
> > SELECT documenteiesire."IDDoc" \nFROM documenteiesire \nWHERE
> documenteiesire."IDDoc" = CAST(substring(%(substring_1)s) AS INTEGER
> > with
> > 'substring_1': 'NrProforma from 3'
>
>
> The above SQL snippets would appear to accurately reflect what you're
> telling it to do, though I can see that you're having some issues with
> func.substring(). SQL substring usually would look like
> func.substring(nr_proforma, 3, 2). I don't know what "from 3" is
> attempting to accomplish, if you're trying to render that exact text in the
> SQL string, that's not how to do it (use text() or literal_column()).
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected]<sqlalchemy%[email protected]>
> .
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
