[sqlalchemy] Re: Single table inheritance and mapping against a selectable
On 7/28/07, Michael Bayer [EMAIL PROTECTED] wrote: those are fine with me. if someone could add a ticket and/or implement that would be helpful. http://www.sqlalchemy.org/trac/ticket/696 -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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: permanent objects and sessions
your best option is probably to keep the objects in a session that's specifically for the global objects. Most important is that if you are using multiple threads, you never call flush() on this session. With regards to threadsafety in this case, as long as the session is not used for a flush() operation, you should be OK. The connection used for querying for a non-transactional session is checked out distinctly per-operation, so there will be no conflicts there. The second place of concern is when the Query retrieves existing objects from the identity map as it loads rows, and as it places newly loaded objects into the identity map; multiple threads might both create the same object and might both store it in the identity map; only one would actually remain in the map. However for a purely read-only situation, your application will still see the same kinds of objects, and even the object that was replaced, which still would remain established in a parent collection, can still call upon that session to issue further lazyloads. I might look into adding a flag in 0.4 session readonly, which produces a session that disallows flush() and places a single mutex around query.instances(); then youd have a completely threadsafe read- only session. its only a couple of lines. On Jul 28, 10:09 am, MuTPu4 [EMAIL PROTECTED] wrote: One of my tables represents a tree structure which I want to be cached in memory during my application's lifetime. Problem is that i can not figure out how to organize sessions acquisition/releasing. I am loading the whole hierarchy using one session and then closing it which makes objects detached. Then duaring modifications and lazy loads across relations I am getting an errors like: InvalidRequestError: Parent instance class 'si.core.category.Category' is not bound to a Session, and no contextual session is established; lazy load operation of attribute 'parent' cannot proceed Creating a new session and reattaching existing objects to it using update( ) helps but I hope that reattaching the whole hierarchy is not the right way. Is there a way to keep permanent objects in memory without holding initial session for the whole application lifetime? Sorry for my bad English. --~--~-~--~~~---~--~~ 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: permanent objects and sessions
On Sat, 28 Jul 2007 14:36:45 - Michael Bayer [EMAIL PROTECTED] wrote: your best option is probably to keep the objects in a session that's specifically for the global objects. Most important is that if you are using multiple threads, you never call flush() on this session. With regards to threadsafety in this case, as long as the session is not used for a flush() operation, you should be OK. The connection used for querying for a non-transactional session is checked out distinctly per-operation, so there will be no conflicts there. The second place of concern is when the Query retrieves existing objects from the identity map as it loads rows, and as it places newly loaded objects into the identity map; multiple threads might both create the same object and might both store it in the identity map; only one would actually remain in the map. However for a purely read-only situation, your application will still see the same kinds of objects, and even the object that was replaced, which still would remain established in a parent collection, can still call upon that session to issue further lazyloads. I might look into adding a flag in 0.4 session readonly, which produces a session that disallows flush() and places a single mutex around query.instances(); then youd have a completely threadsafe read- only session. its only a couple of lines. Sorry if this is naive.. but.. if the data is 'readonly' and can not be flushed, why keep the very valuable thread, session, and db connection open? Why not just load data into a global variable and be done with it? -- michael --~--~-~--~~~---~--~~ 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: permanent objects and sessions
On Jul 28, 5:36 pm, Michael Bayer [EMAIL PROTECTED] wrote:
your best option is probably to keep the objects in a session that's
specifically for the global objects. Most important is that if you
are using multiple threads, you never call flush() on this session.
With regards to threadsafety in this case, as long as the session is
not used for a flush() operation, you should be OK. The connection
used for querying for a non-transactional session is checked out
distinctly per-operation, so there will be no conflicts there. The
second place of concern is when the Query retrieves existing objects
from the identity map as it loads rows, and as it places newly loaded
objects into the identity map; multiple threads might both create the
same object and might both store it in the identity map; only one
would actually remain in the map. However for a purely read-only
situation, your application will still see the same kinds of objects,
and even the object that was replaced, which still would remain
established in a parent collection, can still call upon that session
to issue further lazyloads.
I might look into adding a flag in 0.4 session readonly, which
produces a session that disallows flush() and places a single mutex
around query.instances(); then youd have a completely threadsafe read-
only session. its only a couple of lines.
Thanks for your replay.
Probably my case is somewhat more compilcated but I thought that it is
rather common. It may be outlined as follows.
I have a long-running application (web server) which resopnds to
frequent queries users (pages fetch). The data is organized into
hierarchical directory which is represented by Nodes and Items:
[code]
self.categories_table = Table(
'categories'
, self.metadata
, Column( 'category_id', Integer, primary_key = True )
, Column( 'name', String, nullable = False, index = True )
, Column( 'parent_category_id', Integer, nullable = True, index
= True )
, ForeignKeyConstraint( [ 'parent_category_id' ],
[ 'categories.category_id' ], onupdate = 'CASCADE', ondelete =
'CASCADE' )
)
self.items_table = Table(
'items'
, self.metadata
, Column( 'item_id', Integer, primary_key = True )
, Column( 'name', String, nullable = False, index = True )
, Column( 'parent_category_id', Integer, nullable = False, index
= True )
, ForeignKeyConstraint( [ 'parent_category_id' ],
[ 'categories.category_id' ], onupdate = 'CASCADE', ondelete =
'CASCADE' )
)
[/code]
I want to have a global preloaded tree structure for fast access
(frequent reads and infrequent modifications) with application's
lifetime and ability to fetch Items as needed. I wanted to use some
nice automation features of SA's ORM (lazy
loads across relations, automatic DB updates etc) so I have come up
with the following solution:
[code]
class Category( object ) :
def __init__( self, name, parent_category ) :
self.name = name
self.parent = parent_category
class Item( object ) :
def __init__( self, name, parent_category ) :
self.name = name
self.parent = parent_category
[/code]
[code]
#data mapping
self.items_mapper = mapper( item.Item, self.items_table, properties
= {
'parent' : relation( Category, uselist = False, lazy = False )
} )
self.categories_mapper = mapper( category.Category,
self.categories_table, properties = {
'children' : relation( Category, backref = backref( 'parent',
remote_side = [ self.categories_table.c.category_id ] ), cascade =
'all' )
} )
[/code]
[code]
#application init
session = self.db.create_session( )
self.root =
session.query( Category ).selectone( Category.c.parent_category_id ==
None )
session.close( )
[/code]
[code]
#hypothetical code in one of the threads
session = self.db.create_session( )
new_obj = Category( 'test', directory.root )
session.save( new_obj )
session.flush( )
print new_obj.parent.name
len( new_obj.children )
subitems = session.query( Item ).select_by( parent_category_id =
directory.root.category_id )
session.close( )
[/code]
Now I see that this approach is not viable as-is. Maybe there is a way
to adopt it using modules like SessionContext and assignmapper?
If there is no such a way, I currently see two solutions:
1. Just dont use permanent objects and fetch an appropriate portions
of informations at every request relaying on DB's cache and loopback
network optimizations.
2. Using basic SA features implement an ad-hoc ORM as a part of
Category and Item classes which will use given session to fetch/insert/
uopdate data.
Maybe someone can point me to another not so painful solution?
--~--~-~--~~~---~--~~
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
[sqlalchemy] Newbie question about transactions
In the following code, I intended that nothing will be inserted into
the table because the transaction fails and is rolled back. What
actually happens is that the first insert is not rolled back because
it s committed by itself.
What is the correct way to achieve my goal?
TIA
from sqlalchemy import *
db=create_engine('mysql://user:pw@localhost/db')
cn= db.connect()
cn.execute(DROP TABLE IF EXISTS test)
cn.execute(CREATE TABLE test (`id` bigint(20) NOT NULL , PRIMARY KEY
(`id`))
ENGINE=InnoDB)
db.echo=True
md=BoundMetaData(db)
t=Table('test', md, autoload = True)
trans = cn.begin()
try:
insert(t).execute(id=332)
insert(t).execute(id=332)
trans.commit()
except:
trans.rollback()
raise
--~--~-~--~~~---~--~~
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: Newbie question about transactions
Thanks.
Can you point me to the place in the doc where this is discussed?
I was under the impression that I had one connection only in this
scenario.
TIA
On Jul 28, 11:09 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Jul 28, 2007, at 2:54 PM, mc wrote:
md=BoundMetaData(db)
t=Table('test', md, autoload = True)
trans = cn.begin()
try:
insert(t).execute(id=332)
insert(t).execute(id=332)
trans.commit()
except:
trans.rollback()
raise
the statements must be executed relative to the connection which
contains the transaction:
cn.execute(insert(t), id=332)
--~--~-~--~~~---~--~~
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: Newbie question about transactions
discussion starts here:
http://www.sqlalchemy.org/docs/dbengine.html#dbengine_connections
covers some options available to you including using a thread local
connection.
On Jul 28, 2007, at 5:07 PM, mc wrote:
Thanks.
Can you point me to the place in the doc where this is discussed?
I was under the impression that I had one connection only in this
scenario.
TIA
On Jul 28, 11:09 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Jul 28, 2007, at 2:54 PM, mc wrote:
md=BoundMetaData(db)
t=Table('test', md, autoload = True)
trans = cn.begin()
try:
insert(t).execute(id=332)
insert(t).execute(id=332)
trans.commit()
except:
trans.rollback()
raise
the statements must be executed relative to the connection which
contains the transaction:
cn.execute(insert(t), id=332)
--~--~-~--~~~---~--~~
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: permanent objects and sessions
On Jul 28, 11:08 pm, Michael Bayer [EMAIL PROTECTED] wrote:
theres nothing at all wrong with the above; the only adjustments Id
make are to a. leave a global session open for your directory.root,
and b. remove all cascade rules from the parent relation (in this
case using backref=backref('parent', cascade=None), so that when you
attach the out-of-session Category as a 'parent' to your new
Category, the child category can get a parent_id value but the parent
Category object is otherwise unaffected.
I have tried to use global session object for all DB operations
without synchronization and it seems to work perfectly on simple
tests. It remains only to add application-level synchronization. But I
don't quite understand your second advise about cascading. I thought
that in case of using global session object none of Category instances
can be detached. Am I missing something?
If that produces problems, the more official way to go about it is
to merge() the parent Category into your current session:
parent = session.merge(directory.root)
new_obj = Category('test', parent)
...
the merge() operation will create a copy of parent within the local
session. It will usually issue a fetch operation from the database
in order to get the most recent data for the particular Category.
The problem with this solution is that I dont always know the part of
the hierarchy that I want to be in the current session. I am relying
on implicit lazy loads and the client-code may want to access a large
parts of the tree using new session, reattaching all of these nodes
which makes my cache strategy useless.
this is what SA is currently more tailored towards; being able to
load data fresh from the DB with as few queries as possible, thereby
avoiding the data freshness issues that come when second-level
caches are introduced. What youre really trying to do here is dual-
purpose the Session as a second level cache, which is possible as I
outlined using merge(), but its not completely ideal. SA will
eventually implement a real second level caching system; it will
involve producing new object instances on the fly which are bound to
a particular session; just the actual data from those objects would
be cached instead of being fetched from the DB (and it also will use
synchronization similar to that outlined above).
From the point of view of my example it would be usefull to decouple
loading/storing API from mapped objects themselfs. If it is possible
it will be usefull opportunity imho.
Thank you kindly for your help and advices.
--~--~-~--~~~---~--~~
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: permanent objects and sessions
On Jul 28, 2007, at 8:46 PM, MuTPu4 wrote:
On Jul 28, 11:08 pm, Michael Bayer [EMAIL PROTECTED] wrote:
theres nothing at all wrong with the above; the only adjustments Id
make are to a. leave a global session open for your directory.root,
and b. remove all cascade rules from the parent relation (in this
case using backref=backref('parent', cascade=None), so that when you
attach the out-of-session Category as a 'parent' to your new
Category, the child category can get a parent_id value but the parent
Category object is otherwise unaffected.
I have tried to use global session object for all DB operations
without synchronization and it seems to work perfectly on simple
tests. It remains only to add application-level synchronization. But I
don't quite understand your second advise about cascading. I thought
that in case of using global session object none of Category instances
can be detached. Am I missing something?
when you say:
c = Category('foo', root)
and you then attach 'root' using c.parent = root, a cascade rule
associated with the 'parent' relation will attempt to add the newly
attached 'root' Category to the local session. Since 'root' is
already in a different Session, this will raise an error. so
instead, make a local copy of root using merge() before using it in
a local session.
or if not, you can even just associate using the column based
attributes before flushing:
c = Category('foo')
c.parent_id = root.id
above, you arent attaching any of your cached objects to your new
Category, just setting the appropriate column-valued elements on
it. This way you limit the manipulations to just your new object
without modifying your cache's structure or state at all.
the merge() operation will create a copy of parent within the local
session. It will usually issue a fetch operation from the database
in order to get the most recent data for the particular Category.
The problem with this solution is that I dont always know the part of
the hierarchy that I want to be in the current session. I am relying
on implicit lazy loads and the client-code may want to access a large
parts of the tree using new session, reattaching all of these nodes
which makes my cache strategy useless.
merge() will cascade by default. when you merge() root, it also merge
()s everything currently attached to root (this is also
configurable). its actually not that efficient of an operation if
your structure is very large. you probably dont need to load very
much into your local session though, just whatever gets immediately
associated with whatever objects you're flushing.
From the point of view of my example it would be usefull to decouple
loading/storing API from mapped objects themselfs. If it is possible
it will be usefull opportunity imho.
you can work with the objects detached from any session, but when you
want to put them into a local Session and save them you need to
either put them there, or make copies of them. the whole way unit-of-
work tools like SQLAlchemy's ORM function is by keeping track of
object state and identity; if we allowed you to put your Category
object in your cache session A, as well as session B used in a
particular request that wants to save data, and session C used in yet
another concurrent request that wants to save data, it should be
apparent that manipulating the object in all those concurrent threads
is going to produce inconsistent and conflicting state between each
thread...and manipulation operations are extremely common; for
example, when you attach root to the new Category c via its
parent attribute, the backref is also appending c to root's
children attribute automatically. theres not really any reasonable
option to having local, non-thread-shared copies of the objects which
you're manipulating.
Also, you've stated that you want lazyloads to occur, so already, you
do want load operations embedded into mapped objects. if you didnt
want that, you could leave your cached objects out of any session
entirely.
the only thing to follow here is, for all your read-only operations,
just use your cache. for any kind of im going to save something
operation, just load what you need fresh from the database into a
local session, put it all together, and flush. by going through the
effort to tailor your application this way you help to guarantee the
consistency of your objects and the data that goes into your database.
--~--~-~--~~~---~--~~
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] querying many-to-many (WordPress)
Hello,
I'm using SQLAlchemy to access my WordPress database and I need to
query posts from particular category.
There is a many-to-many mapping between wp_posts and wp_categories
table, throught wp_post2cat table.
I was able to come up with the following code:
cats = self.meta.tables['wp_categories']
posts = self.meta.tables['wp_posts']
post2cat = self.meta.tables['wp_post2cat']
q = self.session.query(WordpressPost)
q = q.filter(WordpressPost.c.status=='publish')
q = q.filter(WordpressCategory.c.slug=='sitenews')
q = q.filter(post2cat.c.post_id==posts.c.ID)
q = q.filter(post2cat.c.category_id==cats.c.cat_ID)
It works correctly but seems too verbose to me. Basically I do two
joins by hand. I suspect SQLAlchemy can do this for me, just can't
figure out how. Help, please?
Max.
P.S.: I have:
mapper(WordpressCategory, wp_categories_tbl , properties={
'id' : wp_categories_tbl.c.cat_ID,
...
})
mapper(WordpressPost, wp_posts_tbl, properties={
'id' : wp_posts_tbl.c.ID,
...
'categories': relation(WordpressCategory,
secondary=wp_post2cat_tbl),
})
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
