SQL Alchemy

Setup

We setup the database in crc/__init__.py.

app = connexion_app.app

...

db = SQLAlchemy(app)

session = db.session

We can then import db or session, depending on our needs.

Example

We log emails sent by the system. Here is the code for the email model.

This code is in crc/models/email.py.

from crc import db
from crc.models.study import StudyModel


class EmailModel(db.Model):
    __tablename__ = 'email'
    id = db.Column(db.Integer, primary_key=True)
    subject = db.Column(db.String)
    sender = db.Column(db.String)
    recipients = db.Column(db.String)
    content = db.Column(db.String)
    content_html = db.Column(db.String)
    study_id = db.Column(db.Integer, db.ForeignKey(StudyModel.id), nullable=True)
    study = db.relationship(StudyModel)

We define our model in a class that extends db.Model.

Like most of our models, we have a primary key of id.

The important columns for the email are subject, sender, recipients, content, and content_html, which are all defined as strings.

Notice that there is both a study_id column that is a Foreign Key, and a study column that is a Relationship.

This model likely started with just study_id, and study was added later. But, we kept the study_id column for backwards compatibility.

Usage

An example of using this model can be found in crc/services/email_service.py

def add_email(subject, sender, recipients, content, content_html, cc=None, study_id=None):

    ...

    email_model = EmailModel(subject=subject, sender=sender, recipients=str(recipients),
                             content=content, content_html=content_html, study=study)

    ...

    db.session.add(email_model)
    db.session.commit()

To add a record to the database,

• instantiate an instance of the model object

• add the instance to the session

• commit the change

For more information about SQLAlchemy, see the documentation.