On Dec 2, 2010, at 10:07 AM, Nagy Viktor wrote:
> 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
OK so what would you like the SQL to read ? the above translates into select
d.iddoc from d where d.iddoc=cast('100032' as integer), should be matching up
to 100032.
>
> 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].
> 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.
--
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.
