RE: [sqlalchemy] Re: open session blocks metadata create_all method
On Wed, 28 Jul 2010 15:17:09 +0530 (IST), Faheem Mitha fah...@email.unc.edu wrote: Hi, When calling create_all on a metadata instance after a session has alrady been opened causes the create_all to hang, I assume because the session is blocking the create_all. Is there some way to get create_all to use the existing session, or any other graceful way around this? Thanks. I guess another option is to close and then reopen the session after the create_all has been called, but I'd prefer not to do that if possible. Puting a session.close() before the create_all fixes the problem. I assume this means that create_all doesn't work in the middle of a transaction, or something like that? You can tell meta.create_all() to use the same underlying DB connection as the session by using the session.connection() method with the 'bind' parameter to create_all(). Ie. connection = session.connection() meta.create_all(bind=connection) See the docs at http://www.sqlalchemy.org/docs/session.html#using-sql-expressions-with-s essions and http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html#sqlalche my.schema.MetaData.create_all Hope that helps, Simon -- 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] Support for Common Table Expressions (CTE)?
Is there any support yet in SQLAlchemy for Common Table Expressions e.g. WITH foo(blurp, bletch) AS (SELECT bar*2, bletch from banana where bletch 3) SELECT f.* FROM foo as f where blurp 1 I have not been following SA development for some months (due to a job move) so I am not yet up to speed with the new yummy @compiles stuff (which looks like it could certainly help with rolling my own support for CTEs if it is not already in the core) thanks, pjjH -- 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] error:Could not determine relationship direction for primaryjoin condition
hi there 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 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. the association table cisbr has more fields than only 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`) ) the setup I use is as following: 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'] -- 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: open session blocks metadata create_all method
On Thu, 29 Jul 2010 11:36:43 +0100, King Simon-NFHD78 simon.k...@motorola.com wrote: You can tell meta.create_all() to use the same underlying DB connection as the session by using the session.connection() method with the 'bind' parameter to create_all(). Ie. connection = session.connection() meta.create_all(bind=connection) See the docs at http://www.sqlalchemy.org/docs/session.html#using-sql-expressions-with-s essions and http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html#sqlalche my.schema.MetaData.create_all Hope that helps, Hi Simon, Thanks. Do you understand why this blocking takes place? I assume by default create_all tries to make a different connection, and fails for some reason? Regards, Faheem. -- 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: open session blocks metadata create_all method
On 7/29/10 17:18 , Faheem Mitha wrote: Hi Simon, Thanks. Do you understand why this blocking takes place? I assume by default create_all tries to make a different connection, and fails for some reason? My guess is that it does not fail, but your database is blocking the create_all statements while another transaction is active. Wichert. -- 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] User DataType for casting
I'm using an Oracle legacy database and can't add a primary key to a table with none, so I am using ROWID as the primary key so sqlalchemy has a unique id. I'm also using (attempting to use) this table pornographically (Concrete Table Inheritance). The trouble I'm having is that Oracle complains about the UNION because the primary key Unicode(255) is really a ROWID. So, I have something like this: SELECT * FROM a.a_id, /*really a ROWID */ ... ... UNION ALL CAST(NULL AS VARCHAR(255)) as a_id, ... ... Oracle is not happy because VARCHAR(255) isn't the same TYPE as ROWID. I was speculating I could solve this by using a User Defined Type. Is there a way I could accomplish either: SELECT * FROM CAST(a.a_id AS VARCHAR(255)) as a_id, /*really a ROWID */ ... ... UNION ALL CAST(NULL AS VARCHAR(255)) as a_id, ... ... === OR === SELECT * FROM a.a_id, /*really a ROWID */ ... ... UNION ALL CAST(NULL AS ROWID) as a_id, ... ... Aside from this, I would want the User Data Type to act in all other ways like Unicode. Thank you very much in advance. -- 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: User DataType for casting
Oops! I didn't check my spell checker closely I meant 'polymorphically' not 'pornographically'!! -- 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: User DataType for casting
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 at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: User DataType for casting
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 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.
[sqlalchemy] window functions
Hi, just wondering if there's support for window functions, or if there's any plans to add this yet? 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 at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Polymorphic union of two sibling classes (no real inheritance)
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?
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,
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)}
)
Thanks in advance.
--
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: open session blocks metadata create_all method
On Thu, 29 Jul 2010 15:18:33 + (UTC) Faheem Mitha fah...@email.unc.edu wrote: On Thu, 29 Jul 2010 11:36:43 +0100, King Simon-NFHD78 simon.k...@motorola.com wrote: You can tell meta.create_all() to use the same underlying DB connection as the session by using the session.connection() method with the 'bind' parameter to create_all(). Ie. connection = session.connection() meta.create_all(bind=connection) See the docs at http://www.sqlalchemy.org/docs/session.html#using-sql-expressions-with-s essions and http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html#sqlalche my.schema.MetaData.create_all Hope that helps, Hi Simon, Thanks. Do you understand why this blocking takes place? I assume by default create_all tries to make a different connection, and fails for some reason? Regards, Faheem. On PostgreSQL what's probably happening is that the connection that the session object is using is IDLE IN TRANSACTION but is holding some lock, probably RowExclusiveLock from uncommitted UPDATE/INSERT/DELETEs. Then, create_all(), when executed on a different connection, enters into a lock wait because virtually all DDL changes automatically acquire AccessExclusiveLock, which conflicts with locks the session is holding. The blocking is because the DDL connection cannot commit until the lock conflict is resolved, by either the session's connection rolling back or committing (causing the DDL-containing transaction to commit or abort, respectively). Here's a PostgreSQL wiki page with an good query for troubleshooting lock waits: http://wiki.postgresql.org/wiki/Lock_dependency_information Also, if you use pgAdminIII, it has an excellent lock monitor tool built in. I suggest doing a run of your application such that it gets blocked in create_all(), and then while it's blocked, check to see what lock is blocking it using the above linked query. Hope this helps.. -Kyle -- 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] window functions
it is a TODO as ticket #1844. You can implement with the compiler extension for now. On Jul 29, 2010, at 2:22 PM, ellonweb wrote: Hi, just wondering if there's support for window functions, or if there's any plans to add this yet? 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 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.
Re: [sqlalchemy] Polymorphic union of two sibling classes (no real inheritance)
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 at
http://www.sqlalchemy.org/docs/mappers.html#using-relationships-with-inheritance
.
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,
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)}
)
Thanks in advance.
--
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.
Re: [sqlalchemy] Polymorphic union of two sibling classes (no real inheritance)
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 at
http://www.sqlalchemy.org/docs/mappers.html#using-relationships-with-inheritance
.
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,
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)}
)
Thanks in advance.
--
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.
[sqlalchemy] Re: Polymorphic union of two sibling classes (no real inheritance)
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 Bower k...@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,
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)}
)
Thanks in advance.
--
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.
Re: [sqlalchemy] Re: Polymorphic union of two sibling classes (no real inheritance)
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 Bower k...@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,
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)}
)
Thanks in advance.
--
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
Re: [sqlalchemy] Re: Polymorphic union of two sibling classes (no real inheritance)
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.
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,
inherits=artranbase_mapper,
concrete=True, polymorphic_identity='archive',
properties={'trancode': relation(TranCode,
cascade='refresh-expire,expunge', lazy=False),
Re: [sqlalchemy] Re: Polymorphic union of two sibling classes (no real inheritance)
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,
inherits=artranbase_mapper,
concrete=True,
[sqlalchemy] Re: Support for Common Table Expressions (CTE)?
I see that a ticket has just been opened up on this topic. http://www.sqlalchemy.org/trac/ticket/1859 I will follow up there. thanks, pjjH On Jul 29, 8:33 am, phrrn...@googlemail.com phrrn...@googlemail.com wrote: Is there any support yet in SQLAlchemy for Common Table Expressions e.g. WITH foo(blurp, bletch) AS (SELECT bar*2, bletch from banana where bletch 3) SELECT f.* FROM foo as f where blurp 1 I have not been following SA development for some months (due to a job move) so I am not yet up to speed with the new yummy @compiles stuff (which looks like it could certainly help with rolling my own support for CTEs if it is not already in the core) thanks, pjjH -- 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: Support for Common Table Expressions (CTE)?
oh weird, someone asked about window functions, and I got the two kind of confused and responded to that person but not to you. On Jul 29, 2010, at 6:43 PM, phrrn...@googlemail.com wrote: I see that a ticket has just been opened up on this topic. http://www.sqlalchemy.org/trac/ticket/1859 I will follow up there. thanks, pjjH On Jul 29, 8:33 am, phrrn...@googlemail.com phrrn...@googlemail.com wrote: Is there any support yet in SQLAlchemy for Common Table Expressions e.g. WITH foo(blurp, bletch) AS (SELECT bar*2, bletch from banana where bletch 3) SELECT f.* FROM foo as f where blurp 1 I have not been following SA development for some months (due to a job move) so I am not yet up to speed with the new yummy @compiles stuff (which looks like it could certainly help with rolling my own support for CTEs if it is not already in the core) thanks, pjjH -- 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.
[sqlalchemy] Re: What is a good pattern for using sqlalchemy+psycopg2+gevent in a pylons app
Sorry I wasnt monitoring this thread. Didnt get a response right away and thought no one had responded. I also think that theoretically there really would be not much of a change to using sqlalchemy (within pylons - pylons already creates a scopedsession object for every web request anyhow) in a co-routine framework such as gevent. Once I get a chance I would try it out and update this thread accordingly. Thanks for your inputs guys. On Jun 25, 5:33 pm, Yang Zhang yanghates...@gmail.com wrote: On Fri, Jun 25, 2010 at 6:41 AM, Michael Bayer mike...@zzzcomputing.com wrote: That's as deep as my thought goes on this and its all based on hypotheticals since I've never used twisted or greenlets or anything like that. Sorry if I'm totally off on howgevent/greenlets work, the linked documents didn't really make it clear how they work for someone who isn't already familiar. That's completely fair, the docs leave a lot to be desired. Think of greenlets as an implementation of cooperative threading. In typical threading, threads can first of all run in parallel, and the threading is also preemptive, meaning context switches between threads can happen at any time. With greenlets, threads are run in a single real OS thread (no parallelism, only multiplexed concurrency), and context switches between threads happen only voluntarily. Why is this useful? Because then you can write non-blocking code in a blocking style. Non-blocking IO is useful for scalable systems development, but event-driven programming (a la twisted) tends to be more tedious and less natural than programming with blocking IO. Plus, most existing code is written against blocking IO, but event-driven programming makes those difficult to reuse. So to answer your first question of what things would look like in an asynchronous world: with cooperative threads like greenlets, everything would hopefully look identical. Embracing event-driven style would indeed spell out significant changes to both sqlalchemy and user code (and that's also not what I'm personally interested in using). Upon closer inspection, it seems that changes might not even be necessary for sqlalchemy, since one can globally set the asynchronous callback for psycopg to hook directly intogevent. http://bitbucket.org/dvarrazzo/psycogreen/src/tip/gevent/psyco_gevent.py -- Yang Zhanghttp://yz.mit.edu/ -- 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] Assigning column defaults after definition?
I've got a bunch of old sqlalchemy code using the declarative framework where the default field values could be assigned after the initial definition, as in this reduced example: ### from sqlalchemy import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relation, sessionmaker Base = declarative_base() class Bogus(Base): __tablename__ = 'bogus' pk = Column(Integer, primary_key=True) c1 = Column(Integer) c1.default = 2000## NOW A PROBLEM ## engine = create_engine(sqlite://, echo = True) Base.metadata.create_all(engine) ### Now this code crashes on the create_all() call inside visitors.py's traverse_using() call, with: AttributeError: 'int' object has no attribute '__visit_name__' If I move the default assignments into the column constructor with the default= kwarg (as is proper according to current docs), it works fine. Is there a clean/functional/safe way to assign defaults _after_ the first assignment? The old code collected together all the default assignments in one spot to cut down on the size of the (large) table definition code and improve readability of the code... it would be great to keep it that way. I'm using sqla 0.6.3 now. -- 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] Assigning column defaults after definition?
On Jul 29, 2010, at 11:08 PM, Russell Warren wrote: I've got a bunch of old sqlalchemy code using the declarative framework where the default field values could be assigned after the initial definition, as in this reduced example: ### from sqlalchemy import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relation, sessionmaker Base = declarative_base() class Bogus(Base): __tablename__ = 'bogus' pk = Column(Integer, primary_key=True) c1 = Column(Integer) c1.default = 2000## NOW A PROBLEM ## engine = create_engine(sqlite://, echo = True) Base.metadata.create_all(engine) ### Now this code crashes on the create_all() call inside visitors.py's traverse_using() call, with: AttributeError: 'int' object has no attribute '__visit_name__' use default=ColumnDefault(2000). If I move the default assignments into the column constructor with the default= kwarg (as is proper according to current docs), it works fine. Is there a clean/functional/safe way to assign defaults _after_ the first assignment? The old code collected together all the default assignments in one spot to cut down on the size of the (large) table definition code and improve readability of the code... it would be great to keep it that way. I'm using sqla 0.6.3 now. -- 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.
[sqlalchemy] how to use primary/secondary join when there are no foreign keys
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.
