[sqlalchemy] Re: enumeration values in the column - referenced from another table
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)... see arguments to sessioncreator, different flavours (e.g. create_session vs sessionmaker) have diff. defaults, pick a subset that suits u. --~--~-~--~~~---~--~~ 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: enumeration values in the column - referenced from another table
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])
[sqlalchemy] Re: enumeration values in the column - referenced from another table
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
[sqlalchemy] Re: enumeration values in the column - referenced from another table
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
[sqlalchemy] Re: enumeration values in the column - referenced from another table
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 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
[sqlalchemy] Re: enumeration values in the column - referenced from another table
i think this should be it: http://www.sqlalchemy.org/docs/04/plugins.html#plugins_associationproxy and relevant discussion here: http://groups.google.com/group/sqlalchemy/browse_thread/thread/52565295f54fd85/31f53dca1e31e5ee?hl=enlnk=gstq=mapper+for+two+tables#31f53dca1e31e5ee 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 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
[sqlalchemy] Re: enumeration values in the column - referenced from another table
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=enlnk=gstq=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
[sqlalchemy] Re: enumeration values in the column - referenced from another table
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):
