[sqlalchemy] query - automatic lazy attribute expiration
Hi!
Is it possible to force query to automatically expire instance's lazy
attributes? And I mean instances that were queried and accessed before
the second query?
Example:
mapper(MyObject, my_table, properties={
myattr: relation(ChildObject)
})
following code will lead in only 3 SELECTs (because the attribute will
be in the identity map due to the first access)
... clean identity map ...
obj = sess.query(MyObject).first()
print obj.myattr
obj = sess.query(MyObject).first()
print obj.myattr # this will not do a new SELECT
I don't want to do something like:
objs = sess.query(MyObject).all()
for obj in objs:
sess.expire(obj, myattr)
Is there any query option so solve that or I have to use sess.expire?
Thanks.
David
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Query variable outerjoin()
this is what I'm looking for but it doesn't work in 0.4.8. 0.5 is fine? Michael Bayer napsal(a): do you perhaps mean to say, query(Sortment).outerjoin((Translation, and_(Sortiment.id==Translation.sortiment_id, Translation.langauge=='en'))) ? On Jan 7, 2009, at 4:04 PM, ml wrote: That is a solution but it is not very efficient. It involves a sequential scanning of the result of the join inside the database. Michael Bayer napsal(a): On Jan 7, 2009, at 11:49 AM, ml wrote: Hi! I have 2 tables: sortiment(id, ...) translations(id, id_sortiment, language, text) The query query (Sortiment ).outerjoin (Sortiment.translations).filter(Translation.language=en) will never return sortiment item with missing en translation because the filter (useless outerjoin). I need the Translation.language=en embed into the outerjoin but the language must be variable. This is a general problem of creating variable joins on session queries. Is there any simple way? you'd filter on or_(Translation.language==en, Translation.language==None) --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Query variable outerjoin()
Hi! I have 2 tables: sortiment(id, ...) translations(id, id_sortiment, language, text) The query query(Sortiment).outerjoin(Sortiment.translations).filter(Translation.language=en) will never return sortiment item with missing en translation because the filter (useless outerjoin). I need the Translation.language=en embed into the outerjoin but the language must be variable. This is a general problem of creating variable joins on session queries. Is there any simple way? Thank you. David --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Query variable outerjoin()
That is a solution but it is not very efficient. It involves a sequential scanning of the result of the join inside the database. Michael Bayer napsal(a): On Jan 7, 2009, at 11:49 AM, ml wrote: Hi! I have 2 tables: sortiment(id, ...) translations(id, id_sortiment, language, text) The query query (Sortiment ).outerjoin(Sortiment.translations).filter(Translation.language=en) will never return sortiment item with missing en translation because the filter (useless outerjoin). I need the Translation.language=en embed into the outerjoin but the language must be variable. This is a general problem of creating variable joins on session queries. Is there any simple way? you'd filter on or_(Translation.language==en, Translation.language==None) --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: error when running query.count()
I don't know how TG works. You have to ask TG users.
D
Mohammed Khan napsal(a):
I think turbogears is maintain the mapper config?... how do I get this
information out..
Thanks
mfk
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of ml
Sent: Wednesday, August 27, 2008 2:09 AM
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: error when running query.count()
Hi!
There are more than 1 mapper to an object. Send your mappers configuration.
David
mkhan napsal(a):
Hi, I am trying get a count for a query but it errors out with
following message:
Page handler: bound method Root.index of harvest.controllers.Root
object at 0x018444B0
Traceback (most recent call last):
File c:\python25\lib\site-packages\cherrypy-2.3.0-py2.5.egg\cherrypy
\_cphttptools.py, line 121, in _run
self.main()
File c:\python25\lib\site-packages\cherrypy-2.3.0-py2.5.egg\cherrypy
\_cphttptools.py, line 264, in main
body = page_handler(*virtual_path, **self.params)
File string, line 3, in index
File c:\python25\lib\site-packages\TurboGears-1.0.5-py2.5.egg
\turbogears\controllers.py, line 359, in expose
*args, **kw)
File string, line 5, in run_with_transaction
File c:\python25\lib\site-packages\TurboGears-1.0.5-py2.5.egg
\turbogears\database.py, line 407, in sa_rwt
retval = func(*args, **kw)
File string, line 5, in _expose
File c:\python25\lib\site-packages\TurboGears-1.0.5-py2.5.egg
\turbogears\controllers.py, line 372, in lambda
mapping, fragment, args, kw)))
File c:\python25\lib\site-packages\TurboGears-1.0.5-py2.5.egg
\turbogears\controllers.py, line 401, in _execute_func
output = errorhandling.try_call(func, *args, **kw)
File c:\python25\lib\site-packages\TurboGears-1.0.5-py2.5.egg
\turbogears\errorhandling.py, line 77, in try_call
return func(self, *args, **kw)
File C:\Documents and Settings\mkhan\workspace\TestAutomation
\Reports\Harvest\harvest\controllers.py, line 31, in index
print results2.count()
File c:\python25\lib\site-packages\sqlalchemy-0.5.0beta3-py2.5.egg
\sqlalchemy\orm\query.py, line 1193, in count
return self._col_aggregate(sql.literal_column('1'),
sql.func.count,
nested_cols=list(self._only_mapper_zero().primary_key))
File c:\python25\lib\site-packages\sqlalchemy-0.5.0beta3-py2.5.egg
\sqlalchemy\orm\query.py, line 240, in _only_mapper_zero
raise sa_exc.InvalidRequestError(This operation requires a Query
against a single mapper.)
InvalidRequestError: This operation requires a Query against a single
mapper.
I would appreciate some comments on what i am doing wrong,
thanks,
mfk
--~--~-~--~~~---~--~~
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: error when running query.count()
Hi!
There are more than 1 mapper to an object. Send your mappers configuration.
David
mkhan napsal(a):
Hi, I am trying get a count for a query but it errors out with
following message:
Page handler: bound method Root.index of harvest.controllers.Root
object at 0x018444B0
Traceback (most recent call last):
File c:\python25\lib\site-packages\cherrypy-2.3.0-py2.5.egg\cherrypy
\_cphttptools.py, line 121, in _run
self.main()
File c:\python25\lib\site-packages\cherrypy-2.3.0-py2.5.egg\cherrypy
\_cphttptools.py, line 264, in main
body = page_handler(*virtual_path, **self.params)
File string, line 3, in index
File c:\python25\lib\site-packages\TurboGears-1.0.5-py2.5.egg
\turbogears\controllers.py, line 359, in expose
*args, **kw)
File string, line 5, in run_with_transaction
File c:\python25\lib\site-packages\TurboGears-1.0.5-py2.5.egg
\turbogears\database.py, line 407, in sa_rwt
retval = func(*args, **kw)
File string, line 5, in _expose
File c:\python25\lib\site-packages\TurboGears-1.0.5-py2.5.egg
\turbogears\controllers.py, line 372, in lambda
mapping, fragment, args, kw)))
File c:\python25\lib\site-packages\TurboGears-1.0.5-py2.5.egg
\turbogears\controllers.py, line 401, in _execute_func
output = errorhandling.try_call(func, *args, **kw)
File c:\python25\lib\site-packages\TurboGears-1.0.5-py2.5.egg
\turbogears\errorhandling.py, line 77, in try_call
return func(self, *args, **kw)
File C:\Documents and Settings\mkhan\workspace\TestAutomation
\Reports\Harvest\harvest\controllers.py, line 31, in index
print results2.count()
File c:\python25\lib\site-packages\sqlalchemy-0.5.0beta3-py2.5.egg
\sqlalchemy\orm\query.py, line 1193, in count
return self._col_aggregate(sql.literal_column('1'),
sql.func.count,
nested_cols=list(self._only_mapper_zero().primary_key))
File c:\python25\lib\site-packages\sqlalchemy-0.5.0beta3-py2.5.egg
\sqlalchemy\orm\query.py, line 240, in _only_mapper_zero
raise sa_exc.InvalidRequestError(This operation requires a Query
against a single mapper.)
InvalidRequestError: This operation requires a Query against a single
mapper.
I would appreciate some comments on what i am doing wrong,
thanks,
mfk
--~--~-~--~~~---~--~~
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: disable polymorphic load
thats a bug which was fixed post 0.4.7p1. Its in trunk and is for 0.4.8. Great! And back to the mapper properties. As I found it always do a polymorphic_fetch=select-like query when dealing with mapper properties referencing a polymorphic base. This is not very efficient. I would prefer an union fetch. I realized that it is similar to query().get(). Example follows: session.query(Base).with_polymorphic(*).filter(Base.id==3).first().x results in 1 joined select: SELECT base.id AS base_id, base.id_parent AS base_id_parent, base.kind AS base_kind, derived.id AS derived_id, derived.x AS derived_x, derived.id_other AS derived_id_other FROM base LEFT OUTER JOIN derived ON derived.id = base.id WHERE base.id = ? ORDER BY base.oid LIMIT 1 OFFSET 0 but session.query(Base).with_polymorphic(*).get(3).x results in 2 selects: SELECT base.id AS base_id, base.id_parent AS base_id_parent, base.kind AS base_kind FROM base WHERE base.id = ? SELECT derived.x AS derived_x, derived.id_other AS derived_id_other FROM base JOIN derived ON derived.id = base.id WHERE base.id = ? Both queries gives the same object but the get() generates 2 selects (like the mapper property fetch). Why? --~--~-~--~~~---~--~~ 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] disable polymorphis load
Hi! How can I disable a polymorphic load for a single query? I found a hint in http://markmail.org/message/2kwbm377j3pdvvqb but I can't find more. Thanks for any advice. David --~--~-~--~~~---~--~~ 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: disable polymorphis load
This is not working: Setup: base = Table(base, metadata, Column(id, Integer, primary_key=True), Column(kind, Integer), ) derived = Table(derived, metadata, Column(id, Integer, ForeignKey(base.id), primary_key=True) ) class Base(object): pass class Derived(Base): pass mapper(Base, base, polymorphic_on=base.c.kind, polymorphic_identity=0, ) mapper(Derived, derived, inherits=Base, polymorphic_identity=1, ) and this call: session.query(Base).with_polymorphic(Base).first() will still result in 2 SELECTs and the returned object is Derived (tables are filled only with Derived objects) SELECT base.id AS base_id, base.kind AS base_kind FROM base ORDER BY base.id LIMIT 1 OFFSET 0 SELECT derived.id AS derived_id FROM derived WHERE %(param_1)s = derived.id __main__.Derived object at 0xce1b10 SQLAlchemy version 0.4.7p1 I need only 1 SELECT and Base object returned. Michael Bayer napsal(a): call with_polymorphic passing in only the base class: query.with_polymorphic(BaseClass).filter() On Aug 25, 2008, at 10:35 AM, ml wrote: Hi! How can I disable a polymorphic load for a single query? I found a hint in http://markmail.org/message/2kwbm377j3pdvvqb but I can't find more. Thanks for any advice. David --~--~-~--~~~---~--~~ 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: disable polymorphis load
I'm heavily using the polymorphic features but I need a stable library so I will stick with 0.4 for now. Thank you! David Michael Bayer napsal(a): oh, that. OK, in the 0.4 series you'd want to set polymorphic_fetch='deferred' on your mapper. Just leave it that way, as the non-deferred behavior has been removed from 0.5 anyway (its deferred in all cases). The second table will be fetched as needed. if you're depending heavily on inheritance I strongly recommend looking at 0.5, this is one area where a significant amount of work has been done. On Aug 25, 2008, at 11:24 AM, ml wrote: This is not working: Setup: base = Table(base, metadata, Column(id, Integer, primary_key=True), Column(kind, Integer), ) derived = Table(derived, metadata, Column(id, Integer, ForeignKey(base.id), primary_key=True) ) class Base(object): pass class Derived(Base): pass mapper(Base, base, polymorphic_on=base.c.kind, polymorphic_identity=0, ) mapper(Derived, derived, inherits=Base, polymorphic_identity=1, ) and this call: session.query(Base).with_polymorphic(Base).first() will still result in 2 SELECTs and the returned object is Derived (tables are filled only with Derived objects) SELECT base.id AS base_id, base.kind AS base_kind FROM base ORDER BY base.id LIMIT 1 OFFSET 0 SELECT derived.id AS derived_id FROM derived WHERE %(param_1)s = derived.id __main__.Derived object at 0xce1b10 SQLAlchemy version 0.4.7p1 I need only 1 SELECT and Base object returned. Michael Bayer napsal(a): call with_polymorphic passing in only the base class: query.with_polymorphic(BaseClass).filter() On Aug 25, 2008, at 10:35 AM, ml wrote: Hi! How can I disable a polymorphic load for a single query? I found a hint in http://markmail.org/message/2kwbm377j3pdvvqb but I can't find more. Thanks for any advice. David --~--~-~--~~~---~--~~ 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: disable polymorphic load
One more thing :-) Now I have polymorphic_fetch=deferred and in some needed cases I call query.with_polymorphic(*). All works perfectly. But can I setup something like with_polymorphic(*) on a mapper property? Because now all relations to Base are polymorphic-deferred and it does me some troubles. Michael Bayer napsal(a): oh, that. OK, in the 0.4 series you'd want to set polymorphic_fetch='deferred' on your mapper. Just leave it that way, as the non-deferred behavior has been removed from 0.5 anyway (its deferred in all cases). The second table will be fetched as needed. if you're depending heavily on inheritance I strongly recommend looking at 0.5, this is one area where a significant amount of work has been done. On Aug 25, 2008, at 11:24 AM, ml wrote: This is not working: Setup: base = Table(base, metadata, Column(id, Integer, primary_key=True), Column(kind, Integer), ) derived = Table(derived, metadata, Column(id, Integer, ForeignKey(base.id), primary_key=True) ) class Base(object): pass class Derived(Base): pass mapper(Base, base, polymorphic_on=base.c.kind, polymorphic_identity=0, ) mapper(Derived, derived, inherits=Base, polymorphic_identity=1, ) and this call: session.query(Base).with_polymorphic(Base).first() will still result in 2 SELECTs and the returned object is Derived (tables are filled only with Derived objects) SELECT base.id AS base_id, base.kind AS base_kind FROM base ORDER BY base.id LIMIT 1 OFFSET 0 SELECT derived.id AS derived_id FROM derived WHERE %(param_1)s = derived.id __main__.Derived object at 0xce1b10 SQLAlchemy version 0.4.7p1 I need only 1 SELECT and Base object returned. Michael Bayer napsal(a): call with_polymorphic passing in only the base class: query.with_polymorphic(BaseClass).filter() On Aug 25, 2008, at 10:35 AM, ml wrote: Hi! How can I disable a polymorphic load for a single query? I found a hint in http://markmail.org/message/2kwbm377j3pdvvqb but I can't find more. Thanks for any advice. David --~--~-~--~~~---~--~~ 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: disable polymorphic load
I created a stripped version of my application's model and I found the
problem. The problem is not in the polymorphism but int the inheritance
condition. I have cycles in my model so I had to use the
inherit_condition. I found that it is a big difference how I choose the
expression sides
inherit_condition=derived.c.id==base.c.id
or
inherit_condition=base.c.id==derived.c.id
But why? Is my inherit_condition incorrect at all?
The example code is attached.
Michael Bayer napsal(a):
On Aug 25, 2008, at 1:05 PM, ml wrote:
One more thing :-)
Now I have polymorphic_fetch=deferred and in some needed cases I
call
query.with_polymorphic(*). All works perfectly. But can I setup
something like with_polymorphic(*) on a mapper property? Because now
all relations to Base are polymorphic-deferred and it does me some
troubles.
not really. What kind of problems ?
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
#!/usr/bin/python
# -*- coding: utf-8 -*-
from sqlalchemy import *
from sqlalchemy.orm import *
#engine = create_engine(postgres://localhost/test, echo=True)
engine = create_engine(sqlite://, echo=True)
metadata = MetaData()
base = Table(base, metadata,
Column(id, Integer, primary_key=True),
Column(id_parent, Integer, ForeignKey(base.id)),
Column(kind, Integer),
)
derived = Table(derived, metadata,
Column(id, Integer, ForeignKey(base.id), primary_key=True),
Column(x, Integer),
Column(id_other, Integer),
ForeignKeyConstraint([id_other], [base.id],
other_fg, use_alter=True),
)
class Base(object):
def __init__(self, id, parent):
self.id = id
self.parent = parent
class Derived(Base):
def __init__(self, id, parent):
Base.__init__(self, id, parent)
self.x = id
mapper(Base, base,
polymorphic_on=base.c.kind,
polymorphic_identity=0,
polymorphic_fetch=deferred,
properties = {
children: relation(Base, cascade=all,
backref=backref(parent, remote_side=[base.c.id])),
}
)
mapper(Derived, derived,
inherits=Base,
#inherit_condition=base.c.id==derived.c.id,
inherit_condition=derived.c.id==base.c.id,
polymorphic_identity=1,
properties = {
other: relation(Base, cascade=all, post_update=True,
primaryjoin=derived.c.id_other==base.c.id)
}
)
metadata.drop_all(engine)
metadata.create_all(engine)
Session = sessionmaker(bind=engine, autoflush=False, transactional=False)
session = Session()
#
# test data - chain 1-2-3
session.save(Derived(3, Derived(2, Derived(1, None
session.save(Derived(6, Derived(5, Derived(4, None
session.flush()
session.clear()
#
q = session.query(Base).with_polymorphic(*).get(3)
assert q.parent.parent.x == 1
q = session.query(Base).with_polymorphic(*).get(6)
assert q.parent.parent.x == 4, q.parent.parent.x
[sqlalchemy] Re: Query to get all items from a table which have no reference to another table?
Hi! Or something like s.query(A).filter(not_(A.column.in_(select(table_b.c.column)) David Empty napsal(a): Hello Felix, No I want to get all items from table A where no matching item in table B exists. I'm aware that this would be very easy if table A stores the foreign key. SQLAlchemy supports the NOT EXISTS syntax through the any() / has() methods. You should be able to do something like this: session.query(A).filter(~A.b.any()) See http://www.sqlalchemy.org/docs/05/ormtutorial.html#datamapping_joins_using for more information. Michael Trier blog.michaeltrier.com --~--~-~--~~~---~--~~ 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] embedding aggregates into query or mapper
Hi! Let's have a typical schema: Company - Employee How can I add a scalar column (in a query or a mapper) to the Company containing the employees count? E.g. query(Company).all() will return list of Company instances and each will have an extra property with emloyees count. But I don't want that retrieved by an extra query for each company. I want that that in one query like SELECT companies.*, count(employees.*) David --~--~-~--~~~---~--~~ 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] inverted in_
Hi!
How can I invert the in_ operator? I need a select like:
... name NOT IN ('alice', 'bob')...
...filter(not User.name.in_('alice', 'bob'))... is not working.
Thanks for advices.
David
--~--~-~--~~~---~--~~
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] polymorphism without extra table
Hi! I have following situation: I have 3 tables which stand as a base for other stuff: table_virtual_categories = Table(virtual_categories, meta, Column(id, Integer, primary_key = True), Column(id_parent, Integer, ForeignKey(virtual_categories.id)), Column(visible, Boolean, nullable=False, default=False), Column(kind, String(10), nullable=False), ) table_virtual_items = Table(virtual_items, meta, Column(id, Integer, primary_key = True), Column(id_category, Integer, ForeignKey(virtual_categories.id), nullable=False), Column(kind, String(10), nullable=False), ) table_virtual_proposals = Table(virtual_proposals, meta, Column(id, Integer, primary_key = True), Column(id_previous_version, Integer, ForeignKey(virtual_proposals.id)), Column(id_item, Integer, ForeignKey(virtual_items.id), nullable=False), Column(title, Unicode(100)), Column(dt_inserted, DateTime, nullable=False, default=func.now()), Column(dt_valid_from, DateTime), Column(dt_valid_to, DateTime), Column(version, LUnicode(100), nullable=False), Column(state, Integer, nullable=False) ) Now I have some situations where I need to inherit some of these tables but I want to keep the schema category-item-proposal: E.g. articles: I need special columns for categories and proposals but articles (~items) have no extra columns: table_articles_categories = Table(articles_categories, meta, Column(id, Integer, ForeignKey(virtual_categories), primary_key = True), Column(description, UnicodeText), ) table_articles = Table(articles, meta, Column(id, Integer, ForeignKey(virtual_items), primary_key = True) ) table_article_proposals = Table(article_proposals, meta, Column(id, Integer, ForeignKey(virtual_proposals), primary_key = True), Column(body, UnicodeText), ) So as you can see the table articles is quite redundant because it has only the primary key column but I didn't find any other way to select articles directly from the table virtual_items according to the column kind without joining to another specialized table. Is there any way around this to omit the articles table? Thanks for advices. David --~--~-~--~~~---~--~~ 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: polymorphism without extra table
that's it! great, thanks! Michael Bayer napsal(a): On Jun 10, 2008, at 8:34 AM, ml wrote: So as you can see the table articles is quite redundant because it has only the primary key column but I didn't find any other way to select articles directly from the table virtual_items according to the column kind without joining to another specialized table. Is there any way around this to omit the articles table? dont put any table in the Article mapper. It will inherit from the base using single table inheritance. --~--~-~--~~~---~--~~ 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: Sqlalchemy and py2exe...
Hi! Just put somewhere an explicit import sqlite. It should be enough. David pdamoc napsal(a): Hello, I've tried packing an app I've made with py2exe and run into trouble... first it was an EGG issue, I've fixed that installing everything as a directory next came the sqlite issue... I simply cannot get it to work after I build the .exe I keep getting: Traceback (most recent call last): File patch.py, line 53, in module File model.pyc, line 87, in __init__ File model.pyc, line 56, in createDB File sqlalchemy\schema.pyc, line 1190, in __init__ File sqlalchemy\schema.pyc, line 1095, in __init__ File sqlalchemy\schema.pyc, line 1114, in connect File sqlalchemy\engine\__init__.pyc, line 154, in create_engine File sqlalchemy\engine\strategies.pyc, line 44, in create File sqlalchemy\engine\url.pyc, line 92, in get_dialect ImportError: unknown database 'sqlite' The app works fine when I start it as a script. any idea is welcomed! Thank you in advance. Peter. --~--~-~--~~~---~--~~ 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: Could not assemble any primary key columns for mapped table
That's it, thanks! Michael Bayer napsal(a): On May 4, 2007, at 9:17 AM, ml wrote: Hi! I have a few tables with non-integer primary keys. Following raises an exception: meta = MetaData(name=somemeta) themes = table(themes, meta, Column(title, Unicode(30), primary_key=True), ) class Theme(object): pass mapper(Theme, themes).compile() youre using the wrong table constructor. try Table(). --~--~-~--~~~---~--~~ 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: multiple M:N joins fails
Michael Bayer napsal(a): On May 1, 2007, at 4:42 AM, ml wrote: I want to get recipes which belongs to a particular category and having a particular flag. So I need both joins recipe-category and recipe- flag. ah. in that case you dont want query.join(x).join(y), you want the second join to still be relative to the original query. i think youre going to have to spell that one out explicitly for now. I don't understand what you mean by spell that one out explicitly. I need something like (recipes JOIN categories) INTERSECTION (recipes JOIN flags) or ((recipes JOIN categories) JOIN flags) generated by the ORM. --~--~-~--~~~---~--~~ 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: multiple M:N joins fails
I want to get recipes which belongs to a particular category and having a particular flag. So I need both joins recipe-category and recipe-flag. Michael Bayer napsal(a): On Apr 30, 2007, at 8:40 AM, ml wrote: Hi! I have 2 relations: - recipes-categories (M:N) - recipes-flags (M:N) I'd like to get something like: SELECT recipes.title FROM recipes JOIN _recipes_ctgs_recipes ON _recipes_ctgs_recipes.id_recipe = recipes.id JOIN recipes_ctgs ON _recipes_ctgs_recipes.id_recipes_ctg=recipes_ctgs.id JOIN _recipes_flgs_recipes ON _recipes_flgs_recipes.id_recipe = recipes.id JOIN recipes_flgs ON _recipes_flgs_recipes.id_recipes_flg=recipes_flgs.id WHERE recipes_ctgs.title='cat1' AND recipes_flgs.title='flag1' when I run sess.query(Recipe).join(ctgs).join(flgs).select(...) it fails with sqlalchemy.exceptions.SQLError: (ProgrammingError) table name _recipes_ctgs_recipes specified more than once where _recipes_ctgs_recipes is a secondary table. Full example attached. well, yeah, youre joining against the same relationship twice. going from ctgs to flgs makes it essentially a self referential join on recipes. i dont understand what youre trying to query for there but my intuition tells me theres probably some better way to lay out that query without 5 joins in between. if not, youll have to lay out the self referential part manually using table aliases. (note to SA old schoolers - see why i hesitated so much to add auto- joins across relationships ? every new feature spawns a whole new class of user issues) --~--~-~--~~~---~--~~ 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: SQLAlchemy 0.3.7 released
Excellent! Michael Bayer napsal(a): 0.3.7 is out and has a huge number of improvements and fixes. some of the highlights: - server side cursor support for Postgres - much improved auto-reconnect support - informix support - long identifier name support - support for unicode table/column/identifier names and SQL statements - deterministic label name generation - new query features, like with_parent() - improvements to custom collection mapping - lots more docstrings - major refactoring of sqlalchemy.engine internals, featuring clearer structural relationships, fewer codepaths, better result handling --~--~-~--~~~---~--~~ 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] multiple M:N joins fails
Hi! I have 2 relations: - recipes-categories (M:N) - recipes-flags (M:N) I'd like to get something like: SELECT recipes.title FROM recipes JOIN _recipes_ctgs_recipes ON _recipes_ctgs_recipes.id_recipe = recipes.id JOIN recipes_ctgs ON _recipes_ctgs_recipes.id_recipes_ctg=recipes_ctgs.id JOIN _recipes_flgs_recipes ON _recipes_flgs_recipes.id_recipe = recipes.id JOIN recipes_flgs ON _recipes_flgs_recipes.id_recipes_flg=recipes_flgs.id WHERE recipes_ctgs.title='cat1' AND recipes_flgs.title='flag1' when I run sess.query(Recipe).join(ctgs).join(flgs).select(...) it fails with sqlalchemy.exceptions.SQLError: (ProgrammingError) table name _recipes_ctgs_recipes specified more than once where _recipes_ctgs_recipes is a secondary table. Full example attached. SA 0.3.7, PostgreSQL 8.1 David --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- test.tgz Description: GNU Unix tar archive
[sqlalchemy] Re: distinct doesn't work with join
Thanks! How stable is this revision? Is it suited for production? Michael Bayer napsal(a): OK r2579 --~--~-~--~~~---~--~~ 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: apache, engine and metadata
2) the number of connections to the db growing ... is implemented well the MetaData() class ? I'm using postgresql schema's and I want to mantain a single connection for every db user. Hi! I had the same problem because I was calling create_engine in every page request (apache + mod_python). Then I changed it to something like: g_engine = None def conn(): global g_engine if not g_engine: g_engine = create_engine() return g_engine The module is loaded only once per Apache process. I hope I directed you the right way :-) David --~--~-~--~~~---~--~~ 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_by
e.g. order_by = [desc(table1.mycol)] Disrupt07 napsal(a): In my table I have a column with type Boolean. When using order_by on this column I am getting the results as follows: False False True True True ... True I want them the other way round (the True first, then the False). How can I change the order? Thanks --~--~-~--~~~---~--~~ 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: selecting from M:N via secondary table's column
How can I select user's addresses when I know only his id? And I don't want to select the user first. session.query(Address).join(user).select(User.c.id==the user id) I was afraid of that :-) I hoped it can go in a cleaner way like join(Address.c.user) but giving the property as a string. --~--~-~--~~~---~--~~ 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] selecting from a relation
Hi!
Lets have:
##
users_table = Table(users, metadata,
Column(id, Integer, primary_key=True),
Column(user_name, String(16))
)
addresses_table = Table(addresses, metadata,
Column(id, Integer, primary_key=True),
Column(id_user, Integer, ForeignKey(users.id)),
Column(addr, String(100))
)
class User(object): pass
class Address(object): pass
mapper(Address, addresses_table)
mapper(User, users_table, properties = {
addresses : relation(Address, cascade=all, delete-orphan,
backref=backref(user)),
}
)
##
I have a user:
user = session.query(User).get_by_user_name(bob)
and I want some of his addresses using some criterion (e.g. all
beginning on b) so I can't use user.addresses.
I know I can do s.query(Address).select_by(id_user=u.id) but that is not
very clean.
I tried s.query(Address).select_by(user=u) as I found similar in the
documentation
(http://www.sqlalchemy.org/docs/datamapping.html#datamapping_selectrelations_relselectby)
but SA raises:
AttributeError: 'LazyLoader' object has no attribute 'columns'
Any suggestion?
Thanks.
David
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to [EMAIL PROTECTED]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: selecting from a relation
See attachment. Tested against 0.3.6.
Michael Bayer napsal(a):
On Apr 12, 2007, at 12:53 PM, ml wrote:
I tried s.query(Address).select_by(user=u) as I found similar in the
documentation
(http://www.sqlalchemy.org/docs/
datamapping.html#datamapping_selectrelations_relselectby)
but SA raises:
AttributeError: 'LazyLoader' object has no attribute 'columns'
works for me, cant reproduce. please attach a full reproducing test
case.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to [EMAIL PROTECTED]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
#!/usr/bin/python2.4
# -*- coding: utf-8 -*-
from sqlalchemy3 import *
import datetime, pickle, sys
engine = create_engine(sqlite://, echo=True)
metadata = BoundMetaData(engine)
users_table = Table(users, metadata,
Column(id, Integer, primary_key=True),
Column(user_name, String(16))
)
addresses_table = Table(addresses, metadata,
Column(id, Integer, primary_key=True),
Column(id_user, Integer, ForeignKey(users.id)),
Column(addr, String(100))
)
class User(object):
def __init__(self, user_name):
self.user_name = user_name
class Address(object):
def __init__(self, addr):
self.addr = addr
mapper(Address, addresses_table)
mapper(User, users_table, properties = {
addresses : relation(Address, cascade=all, delete-orphan,
backref=backref(user)),
}
)
metadata.create_all(engine)
s = create_session(bind_to=engine)
u1 = User(bob)
a1 = Address(bob's house)
a2 = Address(bob's flat)
u1.addresses.append(a1)
u1.addresses.append(a2)
s.save(u1)
u2 = User(alice)
a3 = Address(alice's house)
a4 = Address(alice's flat)
u2.addresses.append(a3)
u2.addresses.append(a4)
s.save(u2)
s.flush()
u = s.query(User).get_by_user_name(bob)
for i in s.query(Address).select_by(user=u):
print i.addr
[sqlalchemy] Re: selecting from a relation
I'm such a moron. I downloaded the SA source into a sqlalchemy3 directory but the SA expects it in a sqlalchemy package so it was internaly loading the old 0.2.8 Ubuntu version. Sorry! :-) Michael Bayer napsal(a): On Apr 12, 2007, at 12:53 PM, ml wrote: I tried s.query(Address).select_by(user=u) as I found similar in the documentation (http://www.sqlalchemy.org/docs/ datamapping.html#datamapping_selectrelations_relselectby) but SA raises: AttributeError: 'LazyLoader' object has no attribute 'columns' works for me, cant reproduce. please attach a full reproducing test case. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to [EMAIL PROTECTED] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] strange reference (appending to children)
Hi!
I found a quite strange behavior. Reproduction is simple.
Setup:
selfref=Table(selfref, metadata,
Column(id,Integer, primary_key=True),
Column(id_par,Integer, ForeignKey(selfref.id))
)
class SelfRef(object):pass
mapper(SelfRef, selfref, properties={
children:relation(SelfRef, cascade=all)
})
Following construction is fine:
sr1 = SelfRef()
sr1.children.append(SelfRef())
but
sr1 = SelfRef().children.append(SelfRef())
will raise
Traceback (most recent call last):
File ./selfref.py, line 22, in ?
sr1 = SelfRef().children.append(SelfRef())
File /usr/lib/python2.4/site-packages/sqlalchemy/attributes.py, line
386, in append
self.__setrecord(item)
File /usr/lib/python2.4/site-packages/sqlalchemy/attributes.py, line
362, in __setrecord
self.attr.append_event(event, self.obj, item)
File /usr/lib/python2.4/site-packages/sqlalchemy/attributes.py, line
261, in append_event
obj._state['modified'] = True
AttributeError: 'NoneType' object has no attribute '_state'
and that is a little bit odd because both constructions are logically
identical, right?
Tested SA versions: 0.2.8, 0.3.6
David
--~--~-~--~~~---~--~~
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] print SQL only
Hi! I want meta.create_all() to generate and print the SQL only and don't query the DB. Is there any way? Thanks for advices. David --~--~-~--~~~---~--~~ 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] constants in strings
Hi! Just for curiosity: why sqlalchemy is using constants in strings? E.g.: cascade = all, delete-orphan Why not something like: cascade = ALL | DELETE_ORPHAN I'm asking because I realized that when I make a mistake in the string the sqlalchemy doesn't report anything and my program crashes later and I never see the connection to the typing error. Another thing is that putting constants/directives in a string separated by a comma is not very clean in this case. David --~--~-~--~~~---~--~~ 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: SQLAlchemy and py2exe problem
Hi! I had similar problem with module postgres. py2exe didn't automaticaly include the module so I simply put import sqlalchemy.databases.postgres into the main script. Perhaps you should try something like this with the logging. DS Karlo Lozovina píše v Út 19. 12. 2006 v 04:01 +: Hi all, I've posted this few minutes ago on c.l.python, but since I'm desperate, I'll do it here to: I'm working with SQLAlchemy 0.3.3, Python 2.5 and py2exe 0.6.5. This simple scripts fails when I run test.exe: *** test.py: *** import sqlalchemy print 'Test' *** It works when interpreted by Python, but when running it from compiled py2exe binary, it fails with this error: Traceback (most recent call last): File main.py, line 1, in module File sqlalchemy\__init__.pyc, line 10, in module File sqlalchemy\orm\__init__.pyc, line 12, in module File sqlalchemy\orm\mapper.pyc, line 7, in module File sqlalchemy\logging.pyc, line 30, in module ImportError: No module named logging Ofcourse, library.zip (in the dist directory) contains 'sqlalchemy \logging.pyc'. After I copy logging.pyc to library.zips' root, I get this error: Traceback (most recent call last): File main.py, line 1, in module File sqlalchemy\__init__.pyc, line 10, in module File sqlalchemy\orm\__init__.pyc, line 12, in module File sqlalchemy\orm\mapper.pyc, line 7, in module File sqlalchemy\logging.pyc, line 30, in module File sqlalchemy\logging.pyc, line 33, in module AttributeError: 'module' object has no attribute 'getLogger' I really don't know what to do next, so any kind of help is appreciated. First of all, I'm wondering is this SQLAlchemys' problem, or is py2exe guilty? Thanks guys... --~--~-~--~~~---~--~~ 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] value too long for type character varying
Hi! Can I force SA (0.2.8) to ignore the sqlalchemy.exceptions.SQLError: (ProgrammingError) value too long for type character varying... and silently truncate the inserted data? Thank you! David --~--~-~--~~~---~--~~ 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: SA ORM delete efficiency
This is a duplicate mail (due to a recent SMTP problems). Don't reply.
DS
ml píše v Po 11. 12. 2006 v 23:13 +0100:
Hi!
I have a little questions about how does the SA work:
Example:
I borrowed a model from SA documentation example User/Address. My mapper
is
mapper(Address, addresses_table)
mapper(User, users_table, properties = {
'addresses' : relation(Address, cascade=all, delete-orphan)
}
)
My example code:
u = User(a, b)
u.addresses.append(Address(1,2,3,4))
u.addresses.append(Address(1,2,3,4))
session.save(u)
session.flush()
session.clear()
print **
u = session.query(User).get_by_user_name(a)
session.delete(u)
session.flush()
Output:
---
BEGIN
INSERT INTO users (user_name, password) VALUES (?, ?)
['a', 'b']
INSERT INTO addresses (user_id, street, city, state, zip) VALUES
(?, ?, ?, ?, ?)
[1, '1', '2', '3', '4']
INSERT INTO addresses (user_id, street, city, state, zip) VALUES
(?, ?, ?, ?, ?)
[1, '1', '2', '3', '4']
COMMIT
**
SELECT users.user_name AS users_user_name, users.password AS
users_password, users.user_id AS users_user_id
FROM users
WHERE users.user_name = ? ORDER BY users.oid
LIMIT 1 OFFSET 0
['a']
BEGIN
SELECT addresses.city AS addresses_city, addresses.address_id AS
addresses_address_id, addresses.user_id AS addresses_user_id,
addresses.zip AS addresses_zip, addresses.state AS addresses_state,
addresses.street AS addresses_street
FROM addresses
WHERE ? = addresses.user_id ORDER BY addresses.oid
[1]
DELETE FROM addresses WHERE addresses.address_id = ?
[[1], [2]]
DELETE FROM users WHERE users.user_id = ?
[1]
COMMIT
Question about deleting:
1) Why does the SA the second SELECT for addresses to obtain primary
keys? Why there is not a direct DELETE FROM addresses WHERE user_id=??
2) If it is neccessary to do this SELECT, why is the SA selecting all
columns? E.g. I will have 1 user with 10 addresses - an idea of
selecting all 10 addresses to get their id's is spooky :-)
Why is it so or em I missing something?
Thank you.
David
--~--~-~--~~~---~--~~
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] SA ORM delete efficiency
Hi!
I have a little questions about how does the SA work:
Example:
I borrowed a model from SA documentation example User/Address. My mapper
is
mapper(Address, addresses_table)
mapper(User, users_table, properties = {
'addresses' : relation(Address, cascade=all, delete-orphan)
}
)
My example code:
u = User(a, b)
u.addresses.append(Address(1,2,3,4))
u.addresses.append(Address(1,2,3,4))
session.save(u)
session.flush()
session.clear()
print **
u = session.query(User).get_by_user_name(a)
session.delete(u)
session.flush()
Output:
---
BEGIN
INSERT INTO users (user_name, password) VALUES (?, ?)
['a', 'b']
INSERT INTO addresses (user_id, street, city, state, zip) VALUES
(?, ?, ?, ?, ?)
[1, '1', '2', '3', '4']
INSERT INTO addresses (user_id, street, city, state, zip) VALUES
(?, ?, ?, ?, ?)
[1, '1', '2', '3', '4']
COMMIT
**
SELECT users.user_name AS users_user_name, users.password AS
users_password, users.user_id AS users_user_id
FROM users
WHERE users.user_name = ? ORDER BY users.oid
LIMIT 1 OFFSET 0
['a']
BEGIN
SELECT addresses.city AS addresses_city, addresses.address_id AS
addresses_address_id, addresses.user_id AS addresses_user_id,
addresses.zip AS addresses_zip, addresses.state AS addresses_state,
addresses.street AS addresses_street
FROM addresses
WHERE ? = addresses.user_id ORDER BY addresses.oid
[1]
DELETE FROM addresses WHERE addresses.address_id = ?
[[1], [2]]
DELETE FROM users WHERE users.user_id = ?
[1]
COMMIT
Question about deleting:
1) Why does the SA the second SELECT for addresses to obtain primary
keys? Why there is not a direct DELETE FROM addresses WHERE user_id=??
2) If it is neccessary to do this SELECT, why is the SA selecting all
columns? E.g. I will have 1 user with 10 addresses - an idea of
selecting all 10 addresses to get their id's is spooky :-)
Why is it so or em I missing something?
Thank you.
David
--~--~-~--~~~---~--~~
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: remote nondirect access to DB
Client application doesn't mean web application and SqlAlchemy would be a too big hammer for SQL console (in my opinion). Well it will be an information system written with wxPython running on multiple computers. I don't want users to see information for direct access to the database. I was trying to expose DBAPI (connections, cursors) via Pyro but it is falling. Sometimes with segfault. David Michael Bayer píše v Čt 07. 12. 2006 v 15:36 -0800: um, web interface ? SQL console ? it would help to know what kind of client youre talking about. --~--~-~--~~~---~--~~ 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: remote nondirect access to DB
Jonathan Ellis píše v Pá 08. 12. 2006 v 11:05 -0700: On 12/7/06, ml [EMAIL PROTECTED] wrote: I want to have a client application accessing a remote Postgres database but I don't want to distribute the user/password to the DB. I want clients to authenticate against some other table of passwords so there must be a stub between client and DB which will know the DB user/pwd. How can I manage this in SA? You can write your stub in SA easily; your client should interface with the stub not via SQL but via a simple protocol you define that limits it to predetermined actions. How? What protocol? Any clues or examples are welcome. DS --~--~-~--~~~---~--~~ 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: remote nondirect access to DB
Jonathan Ellis píše v Pá 08. 12. 2006 v 15:40 -0700: On 12/8/06, ml [EMAIL PROTECTED] wrote: Jonathan Ellis píše v Pá 08. 12. 2006 v 11:05 -0700: On 12/7/06, ml [EMAIL PROTECTED] wrote: I want to have a client application accessing a remote Postgres database but I don't want to distribute the user/password to the DB. I want clients to authenticate against some other table of passwords so there must be a stub between client and DB which will know the DB user/pwd. How can I manage this in SA? You can write your stub in SA easily; your client should interface with the stub not via SQL but via a simple protocol you define that limits it to predetermined actions. How? What protocol? Any clues or examples are welcome. You get to make one up! It's your app! The point is, if you trust the client to send valid SQL you are screwed whether or not the client logs into the database directly or not. So instead of insert into users (name) values (...) your client would sent NEWUSER ... or something. If this sounds intimidating, I would suggest picking up a book covering client/server programming. I believe Programming Python and Core Python both include chapters on socket programming. Now I see where are you heading. So the idea is to put all SA into the stub and each action will be exposed via a single method. It is not what I wanted but it seems I have no other choice. Thanks. DS --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
