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.
