[sqlalchemy] Re: Mapping and updating tables with no primary key
you should be able to say:
table = Table('account_stuff', metadata,
Column('account_id', Integer, ForeignKey('account_ids.account_id'),
primary_key=True),
autoload=True)
That doesn't appear to work unfortunately. When I load the db and
create the two tables and then attempt to do a join:
join(account_ids_table, account_stuff_table)
I get the error:
class 'sqlalchemy.exceptions.ArgumentError': Can't determine join
between 'account_ids' and 'account_stuff'; tables have more than one
foreign key constraint relationship between them. Please specify the
'onclause' of this join explicitly.
Looking at account_stuff_table.foreign_keys I have:
OrderedSet([ForeignKey(u'account_ids.account_id'),
ForeignKey('account_ids.account_id')])
I'm guessing the load duplicated the key. Although I only have two
columns looking at list(account_stuff.c)
[Column(u'credit',SLNumeric(precision=10,length=2)),
Column('account_id',Integer(),ForeignKey('account_ids.account_id'),primary_key=True,nullable=False)]
Thanks
Andy
--~--~-~--~~~---~--~~
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 and updating tables with no primary key
Looking at account_stuff_table.foreign_keys I have:
OrderedSet([ForeignKey(u'account_ids.account_id'),
ForeignKey('account_ids.account_id')])
i see one is unicode'd (the autoloaded), another one is not (yours).
unicode!=str so they probably appear differently named.
see if u can workaround that.
autoloading does not convert unicoded names back into str.
(Paul, u see?)
I'm guessing the load duplicated the key. Although I only have two
columns looking at list(account_stuff.c)
[Column(u'credit',SLNumeric(precision=10,length=2)),
Column('account_id',Integer(),ForeignKey('account_ids.account_id'),
primary_key=True,nullable=False)]
--~--~-~--~~~---~--~~
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 and updating tables with no primary key
On Aug 12, 2007, at 4:44 AM, [EMAIL PROTECTED] wrote:
Looking at account_stuff_table.foreign_keys I have:
OrderedSet([ForeignKey(u'account_ids.account_id'),
ForeignKey('account_ids.account_id')])
i see one is unicode'd (the autoloaded), another one is not (yours).
unicode!=str so they probably appear differently named.
see if u can workaround that.
nope
u'hi'=='hi'
True
autoloading does not convert unicoded names back into str.
(Paul, u see?)
the theme these days is to keep schema elements as unicode on the
python side when we reflect. this is because schema table and column
names may contain non-ascii characters. we have a good deal of
unittests now which successfully create and autoload back tables like
this:
CREATE TABLE 測試 (
id INTEGER NOT NULL,
PRIMARY KEY (id)
)
the issue with the ForeignKey here is just a bug in the override
columns aspect of autoload and ive added ticket #728 for it.
--~--~-~--~~~---~--~~
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 and updating tables with no primary key
one interesting thing here is that i think you've found the oldest bug in SQLAlchemy ever. so thats fixed in the trunk / 0.3 branch, the bug being that it was trying to issue an UPDATE on a table which has no primary keys. Glad to be of service :-) now, if theres a reason you can't have a primary key on account_stuff.account_id, id be interested to hear what that is. Unfortunately the main reason is that the example tables were modelled on a 9 year old legacy Oracle db which is what I really want to use sqlalchemy with. The table which account_stuff is representing has in the order of 18 million rows so making any modifications to it, and we only have Oracle Standard edition, would be too expensive (in time) because the db has to be up 24/7. I guess there is no way of making sqlalchemy thinking that account_stuff.account_id is a PK (equivalent) is there? I guess I could start digging around the underlying code to see if I can convince it! Thanks Andy --~--~-~--~~~---~--~~ 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 and updating tables with no primary key
On Aug 11, 2007, at 5:20 AM, Andy Hird wrote: now, if theres a reason you can't have a primary key on account_stuff.account_id, id be interested to hear what that is. Unfortunately the main reason is that the example tables were modelled on a 9 year old legacy Oracle db which is what I really want to use sqlalchemy with. The table which account_stuff is representing has in the order of 18 million rows so making any modifications to it, and we only have Oracle Standard edition, would be too expensive (in time) because the db has to be up 24/7. I guess there is no way of making sqlalchemy thinking that account_stuff.account_id is a PK (equivalent) is there? I guess I could start digging around the underlying code to see if I can convince it! just place the primary_key=True attribute on your Column. since its an existing table in your oracle database, you arent creating the table there so nothing changes. --~--~-~--~~~---~--~~ 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 and updating tables with no primary key
just place the primary_key=True attribute on your Column. since its an existing table in your oracle database, you arent creating the table there so nothing changes. Ah yeah. That works. Obviously I lose the foreign key relationship with account_ids which is a bit of a shame. It'd be quite useful, certainly for people like me working with large lumbersome legacy databases, if you could specify a non-primary key column to use for doing things like updates. I haven't looked too deep into the sqlalchemy code but is that something that'd be theoretically possible to code. i.e. Something similar to adding a primary_key = True attribute, maybe having a use_as_key=True attribute on some non primary key column (but preserving foreign key relations for example)? If its theoretically possible then its something I'd be willing to have a go at time permitting. Thanks Andy --~--~-~--~~~---~--~~ 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 and updating tables with no primary key
On Aug 11, 2007, at 9:30 PM, Andy Hird wrote: just place the primary_key=True attribute on your Column. since its an existing table in your oracle database, you arent creating the table there so nothing changes. Ah yeah. That works. Obviously I lose the foreign key relationship with account_ids which is a bit of a shame. whys that ? theres no reason you can't set both on the column. It'd be quite useful, certainly for people like me working with large lumbersome legacy databases, if you could specify a non-primary key column to use for doing things like updates. but...the column *is* a primary key column - it identifies the row uniquely. has nothing to do with what the oracle database thinks it is, its just a flag you put on the column. there is also a primary_key argument you can specify on the mapper directly, which is a list of primary key columns. but in your case it makes your mapping more cumbersome, as if you said primary_key= [table1.c.id, table2.c.id], then youd have to identify instances as (1, 1) instead of just 1. when the primary key flags are on the Table, the mapper can figure out that they are both the same value since they are also linked by a foreign key constraint. I haven't looked too deep into the sqlalchemy code but is that something that'd be theoretically possible to code. i.e. Something similar to adding a primary_key = True attribute, maybe having a use_as_key=True attribute on some non primary key column (but preserving foreign key relations for example)? whats the difference ? are we just talking about decoupling create() from the definition of the Table ? if controlling create() is the issue, its a lot more flexible to just create two Table objects, one for your DDL and one for your mappers. --~--~-~--~~~---~--~~ 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 and updating tables with no primary key
whys that ? theres no reason you can't set both on the column.
Ah, my mistake. I kind of assumed a column couldn't be both a foreign
key and a primary key. I'd also tried autoloading the table and then
specifying that the column was a primary key using:
account_stuff_table = Table('account_stuff', metadata, autoload=True)
account_stuff_table.c.account_id.primary_key = True
which didn't seem to work (after setting up the mapper it didn't use
the account_id column on account_stuff as a PK).
But if I create the table using:
account_stuff_table = Table('account_stuff', metadata,
Column('account_id', Integer,
ForeignKey('account_ids.account_id'),
primary_key=True),
Column('credit', Numeric))
it works (which is good enough to be honest).
Should I be able to specify the column is a PK using the autoload
method call? Maybe I'm just setting the PK wrong?
Ignore the rest of my message. Just me misunderstanding stuff.
Thanks for all your help Michael. Its much appreciated.
Andy
--~--~-~--~~~---~--~~
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 and updating tables with no primary key
On Aug 12, 12:19 am, Andy Hird [EMAIL PROTECTED] wrote:
But if I create the table using:
account_stuff_table = Table('account_stuff', metadata,
Column('account_id', Integer,
ForeignKey('account_ids.account_id'),
primary_key=True),
Column('credit', Numeric))
you should be able to say:
table = Table('account_stuff', metadata,
Column('account_id', Integer, ForeignKey('account_ids.account_id'),
primary_key=True),
autoload=True)
and the rest of the columns will autoload.
--~--~-~--~~~---~--~~
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 and updating tables with no primary key
On Aug 10, 2007, at 3:18 AM, Andy Hird wrote: because it's trying to execute the sql: UPDATE account_stuff SET credit=? because I assume account_stuff has no primary key (updates to account_ids specify a where clause). How can I get the above to work? I assume the join in the mapper needs to specify something else? Is it possible? one interesting thing here is that i think you've found the oldest bug in SQLAlchemy ever. so thats fixed in the trunk / 0.3 branch, the bug being that it was trying to issue an UPDATE on a table which has no primary keys. but that doesn't fix your problem...because if you use the latest SQLAlchemy in the trunk or 0.3 branch, now it will just skip the account_stuff table altogether. you just have to put primary_key=True on the account_stuff.account_id column, and then it will work. now, if theres a reason you can't have a primary key on account_stuff.account_id, id be interested to hear what that is. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
