[sqlalchemy] Re: composite primary key problem
Hey, Michael Bayer wrote: the program works for me, I get: That's interesting. I've tested the script with Python 2.4, Python 2.5, and Python 2.6, with SQLAlchemy 0.5.5 and trunk. sqlite version is 3.4.2. I get the assertion error each time. The output I get when echo is True (on SA trunk/Python 2.5) is this: 2009-08-25 15:19:18,637 INFO sqlalchemy.engine.base.Engine.0x...974c PRAGMA table_info(user) 2009-08-25 15:19:18,637 INFO sqlalchemy.engine.base.Engine.0x...974c () 2009-08-25 15:19:18,638 INFO sqlalchemy.engine.base.Engine.0x...974c CREATE TABLE user ( code INTEGER NOT NULL, status INTEGER NOT NULL, username VARCHAR NOT NULL, PRIMARY KEY (code, status) ) 2009-08-25 15:19:18,638 INFO sqlalchemy.engine.base.Engine.0x...974c () 2009-08-25 15:19:18,638 INFO sqlalchemy.engine.base.Engine.0x...974c COMMIT 2009-08-25 15:19:18,640 INFO sqlalchemy.engine.base.Engine.0x...974c BEGIN 2009-08-25 15:19:18,640 INFO sqlalchemy.engine.base.Engine.0x...974c INSERT INTO user (code, status, username) VALUES (?, ?, ?) 2009-08-25 15:19:18,641 INFO sqlalchemy.engine.base.Engine.0x...974c [0, 1, u'a'] 2009-08-25 15:19:18,641 INFO sqlalchemy.engine.base.Engine.0x...974c COMMIT 2009-08-25 15:19:18,641 INFO sqlalchemy.engine.base.Engine.0x...974c BEGIN 2009-08-25 15:19:18,642 INFO sqlalchemy.engine.base.Engine.0x...974c INSERT INTO user (code, status, username) VALUES (?, ?, ?) 2009-08-25 15:19:18,642 INFO sqlalchemy.engine.base.Engine.0x...974c [0, 2, u'a'] 2009-08-25 15:19:18,642 INFO sqlalchemy.engine.base.Engine.0x...974c COMMIT 2009-08-25 15:19:18,643 INFO sqlalchemy.engine.base.Engine.0x...974c BEGIN 2009-08-25 15:19:18,643 INFO sqlalchemy.engine.base.Engine.0x...974c SELECT user.code AS user_code, user.status AS user_status, user.username AS user_username FROM user WHERE user.code = ? AND user.status = ? 2009-08-25 15:19:18,644 INFO sqlalchemy.engine.base.Engine.0x...974c [0, 1] 2009-08-25 15:19:18,645 INFO sqlalchemy.engine.base.Engine.0x...974c SELECT user.code AS user_code, user.status AS user_status, user.username AS user_username FROM user WHERE user.code = ? AND user.status = ? 2009-08-25 15:19:18,645 INFO sqlalchemy.engine.base.Engine.0x...974c [0, 2] 2009-08-25 15:19:18,646 INFO sqlalchemy.engine.base.Engine.0x...974c UPDATE user SET status=? WHERE user.code = ? AND user.status = ? 2009-08-25 15:19:18,646 INFO sqlalchemy.engine.base.Engine.0x...974c [3, 0, 1] 2009-08-25 15:19:18,646 INFO sqlalchemy.engine.base.Engine.0x...974c UPDATE user SET status=? WHERE user.code = ? AND user.status = ? 2009-08-25 15:19:18,646 INFO sqlalchemy.engine.base.Engine.0x...974c [1, 0, 2] Traceback (most recent call last): File bin/devpython, line 25, in module execfile(sys.argv[0]) File reproduce.py, line 44, in module session.commit() File .../sqlalchemy/orm/session.py, line 673, in commit self.transaction.commit() File .../sqlalchemy/orm/session.py, line 378, in commit self._prepare_impl() File .../sqlalchemy/orm/session.py, line 362, in _prepare_impl self.session.flush() File .../sqlalchemy/orm/session.py, line 1358, in flush self._flush(objects) File .../sqlalchemy/orm/session.py, line 1445, in _flush flush_context.finalize_flush_changes() File .../sqlalchemy/orm/unitofwork.py, line 288, in finalize_flush_changes self.session._register_newly_persistent(elem.state) File .../sqlalchemy/orm/session.py, line 1021, in _register_newly_persistent self.identity_map.remove(state) File .../sqlalchemy/orm/identity.py, line 135, in remove raise AssertionError(State %s is not present in this identity map % state) AssertionError: State sqlalchemy.orm.state.InstanceState object at 0x84228ac is not present in this identity map At first glance that looks identical to yours, until the session tries to do the commit in the end. What's the difference? Regards, Martijn --~--~-~--~~~---~--~~ 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] Re: composite primary key problem
Hey, I now also tested the program with sqlite3 3.6.10: same problem. pysqlite2.5.5 is in use. Regards, Martijn --~--~-~--~~~---~--~~ 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] Re: composite primary key problem
Hi there, I'm looking at the remove() method in sqlalchemy.orm.identify.WeakInstanceDict, as this is where the assertion error is raised. In the 'self' dictionary there is indeed an sqlalchemy.orm.state.InstanceState object with under the key (it's the only entry in the dictionary), but it's a different state object than what is passed in as the 'state' parameter. This triggers the AssertionError. This remove() call is triggered by a piece of code that has a comment primary key switch, in _register_newly_persistent in session.py. I wish we could figure out why you don't get it and I do... Regards, Martijn --~--~-~--~~~---~--~~ 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] Re: composite primary key problem
Martijn Faassen wrote: Michael Bayer wrote: 1. whats this ? File bin/devpython, line 25, in ? execfile(sys.argv[0]) That's a buildout-generated Python script that just controls what's on the python path. It effectively behaves like the Python interpreter. I've never had any problems with it before in the past. I used it here much like one would use a virtualenv. Just to doublecheck, I've just also run it in a Python 2.5 virtualenv instead (with SQLAlchemy and pysqlite easy_installed). Same problem. 2. are you running with a clean build of SQLAlchemy ? Yes, I've tried it with a clean trunk checkout as well as a clean build of SQLAlchemy 0.5.5. On two different machines (Linux Ubuntu both, but one's Ubuntu Hardy and the other Ubuntu Jaunty). The Python 2.4 that I tested with is hand-compiled though, not the system Python. does this occur with an entirely vanilla python 2.5 or 2.6 using the builtin sqlite3 ? or is it specific to a separately installed pysqlite ? --~--~-~--~~~---~--~~ 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] Re: composite primary key problem
Hey Michael, Cool that you managed to reproduce the issue. Michael Bayer wrote: and a potential fix is this: Index: lib/sqlalchemy/orm/session.py === --- lib/sqlalchemy/orm/session.py (revision 6289) +++ lib/sqlalchemy/orm/session.py (working copy) @@ -1018,7 +1018,7 @@ state.key = instance_key elif state.key != instance_key: # primary key switch -self.identity_map.remove(state) +self.identity_map.discard(state) state.key = instance_key self.identity_map.replace(state) Index: MANIFEST.in === I guess the unit test for this would be, to do the operation in both ways so that the issue is indicated regardless of dictionary ordering. Yes, that seems like it would catch it. Anything I can do to make sure that a fix is in a 0.5.x release soon? I could look into writing the unit test that demonstrates the problem; would I do this on trunk? Regards, Martijn --~--~-~--~~~---~--~~ 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] Re: composite primary key problem
Martijn Faassen wrote: Hey Michael, Cool that you managed to reproduce the issue. Michael Bayer wrote: and a potential fix is this: Index: lib/sqlalchemy/orm/session.py === --- lib/sqlalchemy/orm/session.py(revision 6289) +++ lib/sqlalchemy/orm/session.py(working copy) @@ -1018,7 +1018,7 @@ state.key = instance_key elif state.key != instance_key: # primary key switch -self.identity_map.remove(state) +self.identity_map.discard(state) state.key = instance_key self.identity_map.replace(state) Index: MANIFEST.in === I guess the unit test for this would be, to do the operation in both ways so that the issue is indicated regardless of dictionary ordering. Yes, that seems like it would catch it. Anything I can do to make sure that a fix is in a 0.5.x release soon? I could look into writing the unit test that demonstrates the problem; would I do this on trunk? so far I have been lining up ORM fixes for an 0.5.6 release, so this would target at the rel_0_5 branch as well as trunk. I think the test would need to be added to test/orm/test_naturalpks. time is very short these days so hopefully this weekend or next will present a stretch that I can get some fixes and maybe releases going. at the very least if you could add a trac ticket that's targeted at 0.5.6 with high priority it can be the next thing I look at. --~--~-~--~~~---~--~~ 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] Re: composite primary key problem
the program works for me, I get: 2009-08-24 16:12:56,797 INFO sqlalchemy.engine.base.Engine.0x...1830 PRAGMA table_info(user) 2009-08-24 16:12:56,806 INFO sqlalchemy.engine.base.Engine.0x...1830 () 2009-08-24 16:12:56,807 INFO sqlalchemy.engine.base.Engine.0x...1830 CREATE TABLE user ( code INTEGER NOT NULL, status INTEGER NOT NULL, username VARCHAR NOT NULL, PRIMARY KEY (code, status) ) 2009-08-24 16:12:56,808 INFO sqlalchemy.engine.base.Engine.0x...1830 () 2009-08-24 16:12:56,808 INFO sqlalchemy.engine.base.Engine.0x...1830 COMMIT 2009-08-24 16:12:56,812 INFO sqlalchemy.engine.base.Engine.0x...1830 BEGIN 2009-08-24 16:12:56,813 INFO sqlalchemy.engine.base.Engine.0x...1830 INSERT INTO user (code, status, username) VALUES (?, ?, ?) 2009-08-24 16:12:56,813 INFO sqlalchemy.engine.base.Engine.0x...1830 [0, 1, u'a'] 2009-08-24 16:12:56,814 INFO sqlalchemy.engine.base.Engine.0x...1830 COMMIT 2009-08-24 16:12:56,814 INFO sqlalchemy.engine.base.Engine.0x...1830 BEGIN 2009-08-24 16:12:56,815 INFO sqlalchemy.engine.base.Engine.0x...1830 INSERT INTO user (code, status, username) VALUES (?, ?, ?) 2009-08-24 16:12:56,815 INFO sqlalchemy.engine.base.Engine.0x...1830 [0, 2, u'a'] 2009-08-24 16:12:56,816 INFO sqlalchemy.engine.base.Engine.0x...1830 COMMIT 2009-08-24 16:12:56,817 INFO sqlalchemy.engine.base.Engine.0x...1830 BEGIN 2009-08-24 16:12:56,818 INFO sqlalchemy.engine.base.Engine.0x...1830 SELECT user.code AS user_code, user.status AS user_status, user.username AS user_username FROM user WHERE user.code = ? AND user.status = ? 2009-08-24 16:12:56,818 INFO sqlalchemy.engine.base.Engine.0x...1830 [0, 1] 2009-08-24 16:12:56,820 INFO sqlalchemy.engine.base.Engine.0x...1830 SELECT user.code AS user_code, user.status AS user_status, user.username AS user_username FROM user WHERE user.code = ? AND user.status = ? 2009-08-24 16:12:56,820 INFO sqlalchemy.engine.base.Engine.0x...1830 [0, 2] 2009-08-24 16:12:56,822 INFO sqlalchemy.engine.base.Engine.0x...1830 UPDATE user SET status=? WHERE user.code = ? AND user.status = ? 2009-08-24 16:12:56,822 INFO sqlalchemy.engine.base.Engine.0x...1830 [3, 0, 1] 2009-08-24 16:12:56,823 INFO sqlalchemy.engine.base.Engine.0x...1830 UPDATE user SET status=? WHERE user.code = ? AND user.status = ? 2009-08-24 16:12:56,823 INFO sqlalchemy.engine.base.Engine.0x...1830 [1, 0, 2] 2009-08-24 16:12:56,824 INFO sqlalchemy.engine.base.Engine.0x...1830 COMMIT Martijn Faassen wrote: Hi there, I'm experimenting with a composite primary key to see whether it might help implement a workflow system, where the primary key consists of an identifier (code) and a workflow status. I run into an error with the ORM (in 0.5.5 and trunk) when I modify part of the primary key (the workflow status). It looks like the session somehow retains a reference to something that isn't around anymore. The documentation claims primary keys can be mutated, but perhaps I'm doing something that really shouldn't be done after all? I've attached the code to reproduce the issue (reproduce.py). The error is during the commit on the last line, when the primary keys of two items are modified. Here is the traceback: Traceback (most recent call last): File bin/devpython, line 25, in ? execfile(sys.argv[0]) File reproduce.py, line 44, in ? session.commit() File .../sqlalchemy/orm/session.py, line 673, in commit self.transaction.commit() File .../sqlalchemy/orm/session.py, line 378, in commit self._prepare_impl() File .../sqlalchemy/orm/session.py, line 362, in _prepare_impl self.session.flush() File .../sqlalchemy/orm/session.py, line 1358, in flush self._flush(objects) File .../sqlalchemy/orm/session.py, line 1445, in _flush flush_context.finalize_flush_changes() File .../sqlalchemy/orm/unitofwork.py, line 288, in finalize_flush_changes self.session._register_newly_persistent(elem.state) File .../sqlalchemy/orm/session.py, line 1021, in _register_newly_persistent self.identity_map.remove(state) File .../sqlalchemy/orm/identity.py, line 135, in remove raise AssertionError(State %s is not present in this identity map % state) AssertionError: State sqlalchemy.orm.state.InstanceState object at 0xb769ae4c is not present in this identity map Regards, Martijn --~--~-~--~~~---~--~~ 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] Re: composite primary key/postgres
Here's the TSQL for a unique index: CREATE UNIQUE NONCLUSTERED INDEX IX_UQ_Sample ON Sample ( first ASC, other ASC, something ASC) I defined Sample as: CREATE TABLE Sample( first int NOT NULL, something int NULL, other bit NULL) I don't know how you'd get SqlAlchemy to generate this when it makes tables for you. actualy this whole mess is because... i need an unique constraint on that set of foreign keys, but mssql refused to have unique constraints other than the primary key, hence i forced it to be the primary key... --~--~-~--~~~---~--~~ 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/postgres
On May 18, 2008, at 11:34 AM, Arlo Belshee wrote: Here's the TSQL for a unique index: CREATE UNIQUE NONCLUSTERED INDEX IX_UQ_Sample ON Sample ( first ASC, other ASC, something ASC) I defined Sample as: CREATE TABLE Sample( first int NOT NULL, something int NULL, other bit NULL) I don't know how you'd get SqlAlchemy to generate this when it makes tables for you. theres a ddl() construct used for this. Some docs are at: http://www.sqlalchemy.org/docs/04/sqlalchemy_schema.html#docstrings_sqlalchemy.schema_DDL --~--~-~--~~~---~--~~ 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/postgres
i have found a sort of workaround for this. a composite primary key in postgres cannot have nulls in any of its columns. so there must be default_value=something. but because the column is also foreign key, that something should point to an existing record... which means creating a bogus record just for the sake of the foregn key. hmmm may be not a workaround then. actualy this whole mess is because... i need an unique constraint on that set of foreign keys, but mssql refused to have unique constraints other than the primary key, hence i forced it to be the primary key... On Tuesday 13 May 2008 10:48:23 Eric Ongerth wrote: So part of the problem is postgresql is autoincrementing where you do not want it to do so? I thought postgresql only autoincrements where your column is of type 'serial'. Is that not true? Or if so, you could use type 'integer' instead of 'serial'. There is also the possibility that the combination of column type 'integer' and 'nullable=False is handled as identical to 'serial', i.e. autoincrement. I think I've seen that before but I'm not sure. On May 12, 2:57 pm, [EMAIL PROTECTED] wrote: hi. i have a sort-of multicolumn m2m association table, where the primary key is composed of all the links. At least 1 link (actualy, 2) is always present, but never all. so i am defining all of those columns with primary_key=True, nullable=True. which is fine in sqlite, but doesnot work in postgres - it autoincrements those columns without value. how can i fix this? would a default_value=0 - or something - work? (now as i look at it, at least as declaration, the whole primary key seems nullable - is this wrong?) ciao svil --~--~-~--~~~---~--~~ 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/postgres
So part of the problem is postgresql is autoincrementing where you do not want it to do so? I thought postgresql only autoincrements where your column is of type 'serial'. Is that not true? Or if so, you could use type 'integer' instead of 'serial'. There is also the possibility that the combination of column type 'integer' and 'nullable=False is handled as identical to 'serial', i.e. autoincrement. I think I've seen that before but I'm not sure. On May 12, 2:57 pm, [EMAIL PROTECTED] wrote: hi. i have a sort-of multicolumn m2m association table, where the primary key is composed of all the links. At least 1 link (actualy, 2) is always present, but never all. so i am defining all of those columns with primary_key=True, nullable=True. which is fine in sqlite, but doesnot work in postgres - it autoincrements those columns without value. how can i fix this? would a default_value=0 - or something - work? (now as i look at it, at least as declaration, the whole primary key seems nullable - is this wrong?) ciao svil --~--~-~--~~~---~--~~ 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
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)
})
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
