[sqlalchemy] Re: User DataType for casting
I'll make a ticket for ROWID type if you like. Also, I'm afraid the CAST(NULL AS VARCHAR(255)) doesn't work with Oracle 8, but I don't have access to Oracle 8 at the moment. I'm afraid you need TO_NUMBER(NULL) or TO_CHAR(NULL), etc... On Jul 29, 1:20 pm, Michael Bayer mike...@zzzcomputing.com wrote: the idiomatic solution would be: class RowID(Unicode): pass from sqlalchemy.ext.compiler import compiles @compiles(RowId): def compile_rowid(compiler, element, **kw): return ROWID we should add ROWID to the oracle dialect. On Jul 29, 2010, at 12:54 PM, Kent wrote: I worked out this solution: class RowID(Unicode): def _compiler_dispatch(self, type_): return ROWID Please let me know if there are any obvious implications that I may have overlooked. Thanks -- 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 athttp://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.
[sqlalchemy] Re: Polymorphic union of two sibling classes (no real inheritance)
Hopefully you've got time to read a compliment: this polymorphism is
very cool (well, sqla in general). Great work!
Kent
On Jul 29, 5:41 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 29, 2010, at 5:00 PM, Kent Bower wrote:
Right. I understand. Thanks for pointing that out, you are correct.
My bigger concern was getting the ArTranBase mapper correct. Apparently
there is no need in this case to specify with_polymorphic= in the mapper.
Did I miss documentation on using 'polymorphic_union' without
with_polymorphic=? That seems to be working, I was just looking for
confirmation that this is a supported use-case.
that is probably correct.
On 7/29/2010 4:51 PM, Michael Bayer wrote:
What I meant was, if you want to say session.query(ArTranBase), which it
appears that you do, then you are querying against ArTranBase.
Since it seems like you want the polymorphic_union here, when you query
ArTranBase and you want it to eagerly load trancode and paymenttype,
it would need to have a relation() on the ArTranBase mapper so that it
knows what to join.
On Jul 29, 2010, at 4:46 PM, Kent wrote:
This seems to work, but I didn't find examples of this. Does this
look correct (assuming there is no parent table in the database and
all I really want is 2 'normal' mappers and a 3rd that performs a
polymorphoric_union)?
==
artran_union = polymorphic_union({
'artran': artrans_table,
'archive': artransarchive_table
}, 'type', 'artran_union')
artranbase_mapper = mapper(ArTranBase, artran_union,
polymorphic_on=artran_union.c.type)
# ArTran
--- #
mapper(ArTran, artrans_table, inherits=artranbase_mapper,
concrete=True, polymorphic_identity='artran',
properties={'trancode': relation(TranCode,
cascade='refresh-expire,expunge', lazy=False),
'paymenttype': relation(PaymentType,
cascade='refresh-expire,expunge', lazy=False)}
)
# ArTranArchive
--- #
mapper(ArTranArchive, artransarchive_table,
inherits=artranbase_mapper,
concrete=True, polymorphic_identity='archive',
properties={'trancode': relation(TranCode,
cascade='refresh-expire,expunge', lazy=False),
'paymenttype': relation(PaymentType,
cascade='refresh-expire,expunge', lazy=False)}
)
On Jul 29, 4:20 pm, Kent Bowerk...@retailarchitects.com wrote:
No, in fact, there is no ArTranBase table at all.
If I remove concrete inheritance, how do I issue a UNION of the two
tables and have the objects polymorphically loaded?
On 7/29/2010 4:18 PM, Michael Bayer wrote:
On Jul 29, 2010, at 2:31 PM, Kent wrote:
I'm getting a messy error that could be a bug, but is very likely
related to my setup of a set of 2 polymorphic classes I am attempting
to map.
One entity is a transaction and the other is a transaction_archive
record. The table structure is therefore very similar for both tables
and it seems to fit Concrete Table Inheritance, except there is no
'parent' entity. Rather, they are sister tables.
What I have mostly works until I get into loading this union as a
relation to another table... then I'm having problems.
I couldn't clearly see the correct way to set up this when there is
no real inheritance, but rather sister entities.
Can you suggest how to correctly map these 2 tables?
it looks fine to me except you're asking for eager loading, and if
you're querying from the ArTranBase you'd need to specify
relationship() at that level (as well as on each child). Example
athttp://www.sqlalchemy.org/docs/mappers.html#using-relationships-with-
OTOH if you are not querying from ArTranBase, remove the usage of
concrete inheritance altogether.
artran_union = polymorphic_union({
'artran': artrans_table,
'archive': artransarchive_table
}, 'type', 'artran_union')
artranbase_mapper = mapper(ArTranBase, artran_union,
with_polymorphic=('*', artran_union),
polymorphic_on=artran_union.c.type,
polymorphic_identity='ignored')
# ArTran
--- #
mapper(ArTran, artrans_table, inherits=artranbase_mapper,
concrete=True, polymorphic_identity='artran',
properties={'trancode': relation(TranCode,
cascade='refresh-expire,expunge', lazy=False),
'paymenttype': relation(PaymentType,
cascade='refresh-expire,expunge', lazy=False)}
)
# ArTranArchive
--- #
mapper(ArTranArchive, artransarchive_table,
[sqlalchemy] Re: how to use primary/secondary join when there are no foreign keys
I believe that you want your branche relation() on Cisdata, not Branchen. Additionally, I think you only want to list foreign_keys in foreign_keys=[]. My guess is: foreign_keys = [tables['cisbr'].c.ID_cisbr, tables['branchen'].c.ID_br] On Jul 30, 1:07 am, robert rottermann rob...@redcor.ch wrote: hi there I would like to define a m:n relation between two tables that are linked by an association table. I am using MySQL 5.1 and SA 0.6.3 this is the select that I want to implement: select * from cisdata c, cisbr cb branchen b, where c.ID_cis = cb.ID_cisbr and cb.ID_br = b.ID_br and b.br = 'Fotografie' as you can see whe have two tables: cis branchen that are linked trough an association table cisbr. I am using the following setup: Base = declarative_base(engine) Base.metadata.reflect() tables = Base.metadata.tables # - # - # class Cisdata(Base): __table__ = tables['cisdata'] class Branchen(Base): branche = relation( 'Cisdata', secondary = tables['cisbr'], primaryjoin = tables['cisdata'].c.ID_cis==tables['cisbr'].c.ID_cisbr, secondaryjoin = tables['branchen'].c.ID_br==tables['cisbr'].c.ID_br, foreign_keys = [tables['cisdata'].c.ID_cis, tables['cisbr'].c.ID_cisbr, tables['cisbr'].c.ID_br, tables['branchen'].c.ID_br], backref=firmen, ) __table__ = tables['branchen'] now when I access a table i get an error: Could not determine relationship direction for primaryjoin condition 'cisdata.`ID_cis` = cisbr.`ID_cisbr`', on relationship Branchen.branche. Do the columns in 'foreign_keys' represent only the 'foreign' columns in this join condition ? when I use the following setup: I would be very glad, if somebody could tell me, what I am doing wrong. thanks robert the association table cisbr has more fields than only the keys. like this: CREATE TABLE `cis`.`cisbr` ( `ID_cisbr` int(11) NOT NULL AUTO_INCREMENT, `ID_br` int(11) NOT NULL, `ID_cis` int(11) NOT NULL, `value` smallint(6) NOT NULL DEFAULT '0', `timestmp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`ID_cisbr`), KEY `ID_br` (`ID_br`,`ID_cis`,`value`) ) -- 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.
[sqlalchemy] Re: Polymorphic union of two sibling classes (no real inheritance)
I am having a problem when I'm specifying an order_by for a
relationship entity's column when the relationship is this
polymorphic_union.
orders = DBSession.query(Order)\
.options(joinedload(Order.transactions))\
.filter(Order.customerid==customerid)\
.order_by(Order.orderdate, desc(ArTranBase.postdate)).all()
The SQL output is like this:
#
SELECT * ...
FROM orders,
orderdetails orderdetails_1,
...
...,
/ polymorphic union (labeled 'anon_1') /
(SELECT artrans.orderid AS orderid,
artrans.status AS status,
...
'artran' AS type
FROM artrans
UNION ALL
SELECT artransarchive.orderid AS orderid,
artransarchive.status AS status,
...
'archive' AS type
FROM artransarchive) anon_1,
paymenttypes paymenttypes_1,
...
WHERE ... /* joins */
AND orders.orderid = anon_1.orderid(+)
... /* more joins */
ORDER BY orders.orderdate,
artran_union.postdate DESC
#
*Notice the Problem:
The ORDER BY is specifying the column 'artran_union.postdate' instead
of 'anon_1.postdate'. The joins are correctly using 'anon_1'.
(or, perhaps the union itself should be labeled 'artran_union' or
'artran_union_1').
Mapping info:
# Order
order_mapper = mapper(Order, orders_table,
properties={
...
...
'transactions': relation(ArTranBase,
cascade='refresh-expire,expunge')
})
artran_union = polymorphic_union({
'artran': artrans_table,
'archive': artransarchive_table
}, 'type', 'artran_union')
# ArTranBase
artranbase_mapper = mapper(ArTranBase, artran_union,
polymorphic_on=artran_union.c.type,
properties={'trancode': relation(TranCode,
cascade='refresh-expire,expunge', lazy=False),
'paymenttype': relation(PaymentType,
cascade='refresh-expire,expunge', lazy=False)}
)
# ArTran
mapper(ArTran, artrans_table, inherits=artranbase_mapper,
concrete=True, polymorphic_identity='artran',
properties={'trancode': relation(TranCode,
cascade='refresh-expire,expunge', lazy=False),
'paymenttype': relation(PaymentType,
cascade='refresh-expire,expunge', lazy=False)}
)
# ArTranArchive
mapper(ArTranArchive, artransarchive_table,
inherits=artranbase_mapper,
concrete=True, polymorphic_identity='archive',
properties={'trancode': relation(TranCode,
cascade='refresh-expire,expunge', lazy=False),
'paymenttype': relation(PaymentType,
cascade='refresh-expire,expunge', lazy=False)}
)
--
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.
Re: [sqlalchemy] Re: Polymorphic union of two sibling classes (no real inheritance)
On Jul 30, 2010, at 9:51 AM, Kent wrote:
I am having a problem when I'm specifying an order_by for a
relationship entity's column when the relationship is this
polymorphic_union.
orders = DBSession.query(Order)\
.options(joinedload(Order.transactions))\
.filter(Order.customerid==customerid)\
.order_by(Order.orderdate, desc(ArTranBase.postdate)).all()
you're making a common mistake here which is to ORDER BY an eagerly loaded
relationship (probably our first FAQ entry).joinedload is strictly about
loading collection contents and will alias itself so as not to interfere with
the query's existing structure. Here you'd want to
query(Order).join(Order.transactions).options(contains_eager(Order.transactions)).order_by(...).
But the awkwardness here is that if Order.transactions is a collection, the
order_by() you're doing doesn't make much sense - if Order 1 and Order 2 have
the same date, but their collection of transactions are interleaved in terms of
their dates, the ordering between them is somewhat meaningless.
If the order_by() is instead intended to determine the ordering just *within*
the Order.transactions collections, you'd use the order_by option on the
transactions relationship() itself.
The SQL output is like this:
#
SELECT * ...
FROM orders,
orderdetails orderdetails_1,
...
...,
/ polymorphic union (labeled 'anon_1') /
(SELECT artrans.orderid AS orderid,
artrans.status AS status,
...
'artran' AS type
FROM artrans
UNION ALL
SELECT artransarchive.orderid AS orderid,
artransarchive.status AS status,
...
'archive' AS type
FROM artransarchive) anon_1,
paymenttypes paymenttypes_1,
...
WHERE ... /* joins */
AND orders.orderid = anon_1.orderid(+)
... /* more joins */
ORDER BY orders.orderdate,
artran_union.postdate DESC
#
*Notice the Problem:
The ORDER BY is specifying the column 'artran_union.postdate' instead
of 'anon_1.postdate'. The joins are correctly using 'anon_1'.
(or, perhaps the union itself should be labeled 'artran_union' or
'artran_union_1').
Mapping info:
# Order
order_mapper = mapper(Order, orders_table,
properties={
...
...
'transactions': relation(ArTranBase,
cascade='refresh-expire,expunge')
})
artran_union = polymorphic_union({
'artran': artrans_table,
'archive': artransarchive_table
}, 'type', 'artran_union')
# ArTranBase
artranbase_mapper = mapper(ArTranBase, artran_union,
polymorphic_on=artran_union.c.type,
properties={'trancode': relation(TranCode,
cascade='refresh-expire,expunge', lazy=False),
'paymenttype': relation(PaymentType,
cascade='refresh-expire,expunge', lazy=False)}
)
# ArTran
mapper(ArTran, artrans_table, inherits=artranbase_mapper,
concrete=True, polymorphic_identity='artran',
properties={'trancode': relation(TranCode,
cascade='refresh-expire,expunge', lazy=False),
'paymenttype': relation(PaymentType,
cascade='refresh-expire,expunge', lazy=False)}
)
# ArTranArchive
mapper(ArTranArchive, artransarchive_table,
inherits=artranbase_mapper,
concrete=True, polymorphic_identity='archive',
properties={'trancode': relation(TranCode,
cascade='refresh-expire,expunge', lazy=False),
'paymenttype': relation(PaymentType,
cascade='refresh-expire,expunge', lazy=False)}
)
--
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.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
