alchemytest.py

# encoding: utf-8

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column
from sqlalchemy import ForeignKey
from sqlalchemy import ForeignKeyConstraint
from sqlalchemy.types import INTEGER
from sqlalchemy.types import TEXT
from sqlalchemy.orm import relationship

Base = declarative_base()


class Account(Base):
    __tablename__ = 'account'

    id = Column('id', INTEGER(), nullable=False, primary_key=True)
    name = Column('name', TEXT(), nullable=False)


class CreditCard(Base):
    __tablename__ = 'credit_card'

    id = Column('id', INTEGER(), nullable=False, primary_key=True)
    account_id = Column('account_id', INTEGER(), ForeignKey('account.id'), nullable=False)
    card_number = Column('card_number', TEXT(), nullable=False)

    account = relationship("Account")


class Address(Base):
    __tablename__ = 'address'

    id = Column('id', INTEGER(), nullable=False, primary_key=True)
    account_id = Column('account_id', INTEGER(), ForeignKey('account.id'), nullable=False)
    credit_card_id = Column('credit_card_id', INTEGER(), nullable=True)

    __table_args__ = (
        ForeignKeyConstraint(['credit_card_id', 'account_id'], ['credit_card.id', 'credit_card.account_id']),
    )

    account = relationship("Account")
    credit_card = relationship("CreditCard")


engine = create_engine("postgresql://localhost/alchemytest")
sessionmaker = sessionmaker(bind=engine)
db = sessionmaker()

account1 = Account(name="account 1")
db.add(account1)
cc = CreditCard(account=account1, card_number="1234")
db.add(cc)
account2 = Account(name="account 2")
db.add(account2)
address = Address(account=account2)
db.add(address)
db.commit()

print "before cc.account_id = %d" % cc.account_id
print "before address.account_id = %d" % address.account_id

address.credit_card = cc
db.add(address)
db.commit()

print "after cc.account_id = %d" % cc.account_id
print "after address.account_id = %d" % address.account_id