I've an already existing database and want to access it using SQLAlchemy. Because, the database structure's managed by another piece of code (Django ORM, actually) and I don't want to repeat myself, describing every table structure, I'm using autoload introspection. I'm stuck with a simple concrete table inheritance.
Payment FooPayment
+ id (PK) <----FK------+ payment_ptr_id (PK)
+ user_id + foo
+ amount
+ date
Here is the code, with table SQL descritions as docstrings:
class Payment(Base):
"""
CREATE TABLE payments(
id serial NOT NULL,
user_id integer NOT NULL,
amount numeric(11,2) NOT NULL,
date timestamp with time zone NOT NULL,
CONSTRAINT payment_pkey PRIMARY KEY (id),
CONSTRAINT payment_user_id_fkey FOREIGN KEY (user_id)
REFERENCES users (id) MATCH SIMPLE)
"""
__tablename__ = 'payments'
__table_args__ = {'autoload': True}
# user = relation(User)
class FooPayment(Payment):
"""
CREATE TABLE payments_foo(
payment_ptr_id integer NOT NULL,
foo integer NOT NULL,
CONSTRAINT payments_foo_pkey PRIMARY KEY (payment_ptr_id),
CONSTRAINT payments_foo_payment_ptr_id_fkey
FOREIGN KEY (payment_ptr_id)
REFERENCES payments (id) MATCH SIMPLE)
"""
__tablename__ = 'payments_foo'
__table_args__ = {'autoload': True}
__mapper_args__ = {'concrete': True}
The actual tables have additional columns, but this is completely irrelevant to the question, so in attempt to minimize the code I've simplified everything just to the core.
The problem is, when I run this:
payment = session.query(FooPayment).filter(Payment.amount >= 200.0).first()
print payment.date
The resulting SQL is meaningless (note the lack of join condidion):
SELECT payments_foo.payment_ptr_id AS payments_foo_payment_ptr_id,
... /* More `payments_foo' columns and NO columns from `payments' */
FROM payments_foo, payments
WHERE payments.amount >= 200.0 LIMIT 1 OFFSET 0
And when I'm trying to access payment.date I get the following error: Concrete Mapper|FooPayment|payments_foo does not implement attribute u'date' at the instance level.
I've tried adding implicit foreign key reference id = Column('payment_ptr_id', Integer, ForeignKey('payments_payment.id'), primary_key=True) to FooPayment without any success. Trying print session.query(Payment).first().user works (I've omited User class and commented the line) perfectly, so FK introspection works.
How can I perform a simple query on FooPayment and access Payment's values from resulting instance?
I'm using SQLAlchemy 0.5.3, PostgreSQL 8.3, psycopg2 and Python 2.5.2. Thanks for any suggestions.