SQLAlchemy example

sqlalchemy_example.py

import os
import sys
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine

Base = declarative_base()

class Person(Base):
    __tablename__ = 'person'
    # Here we define columns for the table person
    # Notice that each column is also a normal Python instance attribute.
    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)

class Address(Base):
    __tablename__ = 'address'
    # Here we define columns for the table address.
    # Notice that each column is also a normal Python instance attribute.
    id = Column(Integer, primary_key=True)
    street_name = Column(String(250))
    street_number = Column(String(250))
    post_code = Column(String(250), nullable=False)
    person_id = Column(Integer, ForeignKey('person.id'))
    person = relationship(Person)

# Create an engine that stores data in the local directory's
# sqlalchemy_example.db file.
engine = create_engine('sqlite:///sqlalchemy_example.db')
Base.metadata.create_all(engine)

sqlalchemy_spyne_example.py

from spyne import TTableModel
TableModel = TTableModel()

# Initialize SQLAlchemy Environment
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
db = create_engine('postgresql+psycopg2://user:password@hostname/database_name')
Session = sessionmaker(bind=db)
TableModel.Attributes.sqla_metadata.bind = db

from spyne import Array, Integer32, Unicode, ComplexModel

class Permission(ComplexModel):
    permission_id = Integer32
    app = Unicode(256)
    op = Unicode(256)

class User(TableModel):
    __tablename__ = 'spyne_user_2'

    user_id = Integer32(primary_key=True)
    user_name = Unicode(256, nullable=False)
    first_name = Unicode(256)
    last_name = Unicode(256)
    permissions = Array(Permission)

TableModel.Attributes.sqla_metadata.create_all(checkfirst=True)