my sqlalchemy eve experiment

models.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy import Column, String, Date, ForeignKey, Integer, Enum, DateTime, func


Base = declarative_base()


class CommonColumns(Base):
    __abstract__ = True
    _created = Column(DateTime, default=func.now())
    _updated = Column(DateTime, default=func.now(), onupdate=func.now())
    _etag = Column(String(40))

    @hybrid_property
    def _id(self):
        """
        Eve backward compatibility
        """
        return self.id


class User(CommonColumns):
    """Student and Company users"""
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    start_date = Column(Date) # date when they can begin mentoring
    end_date = Column(Date) # date when they're no longer available
    zip_code = Column(String(255))
    type = Column(Enum('student', 'employer'))
    details = Column(String(255))
    email = Column(String(255), nullable=False)
    skills = Column(String(255))
    interests = Column(String(255))


class Company(CommonColumns):
    """Employers looking to mentor students"""
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255), nullable=False)
    state = Column(String(255), nullable=False)
    city = Column(String(255), nullable=False)
    zip_code = Column(String(255), nullable=False)
    address_line_1 = Column(String(255), nullable=False)
    address_line_2 = Column(String(255))
    user_id = Column(Integer, ForeignKey('user.id'))
    phone_number = Column(String(255))
    details = Column(String(255))
    website = Column(String(255))


class Project(CommonColumns):
    """Template for projects employers assign to students"""
    __tablename__ = 'project'
    id = Column(Integer, primary_key=True, autoincrement=True)
    employer_id = Column(Integer, ForeignKey('company.id'))
    type = Column(Enum('shadow_me', 'project', 'skill'), nullable=False)
    name = Column(String(255), nullable=False)
    details = Column(String(255)) # Description of project
    start_date = Column(DateTime, nullable=False)
    duration = Column(Integer) # duration of involvement
    skills = Column(String(255))


class ProjectUser(CommonColumns):
    """Information specific to users and project templates.
    Information such as the date and duration of the project,
    whether a student has been applied or accepted, or whether it's a mentor post.
    Also, once a student has been accepted, some fields to provide feedback from
    both the student and/or mentor."""
    __tablename__ = 'project_user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    project_id = Column(Integer, ForeignKey('project.id'), nullable=False)
    user_id = Column(Integer, ForeignKey('user.id'), nullable=False)
    state = Column(Enum('applied', 'accepted', 'mentor'), nullable=False)
    strengths = Column(String(255))
    areas_of_improvement = Column(String(255))
    student_comments = Column(String(255))
    mentor_comments = Column(String(255))

sqla_eve.py

from eve import Eve
from eve_sqlalchemy import SQL
from eve_sqlalchemy.validation import ValidatorSQL
from eve_sqlalchemy.decorators import registerSchema
import random
import csv
from pprint import pprint
import dateutil.parser
from models import *


if __name__ == '__main__':
    from pprint import pprint

    registerSchema('user')(User)
    registerSchema('company')(Company)
    registerSchema('project')(Project)
    registerSchema('project_user')(ProjectUser)

    pu = ProjectUser._eve_schema['project_user']
    pu['schema']['user_id']['data_relation']['embeddable'] = True
    pu['schema']['project_id']['data_relation']['embeddable'] = True

    SETTINGS = {
        'DEBUG': True,
        'SQLALCHEMY_DATABASE_URI': 'sqlite://',
        'RESOURCE_METHODS': ['GET', 'POST', 'DELETE'],
        'ITEM_METHODS': ['GET', 'PATCH', 'PUT', 'DELETE'],
        'DOMAIN': {
            'user': User._eve_schema['user'],
            'company': Company._eve_schema['company'],
            'project': Project._eve_schema['project'],
            'project_user': ProjectUser._eve_schema['project_user'],
        },
        'DATE_FORMAT': '%m/%d/%Y',
        'IF_MATCH': False
    }


    app = Eve(auth=None, settings=SETTINGS, validator=ValidatorSQL, data=SQL)

    # bind sqlalchemy
    db = app.data.driver
    Base.metadata.bind = db.engine
    db.Model = Base
    db.create_all()
    db.session.commit()

    user_csv = csv.DictReader(open('user.csv'))

    project_csv = csv.DictReader(open('project.csv', 'rU'), delimiter='|')
    company_csv = csv.DictReader(open('company.csv', 'rU'))

    print('company: ', company_csv.fieldnames)
    print('user: ', user_csv.fieldnames)
    print('project: ', project_csv.fieldnames)

    for row in user_csv:
        db.session.add(User(**row))

    user_ids = list(range(1, db.session.query(User).count()))
    for row in company_csv:
        row['user_id'] = random.choice(user_ids)
        user_ids.pop(user_ids.index(row['user_id']))
        db.session.add(Company(**row))

    employer_ids = list(range(1, db.session.query(Company).count()))
    for row in project_csv:
        row['start_date'] = dateutil.parser.parse(row['start_date'])
        row['employer_id'] = random.choice(employer_ids)
        row['name'] = random.choice([
            'shadow engineer',
            'shadow manager',
            'design process',
            'shadow artist',
            'hack things',
            'front-end design',
            'system administration',
            'quality assurance',
            'information assurance',
            'systems analysis',
            'database management',
            'data science',
            'translation',
            'sound engineering'
        ])
        db.session.add(Project(**row))

    db.session.commit()

    app.run(debug=True, use_reloader=False)