[sqlalchemy] Referential integrity actions are not doing what I want
Hi,
I have a table of items, where each item can be owned by one
person, and held by someone else. I want the owner to be
compulsory (not nullable), and the holder to be optional (nullable).
To model this I have two tables, one for contacts and one for items.
The item table has two fields owner_id and holder_id. Bot are
references to the contact table and have the on delete rule set to
restrict and set null respectively.
The problem is that when I want to delete the contact attached to the
holder_id column, it seems that SA tries to set *both* references to
null. It should not do this! For example: If you have an item which
has an owner_id 1 and a holder_id 2, then deleting the contact
with ID 2 will cause the following query:
'UPDATE item SET owner_id=%(owner_id)s, holder_id=%(holder_id)s WHERE
item.item_id = %(item_item_id)s' {'holder_id': None, 'item_item_id':
10, 'owner_id': None}
First of all, *why* is SA issuing this query at all? A delete query
would suffice. The ref. integrity should be handled by the DB,
shouldn't it? More importantly, it updates both owner_id and
holder_id. But as previously said, owner_id=1 and holder_id=2. So
deleting contact #2 should only trigger - if at all - an update query
to set holder_id to null.
Any ideas as to what I am doing wrong here?
Here's my model for those two tables:
contact = Table( 'contact', metadata,
Column( 'contact_id', Integer, primary_key=True,
nullable=False),
Column( 'label', Unicode(64), nullable=False ),
)
item = Table( 'item', metadata,
Column( 'item_id', Integer, nullable=False, primary_key=True ),
Column( 'label', Unicode(64) ),
Column( 'barcode', String(64) ),
Column( 'comment', UnicodeText() ),
Column( 'owner_id', Integer, ForeignKey('contact.contact_id',
onupdate=CASCADE, ondelete=RESTRICT), nullable=False),
Column( 'holder_id', Integer, ForeignKey('contact.contact_id',
onupdate=CASCADE, ondelete=SET NULL), nullable=True),
)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Inheritance over more than one level
Yes. I just read in the TG 1.0.x changelog that it should even work
fine with SA 0.6. I was convinced it only supported SA 0.4. I'm glad I
was wrong about that.
Apart from that, I did indeed forget to add the PK constraint. Now the
inheritance tree is working just fine.
Thanks again.
On Nov 5, 7:22 pm, David Gardner dgard...@creatureshop.com wrote:
I'm currently supporting a TG 1.x app, it works just fine with 0.5.6.
If you use TG 1.0 you will get a deprecation warning about the
Session.mapper.
However I believe TG 1.1 uses
thehttp://www.sqlalchemy.org/trac/wiki/UsageRecipes/SessionAwareMapper
which won't give you a deprecation warning.
With that said you would need to check your code for the usual 0.4-0.5
issues.
exhuma.twn wrote:
Unfortunately I am running an TurboGears 1.0 app. which currently only
supports SA0.4
Porting it to TurboGears 2.0 would be... a lot of work... :)
I'll try your solution this evening. Actually the table has a pkey in
the DB. I must have forgotten to add it in the SA model.
On 5 nov, 02:43, Michael Bayer mike...@zzzcomputing.com wrote:
On Nov 4, 2009, at 7:33 PM, exhuma.twn wrote:
Hi,
All the docs only explain how to do inheritance using a simple parent
child model. But what about parent child grandchild? I tried
the following but I get an error:
code
thing_table = Table( 'thing', metadata,
Column( 'uuid', String(36), primary_key=True,
default=func.uuid_generate_v1() ),
Column( 'reporter', String(30), nullable=False )
Column( 'type', String(30), nullable=False )
)
mineral_table = Table('mineral', metadata,
Column('uuid', String(36), primary_key=True, ForeignKey
('thing.uuid')),
Column( 'family', String(30), nullable=False )
);
crystal_table = Table( 'crystal', metadata,
Column('uuid', String(36), ForeignKey('mineral.uuid')),
Column('label', String(30)))
class Thing(object): pass
class Mineral(Thing): pass
class Crystal(Mineral): pass
mapper( Thing, thing_table, polymorphic_on=thing_table.c.type,
polymorphic_identity='thing' )
mapper(Mineral, mineral_table, inherits=Thing,
polymorphic_identity='mineral')
mapper(Crystal, crystal_table, inherits=Mineral,
polymorphic_identity='crystal')
/code
When I create the mappings like this, the model loads correctly, but I
cannot create Crystal instances:
I think you need a primary key on crystal_table for it to be an
official member of the joined table inheritance club. Also 0.4.6 is a
very old SQLA version and you'd have a lot more query fluency if you
were on 0.5 at least.
x = Crystal()
x.family='kjdfh'
session.save(x)
session.flush()
2009-11-05 01:21:32,795 sqlalchemy.engine.base.Engine.0x..2c INFO
BEGIN
2009-11-05 01:21:32,801 sqlalchemy.engine.base.Engine.0x..2c INFO
SELECT uuid_generate_v1() AS uuid_generate_v1_1
2009-11-05 01:21:32,801 sqlalchemy.engine.base.Engine.0x..2c INFO {}
2009-11-05 01:21:32,805 sqlalchemy.engine.base.Engine.0x..2c INFO
INSERT INTO thing (uuid, type) VALUES (%(uuid)s, %(type)s)
2009-11-05 01:21:32,806 sqlalchemy.engine.base.Engine.0x..2c INFO
{'type': 'rock', 'uuid': '2bb7593c-c9a1-11de-8367-001d720c19db'}
2009-11-05 01:21:32,814 sqlalchemy.engine.base.Engine.0x..2c INFO
INSERT INTO mineral (uuid, family) VALUES (%(uuid)s, %(family)s)
2009-11-05 01:21:32,815 sqlalchemy.engine.base.Engine.0x..2c INFO
{'uuid': '2bb7593c-c9a1-11de-8367-001d720c19db', 'family': 'kjdfh'}
2009-11-05 01:21:32,896 sqlalchemy.engine.base.Engine.0x..2c INFO
COMMIT
Oddly, the Crystal instance is never saved. Any ideas? I am running
this using SQLAlchemy 0.4.6.
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Parameter binding with mappers
Hello sqlalchemy,
I need to execute a select-statement on a table, which uses a custom
stored procedure inside the WHERE clause. That's why I need to use a
text() ClauseElement. But, inside my query I also want to specify
parameters. So far, I have it running using simple % python-style
string-formatting. However, this subjects my query to possible SQL-
Injection attacks. So I would prefer to do it The Right Way and use
bound query parameters. But I fail miserably when doing this. Here are
my attempts:
First attempt:
q = text(:r = lldistance( Point(:x, :y), test_field ),
bindparams=[
bindparam('r', type=types.FloatType),
bindparam('x', type=types.FloatType),
bindparam('y', type=types.FloatType),
])
Info.select( q, r=1000, x=40, y=6) )
Second attempt:
q = text(:r = lldistance( Point(:x, :y), test_field ),
bindparams=[
bindparam('r', type=types.FloatType),
bindparam('x', type=types.FloatType),
bindparam('y', type=types.FloatType),
])
q_compiled = q.compile(parameters={'r':1000, 'x': 40, 'y':6},
bind=turbogears.database.get_engine())
Info.select( q_compiled )
... and so on. These are the two most likely correct(?) attempts.
Otheres involved a lot of fiddling around ;)
None of these worked.
I also know I could use the info_table object (the Table object
assigned to Info), But then I would receive simple table rows as
result-set. But the mapped Info object contains other useful methods
I would need. So I would much rather execute a select statement on the
mapped Info object.
Any pointers?
--~--~-~--~~~---~--~~
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] literal inside a WHERE claus
A stupid example to start with: lt = literal( True ) Event.select( and_(True, lt)) This works fine. But the reason I am using literal is because I am using some advanced postgres features inside a Where clause: lt = literal(geographical_entity.path @ ARRAY( SELECT b.path FROM (virtual_region_has_geo_entity NATURAL JOIN virtual_region) a JOIN geographical_entity b USING (geographical_entity_id) WHERE virtual_region_id=1)) Event.select( and_(True, lt)) This does not work. I get a ProgrammingError indicating that the query is wrong. When I look at the log, and copy/paste the SQL-query that was generated, and paste that into the postgresql shell itself it works. So the generated query works. But it doesn't work through SQLAlchemy. What am I missing? Note: the @ operator comes from the ltree module! --~--~-~--~~~---~--~~ 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: Serializing (pickling) a ClauseList
What do you mean with expression?
I'm sorry, I am not yet really fluent in SQLA ;)
On Nov 25, 4:50 pm, Michael Bayer [EMAIL PROTECTED] wrote:
it should pickle just fine, even if youre using func. ...
from sqlalchemy import *
cl= expression.ClauseList(expression.column('one'), func.foo())
import pickle
pickle.dumps(cl)
ccopy_reg\n_reconstructor\np0\n(csqlalchemy.sql.expression\nClauseList
\np1\nc__builtin__\nobject
\np2\nNtp3\nRp4\n(dp5\nS'operator'\np6\ncsqlalchemy.sql.operators
\ncomma_op\np7\nsS'clauses'\np8\n(lp9\ng0\n(csqlalchemy.sql.expression
\n_ColumnClause
\np10\ng2\nNtp11\nRp12\n(dp13\nS'_is_oid'\np14\nI00\nsS'primary_key'\np15\nI00\nsS'is_literal'\np16\nI00\nsS'foreign_keys'\np17\n(lp18\nsS'key'\np19\nS'one'\np20\nsS'table'\np21\nNsS'type'\np22\ng0\n(csqlalchemy.types\nNullType\np23\ng2\nNtp24\nRp25\n(dp26\nS'_impl_dict'\np27\n(dp28\nsbsS'_ColumnClause__label'\np29\nNsS'name'\np30\ng20\nsbag0\n(csqlalchemy.sql.expression\n_Function\np31\ng2\nNtp32\nRp33\n(dp34\nS'clause_expr'\np35\ng0\n(csqlalchemy.sql.expression\n_Grouping\np36\ng2\nNtp37\nRp38\n(dp39\ng22\nNsS'elem'\np40\ng0\n(g1\ng2\nNtp41\nRp42\n(dp43\ng6\ng7\nsg8\n(lp44\nsS'group'\np45\nI01\nsS'group_contents'\np46\nI01\nsbsbsg45\nI01\nsg30\nS'foo'\np47\nsS'packagenames'\np48\n(lp49\nsS'oid_column'\np50\nNsS'_bind'\np51\nNsg22\ng25\nsbasg45\nI01\nsg46\nI01\nsb.
On Nov 25, 2007, at 9:32 AM, exhuma.twn wrote:
Is it possible to somehow pickle a ClauseList object (output of the
and_ function for example)?
I would like to store the where part of a query and re-use it later.
If I simply try to pickle a clause-list, I get some errors, that the
now method cannot be pickled. Which makes sense. But I wonder why
this is in there anyhow. Where clauses are not bound to time.
I suppose then that this has to do with SLQA-internals. But is there a
way to somehow store predicates?
--~--~-~--~~~---~--~~
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: literal inside a WHERE claus
On Dec 2, 1:30 pm, exhuma.twn [EMAIL PROTECTED] wrote: A stupid example to start with: lt = literal( True ) Event.select( and_(True, lt)) This works fine. But the reason I am using literal is because I am using some advanced postgres features inside a Where clause: lt = literal(geographical_entity.path @ ARRAY( SELECT b.path FROM (virtual_region_has_geo_entity NATURAL JOIN virtual_region) a JOIN geographical_entity b USING (geographical_entity_id) WHERE virtual_region_id=1)) Event.select( and_(True, lt)) This does not work. I get a ProgrammingError indicating that the query is wrong. When I look at the log, and copy/paste the SQL-query that was generated, and paste that into the postgresql shell itself it works. So the generated query works. But it doesn't work through SQLAlchemy. What am I missing? Note: the @ operator comes from the ltree module! Aaah I see what's going on. In the postgres logs, I see that the created statement puts quotes around the literal. Essentially passing it on as a string. Is it not possible to avoid that? --~--~-~--~~~---~--~~ 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: literal inside a WHERE claus
On Dec 2, 2:17 pm, exhuma.twn [EMAIL PROTECTED] wrote: On Dec 2, 1:30 pm, exhuma.twn [EMAIL PROTECTED] wrote: A stupid example to start with: lt = literal( True ) Event.select( and_(True, lt)) This works fine. But the reason I am using literal is because I am using some advanced postgres features inside a Where clause: lt = literal(geographical_entity.path @ ARRAY( SELECT b.path FROM (virtual_region_has_geo_entity NATURAL JOIN virtual_region) a JOIN geographical_entity b USING (geographical_entity_id) WHERE virtual_region_id=1)) Event.select( and_(True, lt)) This does not work. I get a ProgrammingError indicating that the query is wrong. When I look at the log, and copy/paste the SQL-query that was generated, and paste that into the postgresql shell itself it works. So the generated query works. But it doesn't work through SQLAlchemy. What am I missing? Note: the @ operator comes from the ltree module! Aaah I see what's going on. In the postgres logs, I see that the created statement puts quotes around the literal. Essentially passing it on as a string. Is it not possible to avoid that? Ah... using text() instead of literal() makes it all behave nicely... :D --~--~-~--~~~---~--~~ 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] Serializing (pickling) a ClauseList
Is it possible to somehow pickle a ClauseList object (output of the and_ function for example)? I would like to store the where part of a query and re-use it later. If I simply try to pickle a clause-list, I get some errors, that the now method cannot be pickled. Which makes sense. But I wonder why this is in there anyhow. Where clauses are not bound to time. I suppose then that this has to do with SLQA-internals. But is there a way to somehow store predicates? --~--~-~--~~~---~--~~ 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: Generative queries in SA0.3 ?
On Nov 19, 12:41 pm, Glauco [EMAIL PROTECTED] wrote: exhuma.twn ha scritto: I have seen that in SA 0.4 on can do something like: q = table.select() q = q.where(x=1) q = q.where(z=2) ... Is this also possible in SA 0.3? I want to build a web-page where a user can refine filters on the go, to perform a drill-down in a data set. Doing this with plain-text SQL is fairly easy, but doing it with SA is not as straight-forward. Considering this scenario, would it be possible to *remove* one such filter from a query? Say, remove the x=1 from the above query. You must post-pone rendering of qry where clause... where_clause = [] where_clause.append( cond1 ) where_clause.append( cond2 ) where_clause.append( cond3 ) you can at this point of programm remove some condition positinally or by content. where_clause.pop( cond2) or del where_clause[1] finally you can rendere your qry: your_base_qry = session.query( bla bla ) if where_clause: your_removed_qry = your_base_qry.filter( and_( *where_clause ) ) Glauco Thanks. This is nearly the way I am doing it right now. I didn't know and_ could take a list. This is will simplify things. I suppose I will stick to this method then as removing refining filters (adding and removing) is something that is done quite often. --~--~-~--~~~---~--~~ 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] Generative queries in SA0.3 ?
I have seen that in SA 0.4 on can do something like: q = table.select() q = q.where(x=1) q = q.where(z=2) ... Is this also possible in SA 0.3? I want to build a web-page where a user can refine filters on the go, to perform a drill-down in a data set. Doing this with plain-text SQL is fairly easy, but doing it with SA is not as straight-forward. Considering this scenario, would it be possible to *remove* one such filter from a query? Say, remove the x=1 from the above query. --~--~-~--~~~---~--~~ 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: Postgres + timestamp with timezone
On Sep 22, 5:12 pm, Michael Bayer [EMAIL PROTECTED] wrote: this is a supported flag in the DateTime object itself, which gets picked up by postgres. DateTime(timezone=True) or TIMESTAMP(timezone=True) On Sep 19, 2007, at 10:07 AM, exhuma.twn wrote: How is it possible to create a postgres column of type timestamp with timezone? The DateTime class only creates a simple timestamp column withouttimezone. I could not find an appropriate class in the sqlalchemy source code. Or am I missing something? Thanks. This works just fine. --~--~-~--~~~---~--~~ 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: Problem with array fields in postgres
Sean Davis wrote: On 1/2/07, Mando [EMAIL PROTECTED] wrote: Sorry, but I don't understand how create, insert or select data with the array field in postgres. I don't think array fields are supported yet. There is a post from this week that discusses the issue. Sean Good to know. Can you also give a link/reference to that post? I am intrigued by it ;) Cheers, Mich. --~--~-~--~~~---~--~~ 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] Composite primary key
Hi,
I have to load a table from 2 different data-sources witch each having
3 different primary keys. This is because those 2 datasources are
already exported from 3 databases from an archaic application.
From those 2 datasources I created - after normalising - 3 new tables.
The main table keeps the 3 primary keys as one composite primary key.
The two other tables have those 3 fields as foreign keys. Now, if I map
those table definitions onto a table with relations, sqlalchemy
complains with the following error:
sqlalchemy.exceptions.ArgumentError: Error determining primary and/or
secondary join for relationship 'bovines' between mappers
'Mapper|Enterprise|entreprises' and 'Mapper|Bovine|bovines'. You
should specify the 'primaryjoin' (and 'secondaryjoin', if there is an
association table present) keyword arguments to the relation() function
(or for backrefs, by specifying the backref using the backref()
function with keyword arguments) to explicitly specify the join
conditions. Nested error is Cant determine join between 'entreprises'
and 'bovines'; tables have more than one foreign key constraint
relationship between them. Please specify the 'onclause' of this join
explicitly.
Ok, so I have to specify the onclause. But how do I do that? For
reference, here is the (non-working) data definition:
t_bovines = Table( 'bovines', metadata,
Column('id', Integer, primary_key=True),
Column('entrydate', Integer),
Column('key1', Integer, ForeignKey('entreprises.key1')),
Column('key2', Integer, ForeignKey('entreprises.key2')),
Column('key3', String, ForeignKey('entreprises.key3')),
Column('var', Integer),
Column('val', Integer),
)
t_entreprises = Table( 'entreprises', metadata,
Column('key1', Integer, primary_key=True),
Column('key2', Integer, primary_key=True, default=0),
Column('key3', String, primary_key=True),
Column('lname', Unicode(30)),
Column('fname', Unicode(30)),
Column('street', Unicode(30)),
Column('country', String(1)),
Column('zip', String(5)),
Column('locality', Unicode(30)),
)
t_surfaces = Table( 'surfaces', metadata,
Column('id', Integer, primary_key=True),
Column('entrydate', Integer),
Column('key1', Integer, ForeignKey('entreprises.key1')),
Column('key2', Integer, ForeignKey('entreprises.key2')),
Column('key3', String, ForeignKey('entreprises.key3')),
Column('var', Integer),
Column('val', Integer),
)
metadata.create_all()
class Bovine(object):
pass
class Surface(object):
pass
class Enterprise(object):
def __repr__(self):
return [Entreprise %s %s %s] % (self.key1, self.key2,
self.key3)
usermapper = mapper(Bovine, t_bovines)
usermapper = mapper(Surface, t_surfaces)
usermapper = mapper(Enterprise, t_entreprises, properties={
'bovines': relation(Bovine),
'surfaces': relation(Surface)
})
--~--~-~--~~~---~--~~
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: Composite primary key
Jonathan Ellis wrote:
Well, you could specify the primaryjoin as described here:
http://www.sqlalchemy.org/docs/adv_datamapping.myt#advdatamapping_properties_customjoin
but I suspect that your existing mapper will Just Work if you switch
to a composite FK, rather than 3 keys on individual columns
t_bovines = Table( 'bovines', metadata,
Column('id', Integer, primary_key=True),
Column('entrydate', Integer),
Column('key1', Integer),
Column('key2', Integer),
Column('key3', String),
Column('var', Integer),
Column('val', Integer),
ForeignKeyConstraint(['key1', 'key2', 'key3'],
['enterprise.key1', 'enterprise.key2', 'enterprise.key3'])
)
t_entreprises = Table( 'entreprises', metadata,
Column('key1', Integer),
Column('key2', Integer),
Column('key3', String),
Column('lname', Unicode(30)),
Column('fname', Unicode(30)),
Column('street', Unicode(30)),
Column('country', String(1)),
Column('zip', String(5)),
Column('locality', Unicode(30)),
PrimaryKeyConstraint('key1', 'key2', 'key3')
)
# similarly adjust surfaces
On 1/9/07, exhuma.twn [EMAIL PROTECTED] wrote:
Hi,
I have to load a table from 2 different data-sources witch each having
3 different primary keys. This is because those 2 datasources are
already exported from 3 databases from an archaic application.
From those 2 datasources I created - after normalising - 3 new tables.
The main table keeps the 3 primary keys as one composite primary key.
The two other tables have those 3 fields as foreign keys. Now, if I map
those table definitions onto a table with relations, sqlalchemy
complains with the following error:
sqlalchemy.exceptions.ArgumentError: Error determining primary and/or
secondary join for relationship 'bovines' between mappers
'Mapper|Enterprise|entreprises' and 'Mapper|Bovine|bovines'. You
should specify the 'primaryjoin' (and 'secondaryjoin', if there is an
association table present) keyword arguments to the relation() function
(or for backrefs, by specifying the backref using the backref()
function with keyword arguments) to explicitly specify the join
conditions. Nested error is Cant determine join between 'entreprises'
and 'bovines'; tables have more than one foreign key constraint
relationship between them. Please specify the 'onclause' of this join
explicitly.
Ok, so I have to specify the onclause. But how do I do that? For
reference, here is the (non-working) data definition:
t_bovines = Table( 'bovines', metadata,
Column('id', Integer, primary_key=True),
Column('entrydate', Integer),
Column('key1', Integer, ForeignKey('entreprises.key1')),
Column('key2', Integer, ForeignKey('entreprises.key2')),
Column('key3', String, ForeignKey('entreprises.key3')),
Column('var', Integer),
Column('val', Integer),
)
t_entreprises = Table( 'entreprises', metadata,
Column('key1', Integer, primary_key=True),
Column('key2', Integer, primary_key=True, default=0),
Column('key3', String, primary_key=True),
Column('lname', Unicode(30)),
Column('fname', Unicode(30)),
Column('street', Unicode(30)),
Column('country', String(1)),
Column('zip', String(5)),
Column('locality', Unicode(30)),
)
t_surfaces = Table( 'surfaces', metadata,
Column('id', Integer, primary_key=True),
Column('entrydate', Integer),
Column('key1', Integer, ForeignKey('entreprises.key1')),
Column('key2', Integer, ForeignKey('entreprises.key2')),
Column('key3', String, ForeignKey('entreprises.key3')),
Column('var', Integer),
Column('val', Integer),
)
metadata.create_all()
class Bovine(object):
pass
class Surface(object):
pass
class Enterprise(object):
def __repr__(self):
return [Entreprise %s %s %s] % (self.key1, self.key2,
self.key3)
usermapper = mapper(Bovine, t_bovines)
usermapper = mapper(Surface, t_surfaces)
usermapper = mapper(Enterprise, t_entreprises, properties={
'bovines': relation(Bovine),
'surfaces': relation(Surface)
})
Magical! This worked :)
Thanks a lot
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
