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
-~----------~----~----~----~------~----~------~--~---
