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 in http://dev.gafol.net/t/aggregator
or
http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/aggregator/
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..
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
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 = type.__call__(cls, surface)
> > > #session.begin_nested()
> > > session.add(instance)
> > > session.commit()
> > > # optional - flush the instance when it's saved
> > > session.flush([instance])
> > > cls.instances[hashkey] = instance
> > > return instance
> > >
> > > class Document(DBase):
> > > __tablename__ = 'documents'
> > > id = sa.Column(sa.Integer(9), primary_key=True,
> > > autoincrement=True)
> > > name = sa.Column(sa.String(500))
> > > uri = sa.Column(sa.String(500))
> > > processing_stage = sa.Column(sa.String(500))
> > >
> > > tokens = relation("Token", backref="Document")
> > >
> > > def __xinit__(self, name):
> > > self.name = name
> > > def __repr__(self):
> > > return "<%s('%s','%s')>" % (self.__class__, self.id,
> > > self.name)
> > >
> > > class Token(DBase):
> > > __tablename__ = 'tokens'
> > > id = sa.Column(sa.Integer(6), primary_key=True,
> > > autoincrement=True)
> > > pos = sa.Column(sa.Integer(6))
> > > type = sa.Column(sa.String(500))
> > > surface = sa.Column(MyUnique(500),
> > > sa.ForeignKey('surface.id')) surface_translated =
> > > sa.Column(sa.String(500))
> > > sem = sa.Column(sa.String(500))
> > >
> > > title_id = sa.Column(sa.Integer,
> > > sa.ForeignKey('documents.id')) #document = relation("Document",
> > > backref="Token")
> > >
> > > #def __init__(self, surface=None, **kwargs):
> > >
> > > def __repr__(self):
> > > return "<%s('%s','%s')>" % (self.__class__, self.id,
> > > self.surface)
> > >
> > > class Surface(object):
> > > __metaclass__ = EntitySingleton
> > > #__tablename__ = 'surface'
> > > #id = sa.Column(sa.Integer(6), primary_key=True,
> > > autoincrement=True)
> > > #surface = sa.Column(sa.String(500), primary_key=True)
> > > def __init__(self, value):
> > > self.surface = value
> > >
> > > def test_database():
> > > session = Session()
> > > doc = Document(uri="xx1", name="TestDoc")
> > > doc.name = "Test"
> > > for i in range(1,5):
> > > t = Token(pos=i, surface="Text" + str(i))
> > > doc.tokens.append(t)
> > > #insert the same values again
> > > for i in range(1,5):
> > > t = Token(pos=i, surface="Text" + str(i))
> > > doc.tokens.append(t)
> > > session.add(doc)
> > > session.commit()
> > > print doc.tokens
> > > print
> > >
> > > if __name__ == "__main__":
> > > db = DocumentBase()
> > > db.open()
> > > test_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
-~----------~----~----~----~------~----~------~--~---
