i cant attach the file, so posting here at least as a reference.- i
finally solved the whole problem and i also believe it is not
overcomplicated. basically, i needed to have a composite object,
access its attributes (which are another objects), and to keep the
interface very simple to be able to send a simple str values. The
values must be checked/converted (in my case, they must be unique, and
created if not present). the solution is a combination of
EntitySingleton example, association proxy, and some aching back
one question to sqlalchemy experts: is it possible to reduce the
number of queries? i noticed that objects are reloaded every time
insert/update is done even though in many cases the session already
contains the data (and it would be ok just to check that insert/update
operations on the db level succeeded)...
from sqlalchemy import *
from sqlalchemy.orm import mapper, sessionmaker, relation, backref,
scoped_session
from sqlalchemy.ext.associationproxy import association_proxy
import weakref
import operator
import sys
from sqlalchemy.ext.associationproxy import AssociationProxy
Session = sessionmaker()
#engine = create_engine('sqlite:///:memory:', echo=True)
engine = create_engine('sqlite:///test.sqlite', echo=True)
metadata = MetaData(engine)
document_table = Table('document_table', metadata,
Column('id', Integer(9), primary_key=True, autoincrement=True),
Column('name', String(500)),
Column('uri', String(500)),
Column('processing_stage', String(500)),
)
token_table = Table('token_table', metadata,
Column('id', Integer(9), primary_key=True, autoincrement=True),
Column('document_id', Integer, ForeignKey('document_table.id')),
Column('pos', Integer(6)),
Column('type', String(255)), #pozdeji to udelame sequence
Column('surface_id', Integer, ForeignKey('surface_table.id')),
Column('stripped_surface_id', Integer,
ForeignKey('stripped_surface_table.id')),
Column('sem_id', Integer, ForeignKey('sem_table.id')),
)
surface_table = Table('surface_table', metadata,
Column('id', Integer, primary_key=True, autoincrement=True),
Column('value', String(500))
)
stripped_surface_table = Table('stripped_surface_table', metadata,
Column('id', Integer, primary_key=True, autoincrement=True),
Column('value', String(500))
)
sem_table = Table('sem_table', metadata,
Column('id', Integer, primary_key=True, autoincrement=True),
Column('value', String(500))
)
def myassociation_proxy(target_collection, attr, **kw):
return MyAssociationProxy(target_collection, attr, **kw)
class MyAssociationProxy(AssociationProxy):
"""
I need to change the behaviour of the AssociationProxy - to force
it not to update
attributes when they are replaced. Ie. obj.tokens = 'XXX' will
keep the old token
unchanged.
I tried with getset_factory, but the problem is i cant replace the
instance
from the setter (or at least i dont know how to replace the
self.target_collection
with a new instance)
The behaviour is changed only for scalars, for lists it remains
the same as before
"""
def __set__(self, obj, values):
if self.owning_class is None:
self.owning_class = type(obj)
if self.scalar is None:
self.scalar = self._target_is_scalar()
if self.scalar:
self._initialize_scalar_accessors()
if self.scalar:
creator = self.creator and self.creator or
self.target_class
target = getattr(obj, self.target_collection)
#make sure we add only the different instnces
new_object = creator(values)
if new_object is not getattr(obj, self.target_collection):
setattr(obj, self.target_collection, new_object)
#setattr(obj, self.target_collection, creator(values))
else:
proxy = self.__get__(obj, None)
if proxy is not values:
proxy.clear()
self._set(proxy, values)
class EntitySingleton(type):
"""a metaclass that insures the creation of unique and non-
existent entities for
a particular constructor argument. if an entity with a particular
constructor
argument was already created, either in memory or in the database,
it is returned
in place of constructing the new instance."""
def __init__(cls, surface, bases, dct):
cls.instances = weakref.WeakValueDictionary()
def __call__(cls, value):
session = Session()
target_attribute = getattr(cls, getattr(cls,
'target_attribute'))
hashkey = value
try:
return cls.instances[hashkey]
except KeyError:
instance =
session.query(cls).filter(target_attribute==value).first()
if instance is None:
instance = type.__call__(cls, value)
# optional - flush the instance when it's saved
# session.flush([instance])
cls.instances[hashkey] = instance
return instance
class Document(object):
def __init__(self, name):
self.name = name
class Token(object):
# proxy the 'keyword' attribute from the 'kw' relation
surface = myassociation_proxy('_surface', 'value')
stripped_surface = myassociation_proxy('_stripped_surface',
'value')
sem = myassociation_proxy('_sem', 'value')
def __init__(self, pos=None, surface=None, stripped_surface=None,
sem=None):
if surface:
self.surface = surface #str
if stripped_surface:
self.stripped_surface = stripped_surface
if sem:
self.sem = sem
def __repr__(self):
return "<%s(id:'%s',pos:'%s', surface:%s, stripped_surface:%s,
sem:%s)>" % (self.__class__.__name__, self.id, self.pos,
self._surface, self._stripped_surface, self._sem)
class Surface(object):
__metaclass__ = EntitySingleton
target_attribute = 'value'
def __init__(self, value):
self.value = value
def __repr__(self):
return "<%s(id:'%s',value:'%s')>" % (self.__class__.__name__,
self.id, getattr(self, self.target_attribute))
class StrippedSurface(object):
__metaclass__ = EntitySingleton
target_attribute = 'value'
def __init__(self, value):
self.value = value
class Sem(Surface):
__metaclass__ = EntitySingleton
target_attribute = 'value'
def __init__(self, value):
self.value = value
mapper(Document, document_table, properties ={
'tokens':relation(Token, backref=backref('document'))
})
mapper(Token, token_table, properties={
'_surface': relation(Surface, lazy=False),
'_stripped_surface': relation(StrippedSurface, lazy=False),
'_sem': relation(Sem, lazy=False),
})
mapper(Surface, surface_table)
mapper(StrippedSurface, stripped_surface_table)
mapper(Sem, sem_table)
metadata.drop_all()
metadata.create_all()
session = Session()
doc = Document('jek')
t = Token('XXX', surface='AAA')
doc.tokens.append(t)
t.surface = 'BBB'
t.sem = 'xxx'
session.add(doc)
session.commit()
t.surface = 'CCC'
doc.tokens.append(Token(pos=1, surface='AAA', stripped_surface='ONE'))
doc.tokens.append(Token(pos=2, surface='BBB', stripped_surface='ONE',
sem = 'afgh'))
doc.tokens.append(Token(pos=3, surface='CCC', stripped_surface='TWO',
sem='afgh'))
t = Token('XXX', surface='AAA')
doc.tokens.append(t)
t.surface = 'BBB'
t.sem = 'eee'
session.commit()
On Oct 2, 4:59 pm, [EMAIL PROTECTED] wrote:
> On Thursday 02 October 2008 17:13:24 rca wrote:
>
>
>
> > i think this should be it:
> >http://www.sqlalchemy.org/docs/04/plugins.html#plugins_associationp
> >roxy
>
> > and relevant discussion here:
> >http://groups.google.com/group/sqlalchemy/browse_thread/thread/5256
> >5295f54fd85/31f53dca1e31e5ee?hl=en&lnk=gst&q=mapper+for+two+tables#3
> >1f53dca1e31e5ee
>
> > On Oct 2, 3:50 pm, rca <[EMAIL PROTECTED]> wrote:
> > > Thanks again, it is much appreciated
>
> > > On Oct 2, 1:03 pm, [EMAIL PROTECTED] wrote:
> > > > if the tables have nothing in common, u can have a
> > > > MapperExtension that when touching one item updates the proper
> > > > table.
> > > > as a working big example,
> > > > see sqlalachemyAggregator, either
> > > > inhttp://dev.gafol.net/t/aggregator
> > > > orhttp://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/
> > > >misc/agg... which does things like counting summing etc
> > > > runtime-updates-of-some-cache.
> > > > but i'm not sure if that is what u want; imo u need it more
> > > > explicit than this..
>
> > > I have the same feeling (much less assertive here, me, the
> > > sql/alchemy noop)
> > > but i will study the example
>
> > > > and i still cant see how obj.surface=xyz will lookup and
> > > > populate surface_id. imo u need 3 attributes:
> > > > - _surface_text - table1.surface, hidden
> > > > - _surface_id - table2.surface, hidden
> > > > - surface: property which is visible and does the
> > > > dispatch/lookup etc
>
> > > yes, the three attributes, i am able to do this now - what I am
> > > unable is to map them to different tables -- and be persisted
> > > when session.save(obj) -- and
> > > session.query(obj).filter(obj.attribute_from_the_second_table)
> > > It must be something stupid, i will try to come with an example
>
> hhmm depends if these 2 separate tables are also your objects or just
> lookup tables. if they are objetcs, then _surf1 shoud point to that
> Obj1/table1 object and change the attribute there; same for _surf2 /
> Obj2/table2.
>
> if they're just lookup tables, then i dont know. how u link to which
> record to update? give example.
>
> > > > ciao
>
> > > > On Thursday 02 October 2008 13:59:40 rca wrote:
> > > > > thanks for the pointer, I tried and finally managed something
> > > > > the synonym (with the big neon "don't panic" in my head)
>
> > > > > I can do the conversion and insert "integers id" in place of
> > > > > "textual representation" and when accessing the parameter
> > > > > .surface, get the textual representation instead of id
> > > > > In this way, it works the same as my own type, but just more
> > > > > flexible (it can access other parameters of the object).
>
> > > > > But still... is there a way to map one class to two tables in
> > > > > one go?
>
> > > > > To have :
> > > > > obj.surface_id --> table1.surface_id
> > > > > obj.surface --> table2.surface
>
> > > > > when you do:
> > > > > obj.surface = "XXX"
> > > > > obj.name="YYY"
> > > > > to have sqlachemy do:
> > > > > "UPDATE table2 SET surface='XXX' WHERE table2.id = 2" (this
> > > > > is a foreign key, the value is in the table1.surface_id)
> > > > > *AND*
> > > > > "UPDATE table1 SET name='YYY' WHERE table1.id = 8"
>
> > > > > i am getting this, which is wrong, because surface is foreign
> > > > > key, an integer:
> > > > > SELECT s1.* FROM table1 AS s1 WHERE s2.surface_id = 'XXX'
>
> > > > > if using the custom type, i am able to translate the
> > > > > attribute to its id, and use it in the query, yet the synonym
> > > > > might be better, only if it was possible to do
> > > > > session.query(Object).filter(Object.surface== "XXX")
> > > > > and get: SELECT s1.*, s2.* FROM table1 AS s1 LEFT JOIN table2
> > > > > AS s2 ON s1.surface_id = s2.id WHERE s2.surface = 'XXX'
>
> > > > > remember, my goal was to use obj.surface = "XXX" and have
> > > > > this value referenced from the other table.
>
> > > > > Sorry if my questions are stupid!
>
> > > > > --roman
>
> > > > > On Oct 1, 10:21 am, [EMAIL PROTECTED] wrote:
> > > > > > maybe try, instead of hiding the conversion inside db-types
> > > > > > layer, to move it upper, i.e. make it a real
> > > > > > reference/FK-column to real object/table, then make a
> > > > > > python property (see "synonim") to do the conversion.
>
> > > > > > On Tuesday 30 September 2008 23:20:16 rca wrote:
> > > > > > > Hi all,
>
> > > > > > > my apologies for a long question, I am new to sqlalchemy
> > > > > > > and I was struggling with one problem. I solved it but I
> > > > > > > really not sure it's the right way to do it.
>
> > > > > > > The problem is that I need to create objects like this:
> > > > > > > Token(pos=2, surface="helloWorld")
>
> > > > > > > The surface is string, but in the database, it should be
> > > > > > > saved as reference to an other table (looked-up and saved
> > > > > > > if not existing)
>
> > > > > > > The way I solved it was to create my own column type
> > > > > > > decorating Integer. On the way in, it gets text and
> > > > > > > stores id, on the way out, it gives the text back.
>
> > > > > > > But I don't know if my solution is safe. For instance,
> > > > > > > -- I am decorating Integer type, but on the output there
> > > > > > > is string. -- I am using the EntitySingleton recipy, but
> > > > > > > I found out I could not use the recommended
> > > > > > > Session = scoped_session(sessionmaker())
> > > > > > > I had to say: Session = sessionmaker()
> > > > > > > -- And I do commit for every new looked-up value
>
> > > > > > > And also, I have the impression the solution is very
> > > > > > > complicated, perhaps you could help me to do it in a
> > > > > > > better way.
>
> > > > > > > Many thanks!
>
> > > > > > > roman
>
> > > > > > > Here is the code with an example:
> > > > > > > -------------------------------------------------------
>
> > > > > > > import sqlalchemy as sa
> > > > > > > from sqlalchemy.ext.declarative import declarative_base
> > > > > > > from sqlalchemy.orm import scoped_session, sessionmaker,
> > > > > > > relation, backref, mapper
> > > > > > > from sqlalchemy import create_engine, Table, Column,
> > > > > > > Integer, String, MetaData, ForeignKey
> > > > > > > #from mimetypes import types_map
> > > > > > > import weakref
> > > > > > > import sqlalchemy.types as types
>
> > > > > > > Session = sessionmaker() #scoped_session(sessionmaker)
>
> > > > > > > DBase = declarative_base()
>
> > > > > > > class MyUnique(types.TypeDecorator):
> > > > > > > """a type that decorates Integer,
> > > > > > > receives a text representation - eg.
> > > > > > > MyValueFromEnumeration and replaces it in the database
> > > > > > > with id of the string above It makes sure that inserted
> > > > > > > values are unique (creates them if not exist)"""
>
> > > > > > > impl = types.Integer
>
> > > > > > > def process_bind_param(self, value, dialect):
> > > > > > > #ukladani do databaze
> > > > > > > if not hasattr(self, '__unique_valuee'):
> > > > > > > self.__unique_valuee = Surface(value)
> > > > > > > return self.__unique_valuee.id
>
> > > > > > > def process_result_value(self, value, dialect):
> > > > > > > #vystup
> > > > > > > return self.__unique_valuee.surface
>
> > > > > > > """ #if it is string type
> > > > > > > def copy(self):
> > > > > > > return MyUniqueString(self.impl.length)
> > > > > > > """
>
> > > > > > > class DocumentBase(object):
> > > > > > > def __init__(self):
> > > > > > > self.metadata = DBase.metadata
> > > > > > > self.engine = None
>
> > > > > > > def open(self, url='sqlite:///:memory:'):
> > > > > > > self.engine = create_engine(url, echo=True)
> > > > > > > #engine = create_engine('sqlite:///' + __file__ +
> > > > > > > ".db", echo=True)
> > > > > > > Session.configure(bind=self.engine)
> > > > > > > self.metadata.bind = self.engine
>
> > > > > > > surface_table = sa.Table('surface',
> > > > > > > self.metadata, sa.Column('id', sa.Integer,
> > > > > > > primary_key=True, autoincrement=True),
> > > > > > > sa.Column('surface', sa.String(500),
> > > > > > > autoincrement=True) )
>
> > > > > > > mapper(Surface, surface_table)
>
> > > > > > > self.metadata.create_all(checkfirst=True)
>
> > > > > > > def add(self, document_object):
> > > > > > > sess = Session()
> > > > > > > sess.save_or_update(document_object)
> > > > > > > sess.commit()
>
> > > > > > > class EntitySingleton(type):
> > > > > > > """a metaclass that insures the creation of unique
> > > > > > > and non- existent entities for
> > > > > > > a particular constructor argument. if an entity with
> > > > > > > a particular constructor
> > > > > > > argument was already created, either in memory or in
> > > > > > > the database, it is returned
> > > > > > > in place of constructing the new instance."""
>
> > > > > > > def __init__(cls, surface, bases, dct):
> > > > > > > cls.instances = weakref.WeakValueDictionary()
>
> > > > > > > def __call__(cls, surface):
> > > > > > > session = Session()
>
> > > > > > > hashkey = surface
> > > > > > > try:
> > > > > > > return cls.instances[hashkey]
> > > > > > > except KeyError:
> > > > > > > instance =
> > > > > > > session.query(cls).filter(cls.surface==surface).first()
> > > > > > > if instance is None:
> > > > > > > instance =
>
> ...
>
> read more »
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
