[sqlalchemy] Mapping not classes but just their keys
I've got a scenario here that seems as if it would be simple to solve,
but it baffling me so far. Pointers in the right direction would be
appreciated.
I have a table of Foo objects, and FooCollection objects. In a classic
many-to-many, a collection may contain a number of Foos, and any given
Foo may belong to multiple collections. So far so simple. So we create
an intermediate table and map accordingly:
class Foo (object):
...
class FooCollection (object):
...
table_foocollections = Table ('foocollections', mymetadata, ...)
table_foo = Table ('foo', mymetadata, ...)
table_foocollections_foo = Table ('foocollection_foo',
mymetadata,
Column ('collection_id',
None,
ForeignKey ('foocollections.id'),
primary_key=True
),
Column ('foo_id',
None,
ForeignKey ('foo.id'),
primary_key=True,
),
)
mapper (FooCollection, table_foocollections,
properties={
'members': relation (FooCollection,
secondary=table_foocollections_foo,
),
},
)
So when I retrieve or update a FooCollection, members has all the
contained Foos.
But, I have a situation where I don't want FooCollection to actually
contain the actual Foos, just a list of their keys. That is, Foos and
FooCollections have to be handled separately. Obviously it will
involve more paperwork, but how can I make the members attribute
just contain the keys? Is a mapper extension required, or is there a
simpler way?
p
--~--~-~--~~~---~--~~
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: Mapping not classes but just their keys
My, that's fast turnaround :^) Thanks Michael. If I understand associationproxy right, it requires changes to be made to the class, which is a nuisance since one of SA's great strengths is that you don't have alter mapped classes. My classes are being used in non-DB applications, so I'd prefer to steer clear of that. Are there any other possible solutions? p On 27 Jun 2008, at 15:57, Michael Bayer wrote: On Jun 27, 2008, at 10:12 AM, Paul-Michael Agapow wrote: But, I have a situation where I don't want FooCollection to actually contain the actual Foos, just a list of their keys. That is, Foos and FooCollections have to be handled separately. Obviously it will involve more paperwork, but how can I make the members attribute just contain the keys? Is a mapper extension required, or is there a simpler way? we have a class-level extension called associationproxy thats used for this: http://www.sqlalchemy.org/docs/05/ plugins.html#plugins_associationproxy -- Dr Paul-Michael Agapow, VieDigitale / Institute of Animal Health [EMAIL PROTECTED] --~--~-~--~~~---~--~~ 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] Problems with merge relations
I'm using merge to push objects into a db. (Long story short: I have
a set of objects that have to behave identically if they are
persisted or not, and may be retrieved or sent to a set of databases.
Hence the use of merge.) However, merged objects appear object
joined by a relation and backrefs twice. Is this a bug or an I making
some subtle mistake?
A condensed demonstration - the names of the mapped objects and
tables are unimportant, but a Biosequence has a one-to-many with a
set of Annotations, established by a backref::
from sqlalchemy import *
from sqlalchemy.orm import *
metadata = MetaData()
class Biosequence (object):
pass
class Annotation (object):
pass
table_biosequences = Table ('biosequences', metadata,
Column ('identifier', Integer(), primary_key=True),
Column ('dummy', String(16)),
)
table_seqannotations = Table ('seqannotations', metadata,
Column ('identifier', Integer(), primary_key=True),
Column ('biosequence_id', Integer(), ForeignKey
('biosequences.identifier')),
)
mapper (Annotation, table_seqannotations)
mapper (Biosequence, table_biosequences,
properties={
'annotations':relation (
Annotation,
backref='biosequence_ref',
lazy=False,
cascade=all, delete, delete-orphan,
),
},
)
engine = create_engine ('sqlite:///', convert_unicode=True,
# echo=True,
)
sessionfactory = sessionmaker (bind=engine, autoflush=False,
transactional=False)
session = sessionfactory()
metadata.create_all (bind=engine, checkfirst=True)
# make an object with 3 'children'
bseq = Biosequence()
anns = [Annotation() for x in range (3)]
bseq.annotations = anns
print Original num of anns:, len (bseq.annotations)
# the merged copy has 6 children
merge_bseq = session.merge (bseq)
session.flush()
print Now num of anns still:, len (bseq.annotations)
print Ids:, [x.identifier for x in bseq.annotations]
print Merged copy num of anns:, len (merge_bseq.annotations)
print Ids:, [x.identifier for x in merge_bseq.annotations]
# as does the return!
results = session.query (Biosequence)
print Number of results, results.count()
returned_bseq = results.one()
print Returned num of anns:, len (returned_bseq.annotations)
print Ids:, [x.identifier for x in returned_bseq.annotations]
# make an new object with 3 children
bseq2 = Biosequence()
bseq2.annotations = [Annotation() for x in range (3)]
print New obj num of anns:, len (bseq.annotations)
session.save_or_update (bseq2)
session.flush()
# and it works as expected
results = session.query (Biosequence)
print Number of retreived objects, results.count()
returned_bseqs = results.all()
for item in returned_bseqs:
print Returned num of anns:, len (item.annotations)
print Ids:, [x.identifier for x in item.annotations]
Which returns::
Original num of anns: 3
Now num of anns still: 3
Ids: [None, None, None]
Merged copy num of anns: 6
Ids: [1, 1, 2, 2, 3, 3]
Number of results 1
Returned num of anns: 6
Ids: [1, 1, 2, 2, 3, 3]
New obj num of anns: 3
Number of retreived objects 2
Returned num of anns: 6
Ids: [1, 1, 2, 2, 3, 3]
Returned num of anns: 3
Ids: [4, 5, 6]
In summary: if an object is stored using 'save_or_update', when later
retrieved, it has the correct number of related children. If merged,
the returned merge copy has each child twice. If this object is
retrieved later, it still has every child twice. Looking at the db,
the correct number of child rows is being stored and the relations
are correct. It therefore looks like there's a problem in the session
for merged objects.
Any pointers?
--
Dr Paul-Michael Agapow: VieDigitale / Inst. for Animal Health
[EMAIL PROTECTED] / [EMAIL PROTECTED]
--~--~-~--~~~---~--~~
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] Same schema, multiple databases, simultaneously
I'm puzzling out how to connect to multiple databases that have the
same tables (and the same table definitions). The use case is that
data may be retrieved from two or more similar dbs and perhaps copied
between them. Following earlier advice, I've used a single metadata
and 'bind' in the session creation to indicate which engine. However
there's a problem. Example code below::
from sqlalchemy import *
from sqlalchemy.orm import *
uri1 = # blah blah
uri2 = # blah blah
class Dummy (object):
simple mapped class
pass
class DbConnection (object):
encapsulate engine session
def __init__ (self, uri, dbschema):
engine = create_engine (uri)
Session = sessionmaker (bind=engine, autoflush=False,
transactional=False)
self._session = Session()
dbschema.metadata.create_all (bind=engine,
checkfirst=True)
def __del__ (self):
self._session.close()
class DbSchema (object):
metadata table defintions
def __init__ (self):
self.metadata = MetaData()
self.dummy_table = Table('dummy', self.metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(40)),
)
mapper (Dummy, self.dummy_table)
# define tables and connect to both dbs
schema = DbSchema()
conn1 = DbConnection (uri1, schema)
conn2 = DbConnection (uri2, schema)
# create object
x = Dummy()
x.name = u'testname'
# save to one and detach object
conn1._session.save_or_update (x)
conn1._session.flush ()
conn1._session.expunge (x)
# save to two silently fails!
conn2._session.save_or_update (x)
conn2._session.flush ()
conn2._session.expunge (x)
The object saves obediently to the first db, but fails to save to the
second (although tables are created correctly). The successful write
is always the first one, regardless of the order of opening
connections. It doesn't depend on how the primary key is defined:
autogenerated or supplied. I therefore surmise this is because
'save_or_update' detects the object as already being persistent,
despite the use of 'expunge'.
Is there another step required to detach the object, or is another
approach necessary to handle multiple dbs?
--
Dr Paul-Michael Agapow: VieDigitale / Inst. for Animal Health
[EMAIL PROTECTED] / [EMAIL PROTECTED]
--~--~-~--~~~---~--~~
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] Attaching a 'pre-created' class to a session?
It seems there must be something I'm missing here, so hopefully other
sets of eyes will spot an obvious mistake.
I have a series of objects that may-or-may not be persistent: some
will be created and stored to a db, some will be retrieved from the
db, maybe modified and updated, others will never go anywhere near
the db. (I have a series of programs using the same objects, only
some of which need to interact with the db.) However, I'm
A simple test case. Given this code:
class Sample (object):
def __init__ (self, id=None, title=None, description=None):
self.id = id
self.title = title
self.description = description
class Conn (object):
def __init__ (self):
conn_uri = CONN_URI_TMPL % TESTDB_CONNINFO
self.SA_ENGINE = create_engine (conn_uri)
self.SA_METADATA = MetaData (conn_uri)
self.SA_ENGINE.echo = True
Session = sessionmaker (bind=self.SA_ENGINE)
self.SA_SESSION = Session()
self.SA_QUERY = self.SA_SESSION.query
self.SAMPLETABLE = Table ('samples', self.SA_METADATA,
Column ('id', Unicode(32), primary_key=True),
Column ('title', Unicode(32)),
Column ('description', Unicode(32)),
)
self.SA_METADATA.create_all (checkfirst=True)
clear_mappers()
mapper (Sample, self.SAMPLETABLE)
Sample is the class I'd like to be able to persist if needed. Conn
just encapsulates the connection for test purposes. Now if I do this:
c = Conn()
s1 = Sample()
s1.id = 'foo'
c.SA_SESSION.save_or_update (s1)
c.SA_SESSION.flush()
all is well. But if I create the Sample before the connection, it
doesn't work:
s1 = Sample()
s1.id = 'foo'
c = Conn()
c.SA_SESSION.save_or_update (s1)
c.SA_SESSION.flush()
AttributeError: 'ColumnProperty' object has no attribute 'key'
So, how can I persist an object constructed before the connection is
established? Or is it necessary to do all work either within or
without of the context of a session?
Thanks
--
Dr Paul-Michael Agapow: VieDigitale / Inst. for Animal Health
[EMAIL PROTECTED] / [EMAIL PROTECTED]
--~--~-~--~~~---~--~~
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: Design: mapped objects everywhere?
Yowser. Thanks to both of you - that's exactly what I mean. Any pointers on where I can find an example of a class that is unaware if it is in the db? Or is there a good example of the second solution, of a single class that does the what and why, and an interchangeable layer/context that does load/saving? I'm digging through dbcook.sf.net but haven't found anything just yet. On 2007 Dec 7, at 22:07, [EMAIL PROTECTED] wrote: Paul Johnston wrote: A Sample may be created by the web application or fetched from the database. Later on, it may be disposed of, edited or checked back into the db. Sounds like you want your app to be mostly unaware of whether a class is saved in the db or not (i.e. persistent)? If so, I'd use a single class, design the properties so they work in non-persistent mode, and then they'll work in persistent mode as well. or like a single class that does the what and why, and an interchangeable layer/context that does load/saving (and the relations!). in such situations declarative programming helps a lot, so u dont bind your self to (the) db (or whatever persistency). Check dbcook.sf.net. My own latest experience is about turning a project that was thought for db/using dbcook into non-db simple-file-based persistency. The change was relatively small, like 5-10 lines per class - as long as there are Collections etc similar notions so Obj side of ORM looks same. -- Dr Paul-Michael Agapow: VieDigitale / Inst. for Animal Health [EMAIL PROTECTED] / [EMAIL PROTECTED] --~--~-~--~~~---~--~~ 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] Design: mapped objects everywhere?
More of a design question, complicated by the fact that I'm a database neophyte (although I've used SA a fair bit). I have a web application that with periodically connect to a database. I've written a wrapper for the db using SQLalchemy, but I'm unsure about the best way to use the mapped classes. In some cases the client will fetch records (mapped classes) from the database and immediately use and then dispose of them. No problems. In other cases, it will keep those fetched objects around indefinitely, perhaps late changing them and sending them back. In even other cases, it might create instances of those objects independently of the db, and use them, maybe checking them in. Put as a use case: A Sample may be created by the web application or fetched from the database. Later on, it may be disposed of, edited or checked back into the db. So, the design question is should the mapped (database) object classes be the same as the extra-db classes? On one hand, it's a lot simpler to have a single class Sample, rather than SampleRow (the mapped class) and SampleData (an unmapped, extra-db class). On the other hand, the requirements and coding of both classes are kinda different, and I find myself changing the properties of the mapped class for better mapping and making the use of the class in non-Db contexts more awkward. Also, the SA magic has made debugging of some non-Db issues quite difficult. Opinions or issues to consider. -- Dr Paul-Michael Agapow: VieDigitale / Inst. for Animal Health [EMAIL PROTECTED] / [EMAIL PROTECTED] --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
