OK, i missed that "order_id" is not a PK and is kind of a "surrogate
non-primary key" column. So, yes you've touched upon something here
we've never been asked to support explicitly before. The bug is
actually within the mapper that it wants to "post-fetch" the value of
that column in order to get the foreign key value for "order_items",
but the Order object hasn't been totally prepared yet for such...so
thats the bug I'll fix here.
So here is an uglier workaround similar to the one below which works
around the "primary key" issue; a function which will force the
sequence to be pre-executed (normally non-primary key defaults are
executed "inline" and postfetched):
def get_seq(ctx):
return ctx.connection.execute(Sequence('orders_order_id_seq'))
orders_table = Table('orders', metadata,
Column('order_id', Integer, default=get_seq),
autoload=True)
On Jan 28, 2008, at 4:35 PM, Michael Bayer wrote:
>
> hey Phil -
>
> reflection bug. Heres a workaround for now:
>
> orders_table = Table('orders', metadata,
> Column('order_id', Integer, Sequence('orders_order_id_seq'),
> primary_key=True),
> autoload=True)
>
>
> On Jan 28, 2008, at 4:03 PM, Phil Coombs wrote:
>
>>
>> Hi
>>
>> I'm a Python and SA newbie investigating SA for a project using
>> Postgres. I have a database schema that I want to point SA at then be
>> able to write my Python code.
>>
>> I'm working with a version of the basic_association example to keep
>> things simple. I want to have unique auto generated ids on my tables
>> (surrogate primary keys) but also want to have primary keys defined
>> over the entities' actual attributes. I want to have surrogate keys
>> on
>> my tables so that I can use them instead of the full priamary keys
>> when writing joins to keep the SQL brief. In some cases (e.g. the
>> association table) I'm defining the primary key in terms of surrogate
>> primary keys rather than copy over the full primary key from the
>> associated tables.
>>
>> When I run the code (below) I get AttributeError: 'Order' object has
>> no attribute '_instance_key'.
>>
>> Please can someone help me setup SA to work in this example?
>>
>> Thanks in advance
>>
>> Phil
>>
>> Postgress DDL
>> ----------------------
>> create table orders (
>> order_id Serial ,
>> o_customer_name text not null,
>> o_order_date timestamp not null,
>> primary key (o_customer_name, o_order_date) using index ,unique
>> (order_id);
>>
>> create table items (
>> item_id Serial ,
>> it_description text not null,
>> it_price Numeric(8,2) not null,
>> primary key (it_description) using index, unique (item_id) using
>> index);
>>
>> create table order_items (
>> oi_order_id Integer not null,
>> oi_item_id Integer not null,
>> oi_price Numeric(8,2) not null,
>> primary key (oi_order_id, oi_item_id) using index);
>>
>> Python
>> ----------
>> [SNIP] setup and connect string
>>
>> engine = create_engine(connectString, echo=False)
>> metadata = MetaData(engine)
>>
>> orders_table = Table('orders', metadata, autoload=True)
>> items_table = Table('items', metadata, autoload=True)
>> orderitems_table = Table('order_items', metadata,
>> Column('oi_order_id', Integer,
>> ForeignKey('orders.order_id'),
>> primary_key=True),
>> Column('oi_item_id', Integer,
>> ForeignKey('items.item_id'),
>> primary_key=True),
>> autoload=True
>> )
>>
>> class Order(object):
>> def __init__(self, customer_name, order_date):
>> self.customer_name = customer_name
>> self.order_date = order_date
>>
>> class Item(object):
>> def __init__(self, description, price):
>> self.description = description
>> self.price = price
>>
>> class OrderItem(object):
>> def __init__(self, item, price=None):
>> self.item = item
>> self.price = price or item.price
>>
>> mapper(Order, orders_table, properties = {
>> 'id' : orders_table.c.order_id,
>> 'customer_name' : orders_table.c.o_customer_name,
>> 'order_date' : orders_table.c.o_order_date,
>> 'order_items' : relation(OrderItem)
>> })
>>
>> mapper(Item, items_table, properties = {
>> 'id' : items_table.c.item_id,
>> 'description' : items_table.c.it_description,
>> 'price' : items_table.c.it_price
>> })
>>
>> mapper(OrderItem, orderitems_table, properties = {
>> 'order_id' : orderitems_table.c.oi_order_id,
>> 'item_id' : orderitems_table.c.oi_item_id,
>> 'price' : orderitems_table.c.oi_price,
>> 'item' : relation(Item)
>> })
>>
>> Session = sessionmaker(bind=engine, autoflush=True,
>> transactional=True)
>> session = Session()
>>
>> session.save(Item('Item A', 10.99))
>> session.save(Item('Item B', 8))
>> session.save(Item('Item C', 4))
>> session.save(Item('Item D', 1))
>> session.flush()
>>
>> def item(name):
>> return session.query(Item).filter_by(description=name).one()
>>
>> order = Order("cust1", datetime.now())
>> order.order_items.append(OrderItem(item('Item A')))
>> order.order_items.append(OrderItem(item('Item B'),10))
>> order.order_items.append(OrderItem(item('Item C')))
>>
>> session.save(order)
>> session.commit()
>>
>>>
>
>
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
