[sqlalchemy] Re: alchemy-migrate with DeclarativeBase
Hi Torsten, How would you define the Account class? Calling create on my DeclarativeBase Account object was one of the first things I tried, but I keep getting: AttributeError: type object 'Account' has no attribute 'create' Maybe a version difference in sqlalchemy? - Shane On Jun 18, 10:22 am, Torsten Landschoff wrote: > Hi Shane, > > On Thu, 2010-06-17 at 23:09 -0700, Shane wrote: > > def upgrade(): > > try: > > session.begin() # Start transaction, but tables are always > > committed > > (See below) > > > DeclarativeBase.metadata.tables[Account.__tablename__].create(migrate_engine) > > Hmm, that looks overly complicated to me. I am using the create method > on the Table as well to do this, but it works fine without a session > just using a plain connection: > > engine = create_engine(...) > conn = engine.connect() > with conn.begin(): > Account.create(conn) > # for testing... > conn.rollback() > > I did not create an ORM session before updating the tables as the schema > might not match the mapped classes before updating. > > Greetings, Torsten > > -- > DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH > Torsten Landschoff > > Office Dresden > Tel: +49-(0)351-4519587 > Fax: +49-(0)351-4519561 > > mailto:torsten.landsch...@dynamore.dehttp://www.dynamore.de > > Registration court: Mannheim, HRB: 109659, based in Karlsruhe, > Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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] new LIMIT/OFFSET Support for oracle - huge speed penalty
On Jun 23, 2010, at 3:50 AM, Ralph Heinkel wrote: > Hi, > > we are about upgrading our sqlalchemy library from 0.4.8 to something newer > and during this process we have detected that the LIMIT/OFFSET support for > oracle has been changed, from using “ROW NUMBER OVER...” to a wrapped > subquery approach in conjunction with ROWNUM as described in > http://www.sqlalchemy.org/docs/reference/dialects/oracle.html#limit-offset-support > > > Unfortunately this approch is about 10 times slower for large tables which is > mainly related to the fact that the innermost subquery has to sort the entire > table with a plain 'order by'. > Interestingly the > ROW_NUMBER() OVER (ORDER BY some db fields) > is so much more efficient than the normal order by approach. > > I don't know the reasons behind the decision for getting rid of the "row > number over" approach, but could it make sense to reimplement this algorithm > again into the current SA as an optional feature? A full history of this feature is here: http://www.sqlalchemy.org/trac/ticket/536 The rationale is based on the bug described in that ticket, as well as that we preferred to go with an approach that was recommended by a lead engineer at Oracle. The dialect includes an option to add the /*+ FIRST_ROWS(N) */ directive, by specifying the "optimize_limits" keyword to create engine - we originally had that in the query in all cases, until some folks chimed in that we shouldn't make that decision by default.I don't know if that helps your use case. The previous system can be restored using a @compiles directive. I have documented that recipe at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/OracleRowNumberOver . -- 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] new LIMIT/OFFSET Support for oracle - huge speed penalty
On Wed, Jun 23, 2010 at 1:50 AM, Ralph Heinkel wrote: > Hi, > > we are about upgrading our sqlalchemy library from 0.4.8 to something newer > and during this process we have detected that the LIMIT/OFFSET support for > oracle has been changed, from using “ROW NUMBER OVER...” to a wrapped > subquery approach in conjunction with ROWNUM as described in > http://www.sqlalchemy.org/docs/reference/dialects/oracle.html#limit-offset-support > > > Unfortunately this approch is about 10 times slower for large tables which > is mainly related to the fact that the innermost subquery has to sort the > entire table with a plain 'order by'. > Interestingly the > ROW_NUMBER() OVER (ORDER BY some db fields) > is so much more efficient than the normal order by approach. Do you have benchmarks to back that up? In Django, we switched from using row_number to rownum after a contributor convinced me that rownum was faster. See: http://code.djangoproject.com/ticket/9136 Thanks, Ian -- 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] new LIMIT/OFFSET Support for oracle - huge speed penalty
Hi, we are about upgrading our sqlalchemy library from 0.4.8 to something newer and during this process we have detected that the LIMIT/OFFSET support for oracle has been changed, from using “ROW NUMBER OVER...” to a wrapped subquery approach in conjunction with ROWNUM as described in http://www.sqlalchemy.org/docs/reference/dialects/oracle.html#limit-offset-support Unfortunately this approch is about 10 times slower for large tables which is mainly related to the fact that the innermost subquery has to sort the entire table with a plain 'order by'. Interestingly the ROW_NUMBER() OVER (ORDER BY some db fields) is so much more efficient than the normal order by approach. I don't know the reasons behind the decision for getting rid of the "row number over" approach, but could it make sense to reimplement this algorithm again into the current SA as an optional feature? Any help/feedback is very appreciated. Thanks, Ralph -- 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] Querying with case insensitive
Hi Alexander,
On 23/06/2010 12:28, Alexander Zhabotinskiy wrote:
Hello. I'v got an problem
I have a string like 'john' and I need to get results for 'john, JOHN'
etc. How to do that?
I do it like this. force db column and search string to upper and I put
a "%" before and after the search string so it looks anywhere within the
db column.
aFilter = "UPPER(%s) LIKE '%s%s%s'" % ('cb_namesandvar',
'%',
'esslin'.upper(),
'%')
wines = session.query(db.Vcbook).filter(aFilter).all()
for wine in wines:
print wine.cb_namesandvar
On my test db this produces:
Goldwater Esslin, Merlot
Goldwater Esslin, Merlot
Werner
--
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] Querying with case insensitive
On 6/23/2010 5:28 AM, Alexander Zhabotinskiy wrote: Hello. I'v got an problem I have a string like 'john' and I need to get results for 'john, JOHN' etc. How to do that? I think perhaps the ilike() filter operator might be the only way? It might even depend on your back-end. http://www.sqlalchemy.org/docs/ormtutorial.html#common-filter-operators http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html#sqlalchemy.sql.expression.ColumnOperators Lance -- 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: AttributeError: 'NoneType' object has no attribute 'dont_expire_missing'
I figured this one out, I had tried to create a attribute synonym by
doing this:
class alaID(Base):
""" Grouped products """
__tablename__ = "al_ids"
__table_args__ = {'schema':'review', 'useexisting':True}
[...]
generated_name = column_property(select(["master_name"], al_id ==
literal_column("al_id"), generated_names_t))
name = generated_name
[...]
This apparently causes some confusion for the mapper when commiting
data, but it works fine for reading
I guess I should use synonym() instead?
-- Joakim
On 23 Juni, 10:37, Joakim wrote:
> I have started to get this error on some machines I deploy my
> application to, but not on my own machine. As far as I can see it is
> running the same verison of SQLAlchemy (0.5.8) and MySQLdb (1.2.2), so
> I have a hard time understanding the issue. Any Ideas on what could be
> causing it?
>
> Traceback (most recent call last):
> [...]
> session.commit()
> File "/home/joakim/as/lib/python2.6/site-packages/SQLAlchemy-0.5.8-
> py2.6.egg/sqlalchemy/orm/session.py", line 671, in commit
> self.transaction.commit()
> File "/home/joakim/as/lib/python2.6/site-packages/SQLAlchemy-0.5.8-
> py2.6.egg/sqlalchemy/orm/session.py", line 388, in commit
> self._remove_snapshot()
> File "/home/joakim/as/lib/python2.6/site-packages/SQLAlchemy-0.5.8-
> py2.6.egg/sqlalchemy/orm/session.py", line 309, in _remove_snapshot
> _expire_state(s, None, instance_dict=self.session.identity_map)
> File "/home/joakim/as/lib/python2.6/site-packages/SQLAlchemy-0.5.8-
> py2.6.egg/sqlalchemy/orm/state.py", line 233, in expire_attributes
> not impl.dont_expire_missing or \
--
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: order_by on a relationship() ?
forget it, I missed this in the docs :
"Note that when using joined eager loaders with relationships, the
tables used by the eager load’s join are anonymously aliased. You can
only order by these columns if you specify it at the relationship()
level. To control ordering at the query level based on a related table,
you join() to that relationship, then order by it"
On 06/23/2010 11:16, Julien Cigar wrote:
Hello all,
always with the following:
orm.mapper(Human, table.human)
orm.mapper(Content, table.content,
polymorphic_on = table.content.c.content_type_id,
properties = {
'owner' : orm.relationship(
Human,
lazy = 'joined',
innerjoin = True,
)
}
)
Is there a way to order_by on the 'owner' property of the Content mapper
directly without having to join the related class (Human in this case)
again ?
I thought something like :
Content.query.filter(Content.container_id==789).\
order_by(Content.owner.login)
but it doesn't work : AttributeError: Neither 'InstrumentedAttribute'
object nor 'Comparator' object has an attribute 'login'
It works with
Content.query.join(Human).\
filter(Content.container_id==789).\
order_by(Human.login)
but then Human is joined two times, one for the explicit .join() and one
for the 'owner' relationship (... JOIN human ON human.id =
content.owner_id JOIN human AS human_1 ON human_1.id = content.owner_id
...)
Also, if I put lazy = 'select' in place of lazy = 'joined' for the above
relationship() and that I do :
Content.query.join(Human).\
filter(Content.container_id==789).\
order_by(Human.login)
and then if I access the 'owner' property of one of those selected
objects SQLAlchemy issues a SELECT again, .. why ? Is there a way to
tell SQLAlchemy that the .join(Human) of the above query is in fact the
'owner' property of Content .. ?
Thanks,
Julien
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
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] Querying with case insensitive
Hello. I'v got an problem I have a string like 'john' and I need to get results for 'john, JOHN' etc. How to do that? -- 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] order_by on a relationship() ?
Hello all,
always with the following:
orm.mapper(Human, table.human)
orm.mapper(Content, table.content,
polymorphic_on = table.content.c.content_type_id,
properties = {
'owner' : orm.relationship(
Human,
lazy = 'joined',
innerjoin = True,
)
}
)
Is there a way to order_by on the 'owner' property of the Content mapper
directly without having to join the related class (Human in this case)
again ?
I thought something like :
Content.query.filter(Content.container_id==789).\
order_by(Content.owner.login)
but it doesn't work : AttributeError: Neither 'InstrumentedAttribute'
object nor 'Comparator' object has an attribute 'login'
It works with
Content.query.join(Human).\
filter(Content.container_id==789).\
order_by(Human.login)
but then Human is joined two times, one for the explicit .join() and one
for the 'owner' relationship (... JOIN human ON human.id =
content.owner_id JOIN human AS human_1 ON human_1.id = content.owner_id ...)
Also, if I put lazy = 'select' in place of lazy = 'joined' for the above
relationship() and that I do :
Content.query.join(Human).\
filter(Content.container_id==789).\
order_by(Human.login)
and then if I access the 'owner' property of one of those selected
objects SQLAlchemy issues a SELECT again, .. why ? Is there a way to
tell SQLAlchemy that the .join(Human) of the above query is in fact the
'owner' property of Content .. ?
Thanks,
Julien
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
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] AttributeError: 'NoneType' object has no attribute 'dont_expire_missing'
I have started to get this error on some machines I deploy my application to, but not on my own machine. As far as I can see it is running the same verison of SQLAlchemy (0.5.8) and MySQLdb (1.2.2), so I have a hard time understanding the issue. Any Ideas on what could be causing it? Traceback (most recent call last): [...] session.commit() File "/home/joakim/as/lib/python2.6/site-packages/SQLAlchemy-0.5.8- py2.6.egg/sqlalchemy/orm/session.py", line 671, in commit self.transaction.commit() File "/home/joakim/as/lib/python2.6/site-packages/SQLAlchemy-0.5.8- py2.6.egg/sqlalchemy/orm/session.py", line 388, in commit self._remove_snapshot() File "/home/joakim/as/lib/python2.6/site-packages/SQLAlchemy-0.5.8- py2.6.egg/sqlalchemy/orm/session.py", line 309, in _remove_snapshot _expire_state(s, None, instance_dict=self.session.identity_map) File "/home/joakim/as/lib/python2.6/site-packages/SQLAlchemy-0.5.8- py2.6.egg/sqlalchemy/orm/state.py", line 233, in expire_attributes not impl.dont_expire_missing or \ -- 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.
