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()