sorry, you were right, I've had a different bug in my code
On Thu, Dec 2, 2010 at 5:19 PM, Michael Bayer <mike...@zzzcomputing.com>wrote:
>
> 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 <mike...@zzzcomputing.com>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 sqlalch...@googlegroups.com.
>> To unsubscribe from this group, send email to
>> sqlalchemy+unsubscr...@googlegroups.com<sqlalchemy%2bunsubscr...@googlegroups.com>
>> .
>> 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 sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> 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 sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com<sqlalchemy%2bunsubscr...@googlegroups.com>
> .
> 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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
