[sqlalchemy] order of execution with MapperExtension
I have a mapper created with a mapper extension that has an after_update() override. For a table in the mapper I do an update and then a commit(). This is the resulting order of execution: update instance (setting an attribute on the mapped class) commit after_update called on instance I.e. The after_update is called after the commit (after the commit has actually committed to the DB , in fact). How can I cause it to be called before the commit? I have tried flushing explicitly before committing (although the session is autoflush) but it didn't change the behavior. Trying to use before_update instead, didn't change things either. Why the order is important : In the after_update I save to another table of the session. I would like the commit() call to commit the whole transaction - both the original update and the insertion in the aftre_update(). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: order of execution with MapperExtension
PLEASE IGNORE PREVIOUS. It turns out that explicitly flushing does change the order (made a silly coding error before). I am all set, but the question remains why autoflush isn't enough. On May 23, 2:15 pm, Moshe C. [EMAIL PROTECTED] wrote: I have a mapper created with a mapper extension that has an after_update() override. For a table in the mapper I do an update and then a commit(). This is the resulting order of execution: update instance (setting an attribute on the mapped class) commit after_update called on instance I.e. The after_update is called after the commit (after the commit has actually committed to the DB , in fact). How can I cause it to be called before the commit? I have tried flushing explicitly before committing (although the session is autoflush) but it didn't change the behavior. Trying to use before_update instead, didn't change things either. Why the order is important : In the after_update I save to another table of the session. I would like the commit() call to commit the whole transaction - both the original update and the insertion in the aftre_update(). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Direct execute of postgres COPY
On May 23, 2008, at 2:49 AM, schickb wrote: On May 22, 8:37 pm, schickb [EMAIL PROTECTED] wrote: Maybe SA is putting second execute in a transaction that isn't being commited? That was the problem. I found this in the SA docs: While many DBAPIs implement a flag called autocommit, the current SQLAlchemy behavior is such that it implements its own autocommit. This is achieved by searching the statement for strings like INSERT, UPDATE, DELETE, etc. and then issuing a COMMIT automatically if no transaction is in progress. I fixed the issue by wrapping the execute with trans = dbconn.begin() before and trans.commit() after. Seems like the decision to autocommit raw sql statements would be better left to the DBAPIs since they know more about non-standard sql statements like COPY. MySQLDB and Pysqlite are the only two DBAPIs I know of which have an autocommit feature. By using our own we gain much better consistency of behavior across all platforms. Add a trac ticket for COPY and we'll add it to the list of supported transactional keywords for PG. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: order of execution with MapperExtension
On May 23, 2008, at 7:26 AM, Moshe C. wrote: PLEASE IGNORE PREVIOUS. It turns out that explicitly flushing does change the order (made a silly coding error before). I am all set, but the question remains why autoflush isn't enough. autoflush will occur before every query execute and within the commit(). the behavior of after_update() is consistent in all cases. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: order of execution with MapperExtension
Evidently, when the autoflush occurred within the commit(), the database transaction COMMIT itself happened before the call to after_update() . The fact is that explicitly calling session.flush() immediately before calling session.commit(), changed the final state of the DB. This means the commit() with autoflush is not equivalent to flush and then commit. On 5/23/08, Michael Bayer [EMAIL PROTECTED] wrote: On May 23, 2008, at 7:26 AM, Moshe C. wrote: PLEASE IGNORE PREVIOUS. It turns out that explicitly flushing does change the order (made a silly coding error before). I am all set, but the question remains why autoflush isn't enough. autoflush will occur before every query execute and within the commit(). the behavior of after_update() is consistent in all cases. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: order of execution with MapperExtension
On May 23, 2008, at 10:44 AM, Moshe Cohen wrote: Evidently, when the autoflush occurred within the commit(), the database transaction COMMIT itself happened before the call to after_update() . thats not how it works. the steps are: session.commit() session.flush() mapper._save_obj() before_update/before_insert() UPDATE/INSERT after_update/after_insert() engine.commit() The fact is that explicitly calling session.flush() immediately before calling session.commit(), changed the final state of the DB. This means the commit() with autoflush is not equivalent to flush and then commit. it would be helpful if you could provide the evidence you're basing this on. it sounds like your Session is not actually within a transaction when you call flush(), so that the flush() begins and commits its own transaction. If transactional=True, this would be a bug. But then calling session.commit() should raise an error, so not sure how you'd achieve that behavior, and I'm not seeing any codepath that could produce that behavior - when transactional=True, a begin() is issued in all cases before any flush call. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: mySQL force index?
Geoff wrote: Does SQLA have any mechanism to use FORCE INDEX? Not in generated SQL. There is a ticket to add hinting support, but currently you'd need to use text() selects or join conditions to get the hints in. http://www.sqlalchemy.org/trac/ticket/921 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Problems with Oracle Express/sqlalchemy
All, Perhaps someone can help me here since I am in that 'bad place' where I am retrying things again and getting deeper than I want into it. I am trying to create a database (using elixir model although the problem appears to be in sqlalchemy so asking here) in Oracle Express. The problem I think is that the table names I am using exist in other schemas. Before I set schema and owner I was getting the ORA-00942 error which I think was that it was finding the table names in other schemas and attempting to use them in the foreign key reference. Now, I am passing both schema and owner explictly for the table (using_table_options(schema='cram',owner='cram')) and I now get: File C:\PYTHON25\lib\site-packages\sqlalchemy-0.4.6-py2.5.egg \sqlalchemy\sche ma.py, line 788, in column foreign key % tname) sqlalchemy.exceptions.NoReferencedTableError: Could not find table 'property_ite m' with which to generate a foreign key Debating if I abandon using sqlalchemy to generate the creation DDL and just use it for mapping or if there is a better solution anyone has. Any help/Best Practices/Incantations in getting Oracle to work right appreciated, Thanks, Brandon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Problems with Oracle Express/sqlalchemy
All,
So have a 'working' solution but think it is pretty bad... Someone
MUST have a better solution. Here is what I had to do:
1. Have create_all explictly not test for tables existing (or else it
finds it in wrong schema)
create_all(checkfirst = False)
2. Explictly set the schema and owner
options_defaults['table_options'] = {'schema':'cram','owner':'cram'}
3. Modify every Foreign_Key reference and prefix the schema name
created_by = Field(Integer, ForeignKey('cram.app_user.control_number',
name = 'property_profile_created_by_fk', use_alter = False), nullable
= True, index = True) #Link field for link CreatedBy
4. Turn off use_alter in each Foreign Key ref (see above)
5. *** Modify schema.py default Index creation to truncate at 30
chars uniquely
global _i_count
_i_count+=1
Index('ix_%s%d' % (self._label[:22],_i_count), self,
unique=self.unique)
5b. The above will go away in next pass when I set index=False and
manually add the Indexes
Anyone else have a cleaner incantation?
Thanks,
Brandon
On May 23, 3:10 pm, Brandon Goldfedder [EMAIL PROTECTED] wrote:
All,
Perhaps someone can help me here since I am in that 'bad place' where
I am retrying things again and getting deeper than I want into it.
I am trying to create a database (using elixir model although the
problem appears to be in sqlalchemy so asking here) in Oracle
Express.
The problem I think is that the table names I am using exist in other
schemas.
Before I set schema and owner I was getting the ORA-00942 error which
I think was that it was finding the table names in other schemas and
attempting to use them in the foreign key reference.
Now, I am passing both schema and owner explictly for the table
(using_table_options(schema='cram',owner='cram')) and I now get:
File C:\PYTHON25\lib\site-packages\sqlalchemy-0.4.6-py2.5.egg
\sqlalchemy\sche
ma.py, line 788, in column
foreign key % tname)
sqlalchemy.exceptions.NoReferencedTableError: Could not find table
'property_ite
m' with which to generate a foreign key
Debating if I abandon using sqlalchemy to generate the creation DDL
and just use it for mapping or if there is a better solution anyone
has.
Any help/Best Practices/Incantations in getting Oracle to work right
appreciated,
Thanks,
Brandon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---
[sqlalchemy] Counts that depend on joins
Hello, I am doing a A-B join before I do any filtering on a query object constructed from A. I then to filtering based on C. I have a column_property mapped to A that gives me the total number of B. This is executed as a subquery, which means it gives me the total number of B in the database, not the total number of B after being filtered by C. How do I get the count of B having applied the filter on C? Thanks, Justin --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Counts that depend on joins
On May 23, 4:33 pm, Justin Tulloss [EMAIL PROTECTED] wrote: I am doing a A-B join before I do any filtering on a query object Sorry, this should be A-B-C join. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: order of execution with MapperExtension
I traced what is happening in the code. I don't fully understand it but I hope the following will help. The crucial point is that in my after_update() method I create a mapped object and call session.save() using the same session (but different table). This is the sequence of events: - I call session.commit() - session._prepare_impl is called and the if self.autoflush: is entered and flush() is called - my after_update() is called - session commit is called again from within unitofwork.flush() - it calls self.transaction.commit() which calls self._prepare_impl() - In this call to _prepare_impl() the if self.autoflush: is not entered and there is no flushing Here is the stack at this point: self._prepare_impl() self.session.flush() self.uow.flush(self, objects) session.commit() self.transaction.commit() self._prepare_impl() - On May 23, 6:07 pm, Michael Bayer [EMAIL PROTECTED] wrote: On May 23, 2008, at 10:44 AM, Moshe Cohen wrote: Evidently, when the autoflush occurred within the commit(), the database transaction COMMIT itself happened before the call to after_update() . thats not how it works. the steps are: session.commit() session.flush() mapper._save_obj() before_update/before_insert() UPDATE/INSERT after_update/after_insert() engine.commit() The fact is that explicitly calling session.flush() immediately before calling session.commit(), changed the final state of the DB. This means the commit() with autoflush is not equivalent to flush and then commit. it would be helpful if you could provide the evidence you're basing this on. it sounds like your Session is not actually within a transaction when you call flush(), so that the flush() begins and commits its own transaction. If transactional=True, this would be a bug. But then calling session.commit() should raise an error, so not sure how you'd achieve that behavior, and I'm not seeing any codepath that could produce that behavior - when transactional=True, a begin() is issued in all cases before any flush call. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Select results to object help
Howdy,
I'm a newbie to sqlalchemy and am having trouble understanding how to
turn selects into objects.
I've got two tables mapped into objects like this:
nis_accounts_table = Table( ... )
nis_users_table = Table( ... )
class NisAccount:
pass
class NisUser:
pass
mapper(NisUser, nis_users_table, properties = {
'accounts':relation(NisAccount,
primaryjoin=nis_users_table.c.id ==
nis_accounts_table.c.nis_user_id,
backref='user',
lazy=False)
},
order_by = nis_users_table.c.uid
)
mapper(NisAccount, nis_accounts_table)
Then I define some selects and execute them:
s = select([nis_accounts_table, nis_users_table],
from_obj=[nis_accounts_table.join(nis_users_table)]).where(
nis_users_table.c.eid != ''
)
a1 = s.correlate(None).alias()
a2 = s.correlate(None).alias()
s2 = select([a2.c.domain_id, a2.c.nis_user_id,
a2.c.gid, a2.c.gcos, a2.c.shell, a2.c.home,
a2.c.terminated, a2.c.reassigned_uid, a2.c.active
], from_obj=[a1.join(a2, (a1.c.eid == a2.c.eid)
(a1.c.uid != a2.c.uid))], use_labels=True)
s3 = select([nis_users_table.c.eid], group_by = nis_users_table.c.eid,
having = (func.count(nis_users_table.c.uid) 1)
(nis_users_table.c.eid != '')
~(nis_users_table.c.uname.like('%_old')))
results = s2.where(a1.c.eid.in_(s3) (a1.c.domain_id ==
41)).execute().fetchall()
What I'd really like is for results to contain a list of NisAccount
objects.
If I run the select like this:
NisAccount.query().execute(s2.where(a1.c.eid.in_(s3) (a1.c.domain_id
== 41))).fetchall()
I get a traceback:
Traceback (most recent call last):
File console, line 1, in ?
File /Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/util.py, line 1260, in
func_with_warning
return func(*args, **kwargs)
File /Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/orm/query.py, line 1365, in
execute
return self._select_statement(clauseelement, params, **kwargs)
File /Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/orm/query.py, line 1382, in
_select_statement
return list(q)
File /Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/orm/query.py, line 984, in
iterate_instances
process[0](context, row, rows)
File /Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/orm/query.py, line 1541, in
main
extension=context.extension,
only_load_props=context.only_load_props,
refresh_instance=context.refresh_instance
File /Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/orm/mapper.py, line 1380, in
_instance
identitykey = self.identity_key_from_row(row)
File /Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/orm/mapper.py, line 915, in
identity_key_from_row
return (self._identity_class, tuple([row[column] for column in
self.primary_key]), self.entity_name)
File /Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/engine/base.py, line 1339, in
__getitem__
return self.__parent._get_col(self.__row, key)
File /Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/engine/base.py, line 1594, in
_get_col
type_, processor, index = self._key_cache[key]
File /Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/util.py, line 125, in
__getitem__
self[key] = value = self.creator(key)
File /Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/engine/base.py, line 1491, in
lookup_key
raise exceptions.NoSuchColumnError(Could not locate column in row
for column '%s' % (str(key)))
NoSuchColumnError: Could not locate column in row for column
'nis_accounts.id'
At this point on my traversal of the SQLAlchemy learning curve, I'm
not sure how to construct this query in a manner that I can use it to
return NisAccount objects.
Any help and or guidance will be greatly appreciated.
Thanks,
Jeff.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: order of execution with MapperExtension
On May 23, 2008, at 6:54 PM, Moshe C. wrote: I traced what is happening in the code. I don't fully understand it but I hope the following will help. The crucial point is that in my after_update() method I create a mapped object and call session.save() using the same session (but different table). session.save() within a mapper extension's before/after update/insert does nothing for the span of that flush. The full list of objects to be sent to the database is already determined at that point and new objects in the session won't get flushed until the next flush (which in this case is outside of the commit()). if you'd like to affect the flush plan upon flush(), you'd have to implement before_flush() on a SessionExtension. Or just stick to manually calling flush() if that solves the particular ordering issue. This is the sequence of events: - I call session.commit() - session._prepare_impl is called and the if self.autoflush: is entered and flush() is called - my after_update() is called - session commit is called again from within unitofwork.flush() this is normal. the unitofwork has its own transaction, which may or may not be a subtransaction. in this case, its a subtransaction; nothing actually happens. - it calls self.transaction.commit() which calls self._prepare_impl() - In this call to _prepare_impl() the if self.autoflush: is not entered and there is no flushing this is because the flush guards against reentrant calls to itself. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Problems with Oracle Express/sqlalchemy
On May 23, 2008, at 3:10 PM, Brandon Goldfedder wrote: All, Perhaps someone can help me here since I am in that 'bad place' where I am retrying things again and getting deeper than I want into it. I am trying to create a database (using elixir model although the problem appears to be in sqlalchemy so asking here) in Oracle Express. The problem I think is that the table names I am using exist in other schemas. Before I set schema and owner I was getting the ORA-00942 error which I think was that it was finding the table names in other schemas and attempting to use them in the foreign key reference. Now, I am passing both schema and owner explictly for the table (using_table_options(schema='cram',owner='cram')) and I now get: dont use owner, its deprecated. schema is all you need. To continue here, you'd have to show us what your Table definitions look like. It seems like you just arent sending the correct identifier to your ForeignKey constructs (for a table with an explicit schema attribute, they should be: ForeignKey(schemaname.tablename.colname) ). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Counts that depend on joins
On May 23, 2008, at 5:33 PM, Justin Tulloss wrote: Hello, I am doing a A-B join before I do any filtering on a query object constructed from A. I then to filtering based on C. I have a column_property mapped to A that gives me the total number of B. This is executed as a subquery, which means it gives me the total number of B in the database, not the total number of B after being filtered by C. How do I get the count of B having applied the filter on C? if B is a column_property then it's defined as having a fixed value relative to the A which its attached to, just like any other column attached to A. If you want the results of a different subquery, youd have to add it to the query using add_column(), or alternatively linking the Query to a totally custom statement using from_statement(). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Select results to object help
On May 23, 2008, at 7:42 PM, Jeff Putsch wrote:
Howdy,
I'm a newbie to sqlalchemy and am having trouble understanding how to
turn selects into objects.
I've got two tables mapped into objects like this:
nis_accounts_table = Table( ... )
nis_users_table = Table( ... )
class NisAccount:
pass
class NisUser:
pass
mapper(NisUser, nis_users_table, properties = {
'accounts':relation(NisAccount,
primaryjoin=nis_users_table.c.id ==
nis_accounts_table.c.nis_user_id,
backref='user',
lazy=False)
},
order_by = nis_users_table.c.uid
)
mapper(NisAccount, nis_accounts_table)
Then I define some selects and execute them:
s = select([nis_accounts_table, nis_users_table],
from_obj=[nis_accounts_table.join(nis_users_table)]).where(
nis_users_table.c.eid != ''
)
a1 = s.correlate(None).alias()
a2 = s.correlate(None).alias()
s2 = select([a2.c.domain_id, a2.c.nis_user_id,
a2.c.gid, a2.c.gcos, a2.c.shell, a2.c.home,
a2.c.terminated, a2.c.reassigned_uid, a2.c.active
], from_obj=[a1.join(a2, (a1.c.eid == a2.c.eid)
(a1.c.uid != a2.c.uid))], use_labels=True)
s3 = select([nis_users_table.c.eid], group_by = nis_users_table.c.eid,
having = (func.count(nis_users_table.c.uid) 1)
(nis_users_table.c.eid != '')
~(nis_users_table.c.uname.like('%_old')))
results = s2.where(a1.c.eid.in_(s3) (a1.c.domain_id ==
41)).execute().fetchall()
What I'd really like is for results to contain a list of NisAccount
objects.
If I run the select like this:
NisAccount.query().execute(s2.where(a1.c.eid.in_(s3) (a1.c.domain_id
== 41))).fetchall()
execute() is a super ancient method that you should not be using
(that's what all the warnings mean when you call it).Instead, use
Query.from_statement(your statement).all().
The error means that the Query cannot locate one of the necessary
columns for NisAccount in the row. This is fairly easy to occur when
you are overriding Query's own statement compilation (which is what
from_statement() does) and providing it with your own list of
columns. In this case I can see that s2 does not have any column
corresponding to nis_accounts.id in it, so that would be the issue
here.
I would advise trying not to rely upon Query.from_statement() so much
since it is harder to use. In this case you could say something along
the lines of:
query.select_from(a1.join(a2, onclause)).filter(a1.c.eid.in_(s3))
Develop this query iteratively, starting simple with core units of the
desired SQL and working outwards until you get the results you want.
Also I'd recommend giving 0.5 a try which is better suited to this
level of Query shenanigans. With 0.5, you can forego the usage of
select() entirely and use Query to create all the subqueries too. The
tutorial has an example of this (albeit a lot simpler than what you're
trying).
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: order of execution with MapperExtension
OK, thanks very much. My wrong assumption was that saves within the hook functions will make it into the current flush. Preceding the commit() with a manual flush, causes the commit to flush this new saves (that occured in the flush). Makes sense now :-) On May 24, 2:52 am, Michael Bayer [EMAIL PROTECTED] wrote: On May 23, 2008, at 6:54 PM, Moshe C. wrote: I traced what is happening in the code. I don't fully understand it but I hope the following will help. The crucial point is that in my after_update() method I create a mapped object and call session.save() using the same session (but different table). session.save() within a mapper extension's before/after update/insert does nothing for the span of that flush. The full list of objects to be sent to the database is already determined at that point and new objects in the session won't get flushed until the next flush (which in this case is outside of the commit()). if you'd like to affect the flush plan upon flush(), you'd have to implement before_flush() on a SessionExtension. Or just stick to manually calling flush() if that solves the particular ordering issue. This is the sequence of events: - I call session.commit() - session._prepare_impl is called and the if self.autoflush: is entered and flush() is called - my after_update() is called - session commit is called again from within unitofwork.flush() this is normal. the unitofwork has its own transaction, which may or may not be a subtransaction. in this case, its a subtransaction; nothing actually happens. - it calls self.transaction.commit() which calls self._prepare_impl() - In this call to _prepare_impl() the if self.autoflush: is not entered and there is no flushing this is because the flush guards against reentrant calls to itself. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Problems with Oracle Express/sqlalchemy
Michael, Yes - that is what I am doing now : ForeignKey(schemaname.tablename.colname) and I have things working. (see example 3). My problem is these steps seem really ugly and a lot more work than it should be to get things working portably and reliably - thus my question on if this is best practice or not. For example the schemaname I have to change depending on whether I am doing Oracle or other DB (thinking I will build the string up from a variable to minimize the changes) On May 23, 7:55 pm, Michael Bayer [EMAIL PROTECTED] wrote: On May 23, 2008, at 3:10 PM, Brandon Goldfedder wrote: All, Perhaps someone can help me here since I am in that 'bad place' where I am retrying things again and getting deeper than I want into it. I am trying to create a database (using elixir model although the problem appears to be in sqlalchemy so asking here) in Oracle Express. The problem I think is that the table names I am using exist in other schemas. Before I set schema and owner I was getting the ORA-00942 error which I think was that it was finding the table names in other schemas and attempting to use them in the foreign key reference. Now, I am passing both schema and owner explictly for the table (using_table_options(schema='cram',owner='cram')) and I now get: dont use owner, its deprecated. schema is all you need. To continue here, you'd have to show us what your Table definitions look like. It seems like you just arent sending the correct identifier to your ForeignKey constructs (for a table with an explicit schema attribute, they should be: ForeignKey(schemaname.tablename.colname) ). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Select results to object help
On May 23, 2008, at 5:07 PM, Michael Bayer wrote:
Jeff Putsch wrote:
Then I define some selects and execute them:
s = select([nis_accounts_table, nis_users_table],
from_obj=[nis_accounts_table.join(nis_users_table)]).where(
nis_users_table.c.eid != ''
)
a1 = s.correlate(None).alias()
a2 = s.correlate(None).alias()
s2 = select([a2.c.domain_id, a2.c.nis_user_id,
a2.c.gid, a2.c.gcos, a2.c.shell, a2.c.home,
a2.c.terminated, a2.c.reassigned_uid, a2.c.active
], from_obj=[a1.join(a2, (a1.c.eid == a2.c.eid)
(a1.c.uid != a2.c.uid))], use_labels=True)
s3 = select([nis_users_table.c.eid], group_by =
nis_users_table.c.eid,
having = (func.count(nis_users_table.c.uid) 1)
(nis_users_table.c.eid != '')
~(nis_users_table.c.uname.like('%_old')))
results = s2.where(a1.c.eid.in_(s3) (a1.c.domain_id ==
41)).execute().fetchall()
What I'd really like is for results to contain a list of NisAccount
objects.
If I run the select like this:
NisAccount.query().execute(s2.where(a1.c.eid.in_(s3)
(a1.c.domain_id
== 41))).fetchall()
execute() is a super ancient method that you should not be using
(that's what all the warnings mean when you call it).Instead, use
Query.from_statement(your statement).all().
OK will do. If I understand correctly you're telling me to do
NisAccounts.query().from_statement(...).all()
The error means that the Query cannot locate one of the necessary
columns for NisAccount in the row.
That's what I figured.
In this case I can see that s2 does not have any column
corresponding to nis_accounts.id in it, so that would be the issue
here.
I get the same error when I include a2.c.id in s2. I suspect
it's because of the overriding of statement compilation I'm
doing.
I would advise trying not to rely upon Query.from_statement() so much
since it is harder to use. In this case you could say something along
the lines of:
query.select_from(a1.join(a2,
onclause)).filter(a1.c.eid.in_(s3))
OK. Now here's where my confusion and lack of experience with SQLAlchemy
really starts to kick in
Develop this query iteratively, starting simple with core units of the
desired SQL and working outwards until you get the results you want.
If I understand correctly,
I need to start with s (the innermost query) and do something like:
print NisAccount.query().select_from(a1.join(a2, (a1.c.eid ==
a2.c.eid) (a1.c.uid != a2.c.uid))).compile()
That gives me the result I expect.
Now, when I add the filter like this:
print NisAccount.query().select_from(a1.join(a2, (a1.c.eid ==
a2.c.eid) (a1.c.uid != a2.c.uid))).filter(a1.c.eid.in_(s3)
(a1.c.domain_id == 41)).compile()
I get an infinite recursion error.
I assume that I'm on the right track.
Also I'd recommend giving 0.5 a try which is better suited to this
level of Query shenanigans.
Perhaps I've got no choice but to try 0.5 :)
I really appreciate your patience and skill in explaining better
approaches to constructing this type of query using SQLAlchemy.
Thanks,
Jeff.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---
